Help fast!!! Distinct value Query focusing on 2 fields, but showing all fields..

[tonydepo]

Ok, I am on a time constraint and really appreciate some help. I have one table "Data" with multiple fields.

Fields:
Acct
Zip
Street
Date


Sample data:
12455 48185 29 KAK Drive 01/01/2012
12455 48185 32 Smalls Drive 01/02/2012
12455 48185 19 Lake drive 01/02/2012
26899 45333 20 Lilly lane 03/03/2012
26899 45999 15 Jacboson ln 03/03/2012


I want to create a query for results that are as follows (1st row with focusing on Acct/ZIP only, but return the entire record):

12455 48185 29 KAK Drive 01/01/2012


26899 45333 20 Lilly lane 03/03/2012


Please Help.... Thank you so much. I tried messing around with First for totals and no luck.

Thanks

[alansidman]

What is your determination as to what determines the first row (record). What criteria do you want to use as a basis of selection?

[tonydepo]

1st 2 Fields for my unique values and display the rest of the record.

Thanks
Reply

[alansidman]

I am not understanding. What determines the first record. In access, data is stored randomly. It is not like spreadsheet. Just because a table looks like a spreadsheet, the order of the records is not necessarily the order in which they are seen. You need to determine what makes a particular record first. Think of a table as a big barrel that you throw data into. You want to retrieve the data in some orderly fashion. Often times it can be on a value (highest, lowest, etc.) or a date (earliest, latest, etc.). I am not grasping what determines in your case what should be first. In your example, the first three records have the same values for account and zip so how would you determine if you had to pull them out of the barrel which one was first?

If you want to use the date as your criteria, ie. earliest date, then perhaps these two queries will do it for you.

SELECT Sheet1.Accnt, Sheet1.Zip, Min(Sheet1.Date1) AS MaxOfField4
FROM Sheet1
GROUP BY Sheet1.Accnt, Sheet1.Zip;

SELECT Query1.Accnt, Query1.Zip, Sheet1.Address, Sheet1.Date1
FROM Query1 LEFT JOIN Sheet1 ON (Query1.MaxOfField4 = Sheet1.Date1) AND (Query1.Accnt = Sheet1.Accnt) AND (Query1.Zip = Sheet1.Zip);

Alan

[tonydepo]

It would be based on date. Thanks