return one instance of account number

[Matthew Dyer]

one field looks at Date and another field looks for Account Number.
Although there can be more than one instance of Account Number per
Date, I only want to return the first instance. Help?

 

[Bob Barrows]

one field looks at Date

I hope that's not the name of the field. "Date" is a reserved keyword, being
the name of a function used in Jet queries. If you can possibly change the
name of that field, do it now (I suggest AccountDate - much more descriptive
than "Date"); otherwise, you will have to remember to bracket that field
name whenever using it in queries.

and another field looks for Account Number.
Although there can be more than one instance of Account Number per
Date, I only want to return the first instance. Help?

You will need a grouping query:

Select AccountDate, Min(AccountNumber) as FirstAcctNum
From Accounts
GROUP BY AccountDate

 

[Matthew Dyer]

I hope that's not the name of the field. "Date" is a reserved keyword, being
the name of a function used in Jet queries. If you can possibly change the
name of that field, do it now (I suggest AccountDate - much more descriptive
than "Date"); otherwise, you will have to remember to bracket that field
name whenever using it in queries.


You will need a grouping query:

Select AccountDate, Min(AccountNumber) as FirstAcctNum
From Accounts
GROUP BY AccountDate

Ya, the "Date" field is actually Action_Date, so that part is taken
care of.

I'm using the designer to help with my query. How would I build a
grouping query that way?

 

[Bob Barrows]

Ya, the "Date" field is actually Action_Date, so that part is taken
care of.

I'm using the designer to help with my query. How would I build a
grouping query that way?

The easiest way for me to explain is to just tell you to switch to SQL View,
paste in my suggested statement, fix the field and table names and switch
back to Design View to see how to do it there.

Or ... I guess I could say:
drag the Action_Date and AccountNumber fields into the grid, click the
Totals button on the toolbar so that a Totals row appears in the grid. Under
Action_Date, it will display Group By so leave that alone. Under
AccountNumber, change the "Group By" in the Totals row to "Min" (or "First",
if you like - I prefer Min)

Actually, I prefer my first explanation ...