How can I query avoiding duplicate data in one field

[Nancy]

Hi -- I maintain a database of children in a family
childcare network. I have a history table with multiple
records for each child. I'd like to create a select query
which will show the children's dates of entry and exit
from various programs over the last few years. The query
as I now have it shows more than one record with the same
programs start date. I'd like it to eliminate duplicate
start dates, and only show one record per child for each
different start date, the record with the most recent
program end date

ChildName Program Start Term
Luis Brito Basic 4/17/2003
Luis Brito Supportive 3/16/2001
Luis Brito Supportive 3/16/2001 4/8/2003

The above is an example of what I get now.

I'd like to see this:

Luis Brito Basic 4/17/2003
Luis Brito Supportive 3/16/2001 4/8/2003


Any suggestions for criteria that I can use to get my
desired results? Many thanks in advance for any ideas!!

 

[Brian Camire]

You might try a query whose SQL look something like this:

SELECT
[Your Table].[ChildName],
[Your Table].[Program],
[Your Table].[Start],
Max([Your Table].[Term]) AS [Term]
FROM
[Your Table]
GROUP BY
[Your Table].[ChildName],
[Your Table].[Program],
[Your Table].[Start]

This will return multiple records for a given child and program if there was
more than one distinct start date. If you want to return only one record in
these cases with the earliest start date and latest end date, you might try
a query whose SQL looks something like this:

SELECT
[Your Table].[ChildName],
[Your Table].[Program],
Min([Your Table].[Start]) AS [Start],
Max([Your Table].[Term]) AS [Term]
FROM
[Your Table]
GROUP BY
[Your Table].[ChildName],
[Your Table].[Program]

Hope this helps.

 

[tom]

Why are you getting duplicated data? Do you input it? Showing Totals in a
query grid should get rid of duplicates, but your data is NOT duplicated...
not as long as you have the Term column... i.e. an additional piece of data
not shown in any of the other fields...

Tom.