Hi all,

I have two columns of data. Column A is names and column B is dates. There
are approximately 33k records of names corresponding to dates. While most of
the names appear only once, there are times when the name appears multiple
times. What I would like is a query that only shows me names when they
appear more than once. If a name only appears once then I do not want it, or
the corresponding date, to be shown in the results. I would like that query
to include the date that the name appears as well.

Can someone assist with this? Many thanks in advance.

Bill

On Aug 11, 11:40*am, BurghRocks <BurghRo...@discussions.microsoft.com>
wrote:
> Hi all,
>
> I have two columns of data. *Column A is names and column B is dates. *There
> are approximately 33k records of names corresponding to dates. *While most of
> the names appear only once, there are times when the name appears multiple
> times. *What I would like is a query that only shows me names when they
> appear more than once. *If a name only appears once then I do not want it, or
> the corresponding date, to be shown in the results. *I would like that query
> to include the date that the name appears as well.
>
> Can someone assist with this? *Many thanks in advance.
>
> Bill

SELECT tPerson.PersonName, Count(Appointment.ApptDate) AS
CountOfApptDate
FROM tPerson INNER JOIN Appointment ON tPerson.PersonID =
Appointment.PersonID
GROUP BY tPerson.PersonName
HAVING (((Count(Appointment.ApptDate))>1));

Use the find duplicates query wizard.

In the database window, on the queries tab
--Select Insert: Query from the Menu
--In the dialog box, Select Find Duplicates Query Wizard
--In the next window, select your Table or Query
--In the next window, select the field with duplicates
--In the next window, select any additional fields you want to display.
--In the next window, Name your query and click Finish

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

pietlinden@hotmail.com wrote:
> On Aug 11, 11:40 am, BurghRocks <BurghRo...@discussions.microsoft.com>
> wrote:
>> Hi all,
>>
>> I have two columns of data. Column A is names and column B is dates. There
>> are approximately 33k records of names corresponding to dates. While most of
>> the names appear only once, there are times when the name appears multiple
>> times. What I would like is a query that only shows me names when they
>> appear more than once. If a name only appears once then I do not want it, or
>> the corresponding date, to be shown in the results. I would like that query
>> to include the date that the name appears as well.
>>
>> Can someone assist with this? Many thanks in advance.
>>
>> Bill
>
> SELECT tPerson.PersonName, Count(Appointment.ApptDate) AS
> CountOfApptDate
> FROM tPerson INNER JOIN Appointment ON tPerson.PersonID =
> Appointment.PersonID
> GROUP BY tPerson.PersonName
> HAVING (((Count(Appointment.ApptDate))>1));

Works great, thank you both very much!

Bill

"John Spencer" wrote:

> Use the find duplicates query wizard.
>
> In the database window, on the queries tab
> --Select Insert: Query from the Menu
> --In the dialog box, Select Find Duplicates Query Wizard
> --In the next window, select your Table or Query
> --In the next window, select the field with duplicates
> --In the next window, select any additional fields you want to display.
> --In the next window, Name your query and click Finish
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>
> pietlinden@hotmail.com wrote:
> > On Aug 11, 11:40 am, BurghRocks <BurghRo...@discussions.microsoft.com>
> > wrote:
> >> Hi all,
> >>
> >> I have two columns of data. Column A is names and column B is dates. There
> >> are approximately 33k records of names corresponding to dates. While most of
> >> the names appear only once, there are times when the name appears multiple
> >> times. What I would like is a query that only shows me names when they
> >> appear more than once. If a name only appears once then I do not want it, or
> >> the corresponding date, to be shown in the results. I would like that query
> >> to include the date that the name appears as well.
> >>
> >> Can someone assist with this? Many thanks in advance.
> >>
> >> Bill
> >
> > SELECT tPerson.PersonName, Count(Appointment.ApptDate) AS
> > CountOfApptDate
> > FROM tPerson INNER JOIN Appointment ON tPerson.PersonID =
> > Appointment.PersonID
> > GROUP BY tPerson.PersonName
> > HAVING (((Count(Appointment.ApptDate))>1));
>