I'm trying to run a query

N

NMHemp

I'm putting together a database to collect and record when employees of our
various project sites complete and return certain forms to us. What this
means is that several sites will potentially have more than one date entered.
I've created a table called "site number" and date fields for each of the
three forms. However, I would like to design a query that just shows the most
recent form we received per site. Any ideas? Nothing I've tried seems to work.
 
J

JP

If I'm understanding you correctly, you have one row per site, and that row
contains three date fields -- one for each form.

If that's right, then you're going to have to create a query that contains
the site and a field something like this

LastDate: iif(Form1Date > Form2Date and Form1Date > Form3Date, Form1Date,
iif(Form2Date > Form3Date and Form2Date > Form1Date), Form2Date), Form3Date)

Actually, it's going to have to be uglier than that since you'll have to use
an nz function to protect against any of the dates being null like this
nz(Form1date, #1/1/1900#)

The better thing would be to change your table so that there is one row per
form, like this

site form date

Then you can just do a summary query in which you group by site and take the
max of date
 
Top