greatest date

[sallyc]

Hi

I have two table linked by a URN, one table has a single date relating to each URN, the other table has multiple dates relateing to the URN.

I want to to take the greatest date on the second table so that i have the largest amount of time between the two dates for each URN.

Can anyone tell me how I would do this?

hope you can help

S

 

[dcx693]

You can run a totals query. If you've never done so before, just create a select query with the fields you want to view. Then go to the View menu and choose Totals. In the Total line that shows up in the QBE grid, choose GroupBy for the URN number, choose Max for the date from the second table. That will give you the most recent date. If you want, you can also show the date field in the QBE grid again and then choose Min. You can even write a formula like Duration:[Most recent date]-[First date] to have Access calculate the day difference for you.

 

[sallyc]

Thank you

 

[fixious123]

? about Max

Can I still use this when I have two different date fields out of numerous people but I want the most recent date out of those two fields.

I.E.

I have M16 AFQC and M16 TRQC. I need the latest date out of these two fields.

Thanks and I know you've tried to help me, just can't get it.

 

[dcx693]

What are "M16 AFQC and M16 TRQC"? The names of your two date fields?

 

[fixious123]

Yes two date fields. Thank you for responding

 

[dcx693]

There is no simple Max function in Access, just the SQL aggregate Max function. For a 2 item comparison, do something like:
=IIf([M16 AFQC]>[M16 TRQC],[M16 AFQC],[M16 TRQC])

 

[fixious123]

Dates?

Where does this function go?