Subqueries and aggregates

[waka]

I have 2 tables:

Table 1 (tbl_Temp) contains temperature data for a particular park at a certain date/time.

Table 2 (tbl_Surveys) contains survey data that was conducted at a certain date/time

I need to find the nearest date/time for each survey in Table 2 to Table1 and import the temperature data into Table 2. While this sounds like a simple problem I've only been able to do it using a series of queries - pretty much because of the need to run an aggregate query to find the minimum date difference. I also want to do this in the background (via VBA) and I'm not entirely sure how to link 2 SQL statements in VBA. Does anyone have a more elegant solution than my series of queries (see below)? Thanks!

Query1:
SELECT tbl_Surveys.Park, Format([SurveyDate],"General Date") AS Survey_Date, tbl_Temp.[Date(ET)], Abs(([SurveyDate]-[Date(ET)])*24) AS DateDiff, tbl_Temp.[Temperature(F)], Year([SurveyDate]) AS [Year]
FROM tbl_Surveys
INNER JOIN tbl_Temp ON tbl_Surveys.Park = tbl_Temp.PARK;

Query2:
SELECT Query1.Park, Query1.Survey_Date, Min(Query1.DateDiff) AS MinOfDateDiff
FROM Query1
GROUP BY Query1.Park, Query1.Survey_Date
ORDER BY Query1.Park, Query1.Survey_Date, Min(Query1.DateDiff);

Query3:
SELECT Query2.Park, Query2.Survey_Date, Query1.[Date(ET)], Query2.MinOfDateDiff, Query1.[Temperature(F)]
FROM Query1 INNER JOIN Query2 ON (Query1.DateDiff = Query2.MinOfDateDiff) AND (Query1.Survey_Date = Query2.Survey_Date) AND (Query1.Park = Query2.Park)
ORDER BY Query2.Park, Query2.Survey_Date;

 

[Guus2005]

There is a VBA function called DateDiff which you can use to determine the minimum number of (e.g.) days between two dates.
Use a MIN aggregate query to determine the minimum per park/...

pseudo

select min(datediff("d", T1.SurveyDate, T2.SurveyDate)) from Table1 T1 inner join Table2 T2 on T1.ParkID = T2.ParkID, T1.SurveyDate = T2.DateET
group by T1.ParkID, ...

All in a single query.
For a more detailed/working query, please post a sample (Acc2003) database.

HTH

 

[waka]

Thanks Guus for replying. I'm not too concerned with the first 2 queries, the formula that I'm using now to calculate the date difference seems to work and I could combine them using a subquery.

The real problem I'm facing is keeping the temperature data when I use the MIN aggregate query. There isn't an aggregate I can use to just keep the temperature that corresponds with the MIN time value unless I match it to the original query (which is why I have to keep the first 2 queries separate - if I aggregate in one query I can't match back to the temperature).

Hope that makes sense...

 

[jdraw]

As Guus said, please post a sample mdb (2003 format).

Need to see table structure and relationships, and some data.

There is a sample of a query using Max aggregate function and other fields at
http://www.access-programmers.co.uk/forums/showthread.php?t=231998


You may get some ideas from it.

 

[waka]

I've attached the database with the 2 tables a 3 queries. I think I may just have to run a macro with the 3 queries..

 

[jdraw]

I think you have some structural issues with your tables.
What to these tables represent? How are the tables related in your view?
What exactly do you want/need?
You have a PK on tbl_Surveys, but no PK on tbl_temperature and no obvious link fields on which to join the tables.

All tbl_survey surveyTime values are 1/1/1900?? Why is this? What does it mean?

You should adopt a naming convention that does NOT have embedded spaces or special characters. Use only alphanumeric and "_". You will save yourself a lot of headaches.

From Date_GMT you can calculate Date_ET so no need to store both.
If Temperature values are related to Parks points, then you need to restructure tables.

Modifying your query7 to remove a Format error and limiting the Parks to FRSP, I get 235638 records--so it appears to be some sort of Cartesian product??? You only have 6 distinct Parks, and your temperature data refers to Park not Park Point.


In plain English what are the Temperature values and to what do they relate in table survey?

 

[waka]

Jdraw - thanks for replying.

I realize that the tables are not related, because they will not be in the final database and I realize there are some formatting issues, but these are not my primary concern at this point. I just need to figure out the logic of these queries and I can clean it up as I go.

The SurveyTime values show up as 1/1/1900, but that's just the formatting. If you click on the cell it'll show the actual time value - just like the survey date just shows the date, but it's also storing the time. In the first query I actually convert SurveyDate to a General Format so both date and time appear in one field like the Date does in tbl_Temperature. Ideally I would just change the format to begin with, but I don't have control over it's structure (I'm working with a frontend that's connected to those backend tables).

Basically tbl_Temperature is a temporary table that I'm importing from multiple excel files. All I want to do is find the temperature from tbl_Temperature that matches as close as possible to the date/time of the surveys done in tbl_Surveys.

The huge number of records in the result of query 7 is because it is basically comparing each survey to each temperature record per park. We don't have a temperature reading at each station, just one weather station for the whole park.

 

[jdraw]

Which date are you comparing DateGMT or DateET?

 

[waka]

Sorry for not being clearer. The Date I'm using is Date(ET) - what was the formatting error you found in query7?

 

[jdraw]

Could not find the Format function.

Seems there is a missing reference. Not an issue since your formats on your table fields do no affect the underlying data. I changed them to General Date and all is well from data perspective.

So the question you are really asking is

We have 2 tables -surveys and temperatures. In Table survey -We have a bunch of survey data for points within Parks and the date/time of the survey point record.
In table temperature we have a Park identifier(name) and 2 date time fields and a temperature reading. We are trying to associate a Temperature reading to a Park (not park point) in table survey based on the finding the record in Table surveys where the Datediff value between surveyDate Date/Time and Temperature Date/Times DateET is the minimum.

Is that correct?

 

[waka]

That is correct. Each record in tbl_Surveys should get an associated temperature value.

 

[jdraw]

Had to go out, will get back later.

 

[jdraw]

Here is a procedure to find the appropriate tbl_Temperature record and the temperature(f) to assign to the record in tbl_Surveys.
I have done it as a procedure since it seemed easiest.

Note: I did assign an autonumber to tbl_Temperatures field IDJ mainly for verification.

This code finds the record with Min Datediff between SurveyDate and date(ET) for each record in tbl_Surveys. I write the output to the immediate window via a debug.print which is documented within the code.

You could create a new table for your own use with these records, or you could adjust the code to update the tempC field in tbl_Surveys directly, your choice. There are 371 records in the output.

Good luck.

'---------------------------------------------------------------------------------------
' Procedure : GetTempsForMinDateDiff
' Author    : Jack
' Date      : 24/08/2012
' Purpose   :We are trying to associate a Temperature reading to a Park (not park point)
'in table survey based on the finding the record in Table Temperature where the Datediff value
'between tbl_Survey surveyDate Date/Time and tbl_Temperature DateET Date/Time  is the minimum.
'---------------------------------------------------------------------------------------
'
Sub GetTempsForMinDateDiff()
    Dim DB As DAO.Database
    Dim rsSur As DAO.Recordset
    Dim rsTemp As DAO.Recordset
    Dim ChkSurveyDate As Date
    Dim strT As String
    Dim MinDate As Long
    Dim HoldDiff As Long
    Dim SurveyRecId As Long
    Dim iCnt As Integer
10    On Error GoTo GetTempsForMinDateDiff_Error

20    Set DB = CurrentDb
30    Set rsSur = DB.OpenRecordset("Select pwrc_Point_SurveyID,Park,surveyDate,TempC from tbl_Surveys order by 1,2,3")

40    rsSur.MoveLast
    'Debug.Print rsSur.RecordCount
50    rsSur.MoveFirst
60    Do While Not rsSur.EOF
70      ChkSurveyDate = rsSur!surveyDate
80      iCnt = iCnt + 1
        'Debug.Print ChkSurveyDate & rsSur!Park & rsSur!surveyDate
        
90      strT = "Select Park, [Date(ET)],[Temperature(f)],idj from tbl_temperature " _
             & " WHERE  Park = '" & rsSur!Park & "' AND Day([Date(ET)]) = " & Day(rsSur!surveyDate) & " And Month([Date(ET)]) = " & Month(rsSur!surveyDate) & " And Year([Date(ET)]) = " & Year(rsSur!surveyDate) _
             & " order by 2 "
        'Debug.Print strT
100     Set rsTemp = DB.OpenRecordset(strT)
110     HoldDiff = 99999   'assign a high value as starting value
120     Do While Not rsTemp.EOF
     'Find the absolute minimum differenct in the SurveyDate and records for this Park and Date(ET) in the temperature table
130         MinDate = Abs(DateDiff("s", rsSur!surveyDate, rsTemp![Date(ET)]))
140         If MinDate < HoldDiff Then
150             HoldDiff = MinDate
160             HoldId = rsTemp!idj
170             SurveyRecId = rsSur!pwrc_Point_SurveyID
180         Else
190         End If
200         rsTemp.MoveNext
210     Loop

       ' Write the details of the record closest match to date/time -
       ' which record to output sequential reference
       ' pwrc_Point_SurveyID in recordset rsSurvey
       ' PARK
       ' SurveyDate
       ' My IDJ in tbl_temperature (my autonumber)
       ' Date(ET)
       ' difference between Dates  Datediff in seconds
       ' Temperature(f)
   '****
   '****  You can write these to a table or directly to tbl_Surveys
   '****
220     Debug.Print iCnt & "  " & SurveyRecId & "  " & rsSur!Park & "  " & ChkSurveyDate & "  Closest Temperature DateRec " & HoldId & "  " & DLookup("[Date(ET)] ", "tbl_temperature", "IDJ = " & HoldId) & "  difference(seconds) = " & HoldDiff & "  " & DLookup("[temperature(f)] ", "tbl_temperature", "IDJ = " & HoldId)
230     rsSur.MoveNext
240   Loop
250   On Error GoTo 0
260   Exit Sub

GetTempsForMinDateDiff_Error:

270   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetTempsForMinDateDiff of Module Module1"
End Sub

 

[vbaInet]

While this sounds like a simple problem I've only been able to do it using a series of queries - pretty much because of the need to run an aggregate query to find the minimum date difference. I also want to do this in the background (via VBA) and I'm not entirely sure how to link 2 SQL statements in VBA. Does anyone have a more elegant solution than my series of queries (see below)? Thanks!

So there's no problem here? You are already doing this using a series of queries?

To be honest, your queries will run faster than looping through a recordset in code in most cases. If you have it working using queries, I would stick to it. Nevertheless, there's no harm in comparing execution times between the two methods.

 

[jdraw]

I agree with vbaInet that SQL queries are usually faster that recordset processing.
However, I saw that you were using

Abs(([SurveyDate]-[Date(ET)])*24) AS DateDiff

which showed me you were not using or aware of the Datediff function.

I just found a recordset option easier for me to understand the processing, and have commented the code to help you in understanding what was done. It also seemed to me that this was a one time exercise, so speed of execution was not a driving factor for me.

And -from the basic nature of the post- it appears you are trying to match data from somewhat unrelated tables and use the results in some sort of analysis.

This line is my attempt to select only the relevant records in tbl_temperature for matching a record in tbl_surveys.

 strT = "Select Park, [Date(ET)],[Temperature(f)],idj from tbl_temperature " _
             & " WHERE  Park = '" & rsSur!Park & "' AND Day([Date(ET)]) = " & Day(rsSur!surveyDate) & " And Month([Date(ET)]) = " & Month(rsSur!surveyDate) & " And Year([Date(ET)]) = " & Year(rsSur!surveyDate) _
             & " order by 2 "