Query Question

[Jeff]

my data looks like the following

111 0 01/01/2007
111 0 02/01/2007
222 0 04/01/2007
222 0 05/01/2007
555 1 06/01/2007
666 0 06/01/2007

how can i retrieve the following???

111 0 01/01/2007
222 0 04/01/2007
555 1 06/01/2007
666 0 06/01/2007

my goal is to retrive distinct cols 1 and 2, and then the earliest date in
col 3 for distinct records in cols 1 and 2???

any suggestions??? I tried different ways of using FIRST, but was
unsuccessful.

Thanks in advance.

 

[Jeff Boyce]

Jeff

How is this a "reports" newsgroup question?

Take a look at the Totals queries, and the Min and Max aggregation.

When you use "FIRST", you're letting Access decide what order the records
are stored in internally.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marcus Lloyd]

Hi,

What I think you need to do is:

1. Create your query
2. Then right click in the query, on one of the fields you have selected and
select totals.
3. For the first field select "Group By"
4. For the second field select "Sum", or the function you require
5. For the date field select "Group By" and sort Asending

As for the dates, if you need to group them together but they have different
dates, format the date i.e. Month/Year Format(Date, "mm/yyyy")

That should group all the data and sum/min/max the data into the groups. If
the date causes a problem then use the where clause with a formatted date.

The only issue I see is the date for example, records starting 222, have a
different date. So if you group by date then they will be separated.

Hope it helps.

regards

Marcus Lloyd