You do this in SQL using a derived table, but I am not sure this is done in Access ANSI 89 SQL.
Try creating a local table, and a delete query to empty it before each code run.
Then a query to list all the Site_Numbers (SELECT DISTINCT Site_Number...)
Then when you run your report, delete all data in the local table, open the distinct query, take the first site number, take the DMAX() for that sitenumber from the first table, and post it to the local report table. Then move on to the next site number. When that is all done, open the report on the local report table.
The local report table will then have all the Site_Numbers, and the results of all the DMAX() functions. I think you will have to take the Site_Number into a variable (sSite As String) and do this:
sSite = rs("Site_Number")
...
DMAX("site_program_year","tblsss","site_number='" & sSite "'")
Is that helping any?
mmcdonal
|