Complex Date Query

[pickslides]

Hi there forumers.

I have 2 tables one is a water consumption table with billing records. The table has a To date and From date for each record but they are random.

The other table is a climate table full of data like rainfall and temperature etc..

I am looking for a query that will take information from the consumption table the To date and From date to match up with the climate data.

For example if a customer is billed for water usage between 1/3/2009 and 25/6/2009 then I would like to match the total rainfall and average temperature with the water usage over this particular period and in turn look for some correlation in the data.

 

[jdraw]

Hi there forumers.

I have 2 tables one is a water consumption table with billing records. The table has a To date and From date for each record but they are random.

The other table is a climate table full of data like rainfall and temperature etc..

I am looking for a query that will take information from the consumption table the To date and From date to match up with the climate data.

For example if a customer is billed for water usage between 1/3/2009 and 25/6/2009 then I would like to match the total rainfall and average temperature with the water usage over this particular period and in turn look for some correlation in the data.

Based on your sample database and your post, here is a query that will relate the tables and give you the Total Rainfall and Average Temperature
for the From/To Dates in the Consumption Table.
The query will ask for the MasterID from the Consumption Table, and will provide the Total RainFall and AvgTemp over the period involved.

SELECT Sum(Climate.Rainfall) AS SumOfRainfall
, Avg(Climate.Temp) AS AvgOfTemp
FROM Consumption, Climate
WHERE
        (((Climate.Date) Between [Consumption].[From Date] And [Consumption].[To Date]) AND
       ((Consumption.[Master ID])=[Enter master ID from Consumption]));

 

[pickslides]

Hi jdraw, Thanks for your prompt reply and Thank you for that piece of code. It worked very well.

This query only returned what I was after for 1 Master ID at a time. A great start!

Is there a simple way this could be adjusted so it would return a list of all Master IDs aligned with their matched climate data?

 

[jdraw]

Hi jdraw, Thanks for your prompt reply and Thank you for that piece of code. It worked very well.

This query only returned what I was after for 1 Master ID at a time. A great start!

Is there a simple way this could be adjusted so it would return a list of all Master IDs aligned with their matched climate data?

Yes, you could use vba.
Put the SQL string into a variable, and use a loop to process all the records in the table with the MasterID.

How familiar are you with vba? How many records in the real Consumption table?

 

[pickslides]

Yes, you could use vba.
Put the SQL string into a variable, and use a loop to process all the records in the table with the MasterID.

How familiar are you with vba? How many records in the real Consumption table?

There is around 400,000 records in the consumption table. I have actually figured it out. Thanks again for your help.

 

[jdraw]

There is around 400,000 records in the consumption table. I have actually figured it out. Thanks again for your help.

Glad to help.