Domain Aggregate per record?

[JustJim]

I have a table for water monitoring sites, each site has a SiteNo (site number) field and in another table each site has details of a number of samples taken at that site, including the date taken, What I need is a query which gives me details of the sites with the first and last date on which a sample was taken for each site. The following code gets me the earlies sample in the sample table, but what do I put in the third part of the DMin to get it for each site?

EarliestSample: DMin("[Date]","tblSample")

It's got to be something like

"SiteNo = 'something'"

but what? Should I compare it to something else in the query

"SiteNo = SiteNo"

or something on the report where the query is the Control Source?

Thanks in advance, and

Go Mary, Go (9K!)
(Edit: Actually, everybody in the top 10 with the exception of JosAH and possibly r0 (and pbmods who hase just clocked one) are in the final stages of the next K. Way to go troops!)

Jim

[JConsulting]

I have a table for water monitoring sites, each site has a SiteNo (site number) field and in another table each site has details of a number of samples taken at that site, including the date taken, What I need is a query which gives me details of the sites with the first and last date on which a sample was taken for each site. The following code gets me the earlies sample in the sample table, but what do I put in the third part of the DMin to get it for each site?

EarliestSample: DMin("[Date]","tblSample")

It's got to be something like

"SiteNo = 'something'"

but what? Should I compare it to something else in the query

"SiteNo = SiteNo"

or something on the report where the query is the Control Source?

Thanks in advance, and

Go Mary, Go (9K!)
(Edit: Actually, everybody in the top 10 with the exception of JosAH and possibly r0 (and pbmods who hase just clocked one) are in the final stages of the next K. Way to go troops!)

Jim

Hi Jim,
Add your primary table to your query grid. Add your Child table twice.
Join them all on the ID/FK field

Drag your ID field from your primary down to the query grid.
Drag your date field down from the 1st child table
drag your date field down from the 2nd child table

now use the group by thing...for the id
use the Min for the first date field
Use the Max for the second.

That's it!

Example

SELECT Table1.ID, Min(tblDateMax_1.date1) AS MinOfdate1, Max(tblDateMax_2.date2) AS MaxOfdate2
FROM (Table1 INNER JOIN tblDateMax AS tblDateMax_1 ON Table1.ID = tblDateMax_1.ID) INNER JOIN tblDateMax AS tblDateMax_2 ON Table1.ID = tblDateMax_2.ID
GROUP BY Table1.ID;
 

J

[JustJim]

Hi Jim,
Add your primary table to your query grid. Add your Child table twice.
Join them all on the ID/FK field

Drag your ID field from your primary down to the query grid.
Drag your date field down from the 1st child table
drag your date field down from the 2nd child table

now use the group by thing...for the id
use the Min for the first date field
Use the Max for the second.

That's it!

J

You're good, you're darn good!
If you're the Houston, Tx of programming (I snuck a look at your profile), then I'm the San Angelo!

Thank you so much.

Jim

[JConsulting]

You're good, you're darn good!
If you're the Houston, Tx of programming (I snuck a look at your profile), then I'm the San Angelo!

Thank you so much.

Jim

Happy to help. And Thanks for the kudos!
J

[JustJim]

Happy to help. And Thanks for the kudos!
J

A supplementary question, if you're still watching this thread. Is it having the same table in a query twice that massively increases the running time of the query?

Jim