How to use dates in one table to select a range of dates in anothe

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work with stream databases. I am trying take the water chemistry samples
(in tbl chemistry) that were collected 30 days before or 30 days after an
insect sample (in tbl insects) was collected at the same site (~1000 insects
sites, not all match the ~2000 chemistry sites), and populate the tbl insects
(or create a new table) with
1. the number of chemistry samples collected +/- 30days of the insect sample
at that site
2. the average of the chemistry parameters (nitrogen, phosphorus,
chlorophyll) collected +/- 30 days of the insect sample
3. And have the ability to change the range of days, such as get the
chemistry collected 60 days prior to insect samples.
 
DataDebbie said:
I work with stream databases. I am trying take the water chemistry samples
(in tbl chemistry) that were collected 30 days before or 30 days after an
insect sample (in tbl insects) was collected at the same site (~1000 insects
sites, not all match the ~2000 chemistry sites), and populate the tbl insects
(or create a new table) with
1. the number of chemistry samples collected +/- 30days of the insect sample
at that site
2. the average of the chemistry parameters (nitrogen, phosphorus,
chlorophyll) collected +/- 30 days of the insect sample
3. And have the ability to change the range of days, such as get the
chemistry collected 60 days prior to insect samples.
Click to expand...


SELECT B.bugsite, B.bugdate,
Avg(C.N) As AvgN, Avg(C.P) As AvgP, Avg(K) As AvgK
FROM chemistry AS C INNER JOIN insects AS B
ON C.chemsite = B.bugsite
AND C.chemdate >= B.bugdate - [Days Before]
AND C.chemdate >= B.bugdate + [Days After]
GROUP BY B.bugsite, B.bugdate

I don't see any reason to store any values of these values
in the insects or any other table. Actually, there are many
good reason why they should not be saved.
 
Hi Marshall,

Thanks - we tried that query and it didn't quite work. The greater the range
of days I ask for based on the bug sites, the fewer bug sites are returned
that have chemistry data. And it should be reverse - the greater the range
of days, the more bug sites that will also have chem data.

Below is the query we tried.
idcpcb = the site code
copy of orig 2004Marsamp raw = raw chemistry data listed by site and date
copy of tbl richnesssitedate = raw bug data, listed by site and date

(I know these names are long - this was just for practice).

Any ideas? If you had time and wanted to help, could I contact you another
way and send you a mini-dataset to try this out on? It's really important to
our project to figure it out.

Here is the query we entered in SQL:
SELECT B.idcpcb, B.date, Avg(C.[Total N (mg/l)]) AS AvgN, Avg(C.[Total P
(mg/l)]) AS AvgP, Avg(C.[Chlorophyll a (ug/l)]) AS AvgChla
FROM [copy of orig 2004Marsamp raw] AS C INNER JOIN [copy of tbl
richnesssitedate] AS B ON (C.idcpcb=B.idcpcb) AND (C.date>=B.date-[Days
Before]) AND (C.date>=B.date+[Days After])
GROUP BY B.idcpcb, B.date;

Thanks!
Debbie



Marshall Barton said:
DataDebbie said:
I work with stream databases. I am trying take the water chemistry samples
(in tbl chemistry) that were collected 30 days before or 30 days after an
insect sample (in tbl insects) was collected at the same site (~1000 insects
sites, not all match the ~2000 chemistry sites), and populate the tbl insects
(or create a new table) with
1. the number of chemistry samples collected +/- 30days of the insect sample
at that site
2. the average of the chemistry parameters (nitrogen, phosphorus,
chlorophyll) collected +/- 30 days of the insect sample
3. And have the ability to change the range of days, such as get the
chemistry collected 60 days prior to insect samples.
Click to expand...


SELECT B.bugsite, B.bugdate,
Avg(C.N) As AvgN, Avg(C.P) As AvgP, Avg(K) As AvgK
FROM chemistry AS C INNER JOIN insects AS B
ON C.chemsite = B.bugsite
AND C.chemdate >= B.bugdate - [Days Before]
AND C.chemdate >= B.bugdate + [Days After]
GROUP BY B.bugsite, B.bugdate

I don't see any reason to store any values of these values
in the insects or any other table. Actually, there are many
good reason why they should not be saved.
Click to expand...
 
Hi Marshall - I forgot to ask this in the previous post - how does the query
know to base the range on the day - rather than the year or month? In this
part:
(C.date>=B.date-[Days > Before])

Thanks.

DataDebbie said:
Hi Marshall,

Thanks - we tried that query and it didn't quite work. The greater the range
of days I ask for based on the bug sites, the fewer bug sites are returned
that have chemistry data. And it should be reverse - the greater the range
of days, the more bug sites that will also have chem data.

Below is the query we tried.
idcpcb = the site code
copy of orig 2004Marsamp raw = raw chemistry data listed by site and date
copy of tbl richnesssitedate = raw bug data, listed by site and date

(I know these names are long - this was just for practice).

Any ideas? If you had time and wanted to help, could I contact you another
way and send you a mini-dataset to try this out on? It's really important to
our project to figure it out.

Here is the query we entered in SQL:
SELECT B.idcpcb, B.date, Avg(C.[Total N (mg/l)]) AS AvgN, Avg(C.[Total P
(mg/l)]) AS AvgP, Avg(C.[Chlorophyll a (ug/l)]) AS AvgChla
FROM [copy of orig 2004Marsamp raw] AS C INNER JOIN [copy of tbl
richnesssitedate] AS B ON (C.idcpcb=B.idcpcb) AND (C.date>=B.date-[Days
Before]) AND (C.date>=B.date+[Days After])
GROUP BY B.idcpcb, B.date;

Thanks!
Debbie



Marshall Barton said:
DataDebbie said:
I work with stream databases. I am trying take the water chemistry samples
(in tbl chemistry) that were collected 30 days before or 30 days after an
insect sample (in tbl insects) was collected at the same site (~1000 insects
sites, not all match the ~2000 chemistry sites), and populate the tbl insects
(or create a new table) with
1. the number of chemistry samples collected +/- 30days of the insect sample
at that site
2. the average of the chemistry parameters (nitrogen, phosphorus,
chlorophyll) collected +/- 30 days of the insect sample
3. And have the ability to change the range of days, such as get the
chemistry collected 60 days prior to insect samples.
Click to expand...


SELECT B.bugsite, B.bugdate,
Avg(C.N) As AvgN, Avg(C.P) As AvgP, Avg(K) As AvgK
FROM chemistry AS C INNER JOIN insects AS B
ON C.chemsite = B.bugsite
AND C.chemdate >= B.bugdate - [Days Before]
AND C.chemdate >= B.bugdate + [Days After]
GROUP BY B.bugsite, B.bugdate

I don't see any reason to store any values of these values
in the insects or any other table. Actually, there are many
good reason why they should not be saved.
Click to expand...
Click to expand...
 
ahh bleep.... I had the < sign reversed in the second
condition. Change it to:

AND C.chemdate <= B.bugdate + [Days After]

Time? What's that? I only get to check in here between
making sure the carpenters, plumber and electrician are
following the right plan. ;-)
--
Marsh
MVP [MS Access]


Thanks - we tried that query and it didn't quite work. The greater the range
of days I ask for based on the bug sites, the fewer bug sites are returned
that have chemistry data. And it should be reverse - the greater the range
of days, the more bug sites that will also have chem data.

Below is the query we tried.
idcpcb = the site code
copy of orig 2004Marsamp raw = raw chemistry data listed by site and date
copy of tbl richnesssitedate = raw bug data, listed by site and date

(I know these names are long - this was just for practice).

Any ideas? If you had time and wanted to help, could I contact you another
way and send you a mini-dataset to try this out on? It's really important to
our project to figure it out.

Here is the query we entered in SQL:
SELECT B.idcpcb, B.date, Avg(C.[Total N (mg/l)]) AS AvgN, Avg(C.[Total P
(mg/l)]) AS AvgP, Avg(C.[Chlorophyll a (ug/l)]) AS AvgChla
FROM [copy of orig 2004Marsamp raw] AS C INNER JOIN [copy of tbl
richnesssitedate] AS B ON (C.idcpcb=B.idcpcb) AND (C.date>=B.date-[Days
Before]) AND (C.date>=B.date+[Days After])
GROUP BY B.idcpcb, B.date;

Marshall Barton said:
SELECT B.bugsite, B.bugdate,
Avg(C.N) As AvgN, Avg(C.P) As AvgP, Avg(K) As AvgK
FROM chemistry AS C INNER JOIN insects AS B
ON C.chemsite = B.bugsite
AND C.chemdate >= B.bugdate - [Days Before]
AND C.chemdate >= B.bugdate + [Days After]
GROUP BY B.bugsite, B.bugdate

I don't see any reason to store any values of these values
in the insects or any other table. Actually, there are many
good reason why they should not be saved.
Click to expand...
Click to expand...
 
DataDebbie said:
Hi Marshall - I forgot to ask this in the previous post - how does the query
know to base the range on the day - rather than the year or month? In this
part:
(C.date>=B.date-[Days Before])
Click to expand...

That works because a date value is stored as a double with
the integer part being the number of days (since 30 Dec
1899).

That's not a particularly good practice (more rigorous would
be to use the DateAdd function), but it will work fine for
days. It will not work at all for months or years.
 
Thanks! That worked!
Debbie

Marshall Barton said:
ahh bleep.... I had the < sign reversed in the second
condition. Change it to:

AND C.chemdate <= B.bugdate + [Days After]

Time? What's that? I only get to check in here between
making sure the carpenters, plumber and electrician are
following the right plan. ;-)
--
Marsh
MVP [MS Access]


Thanks - we tried that query and it didn't quite work. The greater the range
of days I ask for based on the bug sites, the fewer bug sites are returned
that have chemistry data. And it should be reverse - the greater the range
of days, the more bug sites that will also have chem data.

Below is the query we tried.
idcpcb = the site code
copy of orig 2004Marsamp raw = raw chemistry data listed by site and date
copy of tbl richnesssitedate = raw bug data, listed by site and date

(I know these names are long - this was just for practice).

Any ideas? If you had time and wanted to help, could I contact you another
way and send you a mini-dataset to try this out on? It's really important to
our project to figure it out.

Here is the query we entered in SQL:
SELECT B.idcpcb, B.date, Avg(C.[Total N (mg/l)]) AS AvgN, Avg(C.[Total P
(mg/l)]) AS AvgP, Avg(C.[Chlorophyll a (ug/l)]) AS AvgChla
FROM [copy of orig 2004Marsamp raw] AS C INNER JOIN [copy of tbl
richnesssitedate] AS B ON (C.idcpcb=B.idcpcb) AND (C.date>=B.date-[Days
Before]) AND (C.date>=B.date+[Days After])
GROUP BY B.idcpcb, B.date;

DataDebbie wrote:
I work with stream databases. I am trying take the water chemistry samples
(in tbl chemistry) that were collected 30 days before or 30 days after an
insect sample (in tbl insects) was collected at the same site (~1000 insects
sites, not all match the ~2000 chemistry sites), and populate the tbl insects
(or create a new table) with
1. the number of chemistry samples collected +/- 30days of the insect sample
at that site
2. the average of the chemistry parameters (nitrogen, phosphorus,
chlorophyll) collected +/- 30 days of the insect sample
3. And have the ability to change the range of days, such as get the
chemistry collected 60 days prior to insect samples.
Click to expand...
Marshall Barton said:
SELECT B.bugsite, B.bugdate,
Avg(C.N) As AvgN, Avg(C.P) As AvgP, Avg(K) As AvgK
FROM chemistry AS C INNER JOIN insects AS B
ON C.chemsite = B.bugsite
AND C.chemdate >= B.bugdate - [Days Before]
AND C.chemdate >= B.bugdate + [Days After]
GROUP BY B.bugsite, B.bugdate

I don't see any reason to store any values of these values
in the insects or any other table. Actually, there are many
good reason why they should not be saved.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top