By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Domain Aggregate per record?

JustJim
Expert 100+
P: 407
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?
Expand|Select|Wrap|Line Numbers
  1. EarliestSample: DMin("[Date]","tblSample")
It's got to be something like
Expand|Select|Wrap|Line Numbers
  1. "SiteNo = 'something'"
but what? Should I compare it to something else in the query
Expand|Select|Wrap|Line Numbers
  1. "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
Oct 5 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
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?
Expand|Select|Wrap|Line Numbers
  1. EarliestSample: DMin("[Date]","tblSample")
It's got to be something like
Expand|Select|Wrap|Line Numbers
  1. "SiteNo = 'something'"
but what? Should I compare it to something else in the query
Expand|Select|Wrap|Line Numbers
  1. "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

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Min(tblDateMax_1.date1) AS MinOfdate1, Max(tblDateMax_2.date2) AS MaxOfdate2
  2. 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
  3. GROUP BY Table1.ID;
  4.  
J
Oct 5 '07 #2

JustJim
Expert 100+
P: 407
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
Oct 5 '07 #3

JConsulting
Expert 100+
P: 603
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
Oct 5 '07 #4

JustJim
Expert 100+
P: 407
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
Oct 9 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.