Help with sql query (find who has file depending on date)

[Eddy]

I've been wracking my head all day with this one.

A file gets assigned to a agent here and over time it gets passed around.
The date the file gets assigned to the agent is recorded. So I have an
invoice table with an agent table and an agentinvoice table in between to
keep track of everyone who's had the invoice.

Now what I need to do is run a query off the agentinvoice table to see who
is currently working with the invoice. I know it has everything to do with
retrieving the max date. I've tried the max function, first, last, everything
I could think of. Can someone give me a hand or point me in the write
direction.

Thanks in advance!!!

 

[Jeff Boyce]

Eddy

Create a new query in design mode. Add the AgentInvoice table. Select the
InvoiceID field and the DateAssigned field.

Select the Totals button from the toolbar. Set the InvoiceID groupby
field/cell to GroupBy.

Set the DateAssigned groupby field/cell to Maximum.

Run. (the query, not you).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Eddy]

Thanks Jeff...I appreciate it

Eddy

Create a new query in design mode. Add the AgentInvoice table. Select the
InvoiceID field and the DateAssigned field.

Select the Totals button from the toolbar. Set the InvoiceID groupby
field/cell to GroupBy.

Set the DateAssigned groupby field/cell to Maximum.

Run. (the query, not you).

Regards

Jeff Boyce
<Office/Access MVP>