Query to combine two tables

[boerbende]

Dear readers

Assume two tables.
Table 1: 5 columns
date from; time from; date to; time to; measurement1 for this period
1-12-15 ; 08:00 ;1-12-15 ; 19:59 ; 0,89
1-12-15 ; 20:00 ;2-12-15 ; 07:59 ; 0,71
2-12-15 ; 08:00 ;2-12-15 ; 19:59 ; 0,91
2-12-15 ; 20:00 ;3-12-15 ; 07:59 ; 0,33
3-12-15 ; 08:00 ;3-12-15 ; 19:59 ; 0,56
…
And
Table 2: 3 columns
date ; time ; measurement2
1-12-15 09:00 0,52
1-12-15 10:00 0,48
1-12-15 15:15 0,52
1-12-15 20:10 0,51
2-12-15 09:00 0,91
2-12-15 11:00 0,95
2-12-15 22:00 0,33
3-12-15 09:00 0,56
…
I would like to combine those two with a query, but how?

Result I would like to achieve is
All records + columns from Table 1 + columns Min, AVG, Max from table 2 in this period
date from ; time from ; date to ; time to; measurement1, min(measurement2), avg(measurement2), max(measurement2) in the same period
1-12-15 ; 08:00 ;1-12-15 ; 19:59 ; 0,89 ; 0,48 ; 0,50 ; 0,52 (min, max, avg between 08:00-20:00)
1-12-15 ; 20:00 ;2-12-15 ; 07:59 ; 0,71 ; 0,51 ; 0,51 ; 0,51 (min, max, avg between 20:00-08:00)
2-12-15 ; 08:00 ;2-12-15 ; 19:59 ; 0,91 ; 0,91 ; 0,93 ; 0,95 (min, max, avg between 08:00-20:00)
Etc…

Many thanks

Ben

 

[plog]

You really need to make it clear to the reader how to parse your data. Where does one value end and the other being? This can be done by using field names where you declare your delimiter (character to seperate fields).

YourTableNameHere
Field1Name; Field2Name; Field3Name;...
7/1/2015; Sally; 17
8/2/2015; Dave; 41
...

Even though I can't follow your complete question and see your data, I do know that combining data from tables via a query, requires a UNION (http://www.w3schools.com/sql/sql_union.asp). Look into that, and if you need more help, repost data as I have demonstrated.

 

[llkhoutx]

It seems that you want 8 queries: 2 time intervals plus min, max, avg for each. The join those queries on time interval in a query to produce the desired columns you want.

 

[boerbende]

Sorry, I see my question was not clear enough. Hope the addition makes it a little better

 

[plog]

I see it now, and you have your data improperly structured. You need to have all your date/time data in one field. It's called a Date/Time data field for that reason--it can hold both. That would be the most efficient way to accomplish this...

However, you can use your cartesian product query and apply criteria. You should have Table1 and Table2 in your query, but unlinked, correct? What you need to do is leave them unlinked and then build a long IIF statement to make sure Table2 records fall in Table1 records timeframes. You have your Date/Times together this becomes easier (still not the efficient way to do this, but easier), but since you don't its going to be a long IIF statement. This isn't actual code, just psuedo code to demonstrate logic.

Include: IIF(Table2Record>=Table1Start AND Table2Record<=Table1End, 1,0)

Then below that in the criteria portion you put 1, to include only records that pass the above test.

Its your cross to bear to write the actual code if you choose this method, again, fix your date/times and you can use a JOIN to accomplish the same thing. That code would be an INNER JOIN clause like so:

INNER JOIN Table2 ON Table2.DateTime>=Table1.Start AND Table2.DateTime<=Table1.End

 

[boerbende]

Thank you plog.
More people react that some things are not correct and I totally agree, but I can't help it :-( . I am working with data coming from databases I cannot change. But to rework it to datetime is not a problem. This is just a formula: date + time.

In your response I see something which is an eyeopener for me ... a join on a constraint. I will work further with this, I think this opens some possibilities for a second SELECT in a nested SQL for the min, avg and max

 

[plog]

Just because you get data in one manner doesn't mean you have to work with it in that manner. What you should do is use queries to get it in a proper form. I would build a query for each of your tables and get your date/time fields together. You can do this via the CDATE function (http://www.techonthenet.com/access/functions/datatype/cdate.php).

For example:

StartTime: CDATE(DateFrom & " " & TimeFrom)

Would make a field with the Date/TIme in one field which would then make it a lot easier to compare with.