I have built code that will query fields from text files. I have 2 text files (avTable) & (rtTable).
The following SQL statement works great:
These 2 tables contain dates, hours, and rates. avTable has complete data (ie all hours for every date), rtTable does not. What SQL statement will return me a recordset that has Date, Hour, AvgRate from the rtTable and Date, Hour, AvgRate from the avTable when the rtTable does not have that item (ie Hour). Note field names are different: avTable Field names = Date, Hour, Average; rtTable field names = DateOf, Hour, AvgRate.
For exampl: rtTable has for 1/1/2011 an avgRate for hours 1, 2, & 4. I want a recordset that has those data points but then also the missing hour 3 from the avTable. So my new recordset will have Date, hour, Rate for 1/1/2011 hours 1, 2, 3, & 4
I hope I makes sense.
The following SQL statement works great:
Code:
GetJoinQuery = Join$(Array( _
"SELECT av.Date, av.Hour, rt.AvgRate", _
"FROM `" & avTable & "` av, `" & rtTable & "` rt ", _
"WHERE av.Date = rt.DateOf AND av.Hour = rt.Hour ", _
"ORDER BY av.Date, av.Hour"), vbCrLf)These 2 tables contain dates, hours, and rates. avTable has complete data (ie all hours for every date), rtTable does not. What SQL statement will return me a recordset that has Date, Hour, AvgRate from the rtTable and Date, Hour, AvgRate from the avTable when the rtTable does not have that item (ie Hour). Note field names are different: avTable Field names = Date, Hour, Average; rtTable field names = DateOf, Hour, AvgRate.
For exampl: rtTable has for 1/1/2011 an avgRate for hours 1, 2, & 4. I want a recordset that has those data points but then also the missing hour 3 from the avTable. So my new recordset will have Date, hour, Rate for 1/1/2011 hours 1, 2, 3, & 4
I hope I makes sense.
Last edited:
