return one instance of account number

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

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?
 
Matthew said:
one field looks at Date
Click to expand...

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?
Click to expand...

You will need a grouping query:

Select AccountDate, Min(AccountNumber) as FirstAcctNum
From Accounts
GROUP BY AccountDate
 
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
Click to expand...

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?
 
Matthew said:
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?
Click to expand...

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 ... :-)
 
Back
Top