Merge two tables in query by date/time, preserving hourly time in one of the tables

[RoGo]

Folks –

New to the forum, so please bear with me.

I’m looking to do a simple merge of two tables containing date and time (MM/DD/YY HH:MM) information. The first table has hourly records of river flows:

Flow_DateTime Flow
1/1/22 0:00 13400
1/1/22 1:00 13900
1/1/22 2:00 14100
1/1/22 3:00 14300
1/1/22 4:00 14300
1/1/22 5:00 13700
1/1/22 6:00 13700
1/1/22 7:00 13600
1/1/22 8:00 13500
1/1/22 9:00 13400
1/1/22 10:00 13300
1/1/22 11:00 13200
1/1/22 12:00 13000
1/1/22 13:00 12900
1/1/22 14:00 12700
1/1/22 15:00 12500
1/1/22 16:00 12300
1/1/22 17:00 12200
1/1/22 18:00 12100
1/1/22 19:00 12000
1/1/22 20:00 12100
1/1/22 21:00 12200
1/1/22 22:00 12300
1/1/22 23:00 12300
1/2/22 0:00 12300
1/2/22 1:00 13200
1/2/22 2:00 13500
1/2/22 3:00 12900
1/2/22 4:00 13400
1/2/22 5:00 13600
1/2/22 6:00 14000
etc.

The second table has just daily records of fish counts (nominal time of 0:00):

Count_Date_Time Fish_Count
1/1/22 0:00 8
1/2/22 0:00 7
1/3/22 0:00 6
etc.

What I’d like to do is merge the two tables but preserve the hourly flow measurements, like this (Fish_Count cells blank for all times other than 0:00):

Flow_DateTime Flow Fish_Count
1/1/22 0:00 13400 8
1/1/22 1:00 13900
1/1/22 2:00 14100
1/1/22 3:00 14300
1/1/22 4:00 14300
1/1/22 5:00 13700
1/1/22 6:00 13700
1/1/22 7:00 13600
1/1/22 8:00 13500
1/1/22 9:00 13400
1/1/22 10:00 13300
1/1/22 11:00 13200
1/1/22 12:00 13000
1/1/22 13:00 12900
1/1/22 14:00 12700
1/1/22 15:00 12500
1/1/22 16:00 12300
1/1/22 17:00 12200
1/1/22 18:00 12100
1/1/22 19:00 12000
1/1/22 20:00 12100
1/1/22 21:00 12200
1/1/22 22:00 12300
1/1/22 23:00 12300
1/2/22 0:00 12300 7
1/2/22 1:00 13200
1/2/22 2:00 13500
1/2/22 3:00 12900
1/2/22 4:00 13400
1/2/22 5:00 13600
1/2/22 6:00 14000
etc.

But instead, by using a simple Select query linking the tables by Flow_DateTime and Count_Date_Time, the query collapses everything down to daily records (presumably because Count_Date_Time is the "lowest common denominator"):

Flow_DateTime Flow Fish_Count
1/1/22 0:00 13400 8
1/2/22 0:00 12300 7
1/3/22 0:00 14900 6
etc.

Is there a way to preserve the hourly lines of flow data in the query? Different query type? Use specific criteria?

Examples welcome; thanks for your help.

-R

 

[MarkK]

simple merge

Maybe it is actually a difficult merge?

My first suggestion is to post your SQL, because mucking around with what you already have will make it faster to help.

What might work is a LEFT JOIN or RIGHT JOIN on the fields relating the tables. You might be using an INNER JOIN, which only returns rows where there is an exact match between field values.

 

[RoGo]

I was using an inner join. Modifying it to a join with arrow pointing from Flow_DateTime to Count_DateTime (presumably an OUTER join [RIGHT join?]) fixed the problem - many thanks MarkK.

Will try to post SQL next time. Again, new to the forum so have some learning to do.

 

[Gasman]

I was using an inner join. Modifying it to a join with arrow pointing from Flow_DateTime to Count_DateTime (presumably an OUTER join [RIGHT join?]) fixed the problem - many thanks MarkK.

Will try to post SQL next time. Again, new to the forum so have some learning to do.

Look at the SQL view, that will tell you which.

 

[plog]

Let's call your tables tblFish and tblFlow. If the date/time date in tblFlow is a superset of tblFish, you can easily do this with a LEFT JOIN:

SELECT *.tblFlow, Fish_Count
FROM tblFlow LEFT JOIN tblFish ON Flow_DateTime = Count_DateTime

However, I don't trust your data--or anybodies data ever. I suggest you prove to yourself that not only tblFlow is a superset on the Date_Time field and verify that it will always be that way.

If it is not, or more importantly you want to future proof it against missing data in the future, you will need a more complex solution using a UNION query to generate a superset list of all DateTimes in both tables.

 

[RoGo]

SQL view says LEFT join. Thanks for pointing that out.

Again although I have been using Access for 20+ years, I've only been doing so in a basic way. Today was the first time I've looked at SQL view. I used to let my techs and grad students do the heavy lifting with Access; paying the price now in retirement .

 

[RoGo]

Let's call your tables tblFish and tblFlow. If the date/time date in tblFlow is a superset of tblFish, you can easily do this with a LEFT JOIN:

SELECT *.tblFlow, Fish_Count
FROM tblFlow LEFT JOIN tblFish ON Flow_DateTime = Count_DateTime

However, I don't trust your data--or anybodies data ever. I suggest you prove to yourself that not only tblFlow is a superset on the Date_Time field and verify that it will always be that way.

If it is not, or more importantly you want to future proof it against missing data in the future, you will need a more complex solution using a UNION query to generate a superset list of all DateTimes in both tables.

Agreed - need to verify results. The flow data are pretty complete in terms of dates/times, though, so I'm comfortable with using that as a superset.

 

[The_Doc_Man]

The only thing I need to offer in warning is that IF the date/times are stored in the "traditional" way - as a typecast of a DOUBLE, then there is a potential for errors that would prevent traditional JOIN clauses from finding anything. You could make the date/time fields into a string, which would then be exact and JOINs would work. However, it costs 50% more space to store the date/time key as a string in yyyymmddhhnn format - 12 characters vs. the 8 bytes of a DOUBLE data type or DATE data type.

The problem is caused by the time portion of a "standard" date/time field being fractional and expressed as a BINARY fraction when in fact the time of day is based on sexagesimal numbers. (Don't worry, it's a good word... means "base 60".) Sexagesimal factors to 6 x 10 or 12 x 5 or 2 x 3 x 5. Expressing either 1/3 or 1/5 IN BINARY is an irrational fractional number. The problem with irrational fractions on a computer is that the way you reached that particular time value makes a huge difference in how the number is represented internally. The fact that you would be implicitly truncating the number when you got rid of the seconds portion doesn't help.

 

[Gasman]

Doc,
Could you just use DateValue() to compare like with like?

 

[The_Doc_Man]

DateValue should be reliable enough to make it an apples-to-apples comparison if both comparands are strings and are converted using the same function. However, function DateValue converts strings to DATE data type for its return value. If one of the compared values is converted another way (i.e. CDATE(quoted-string) or CDATE(long-integer) as opposed to DATEVALUE(quoted-string) (or perhaps a comparand is originally a DATE data type) then the implied conversions are not done the same way and potential exists for rounding errors during the conversions. Or at least, that is how I would approach the comparison problem.

I would HOPE that DateValue and CDATE would return the same value of their DATE variable if given the same input strings. However, that is a test I haven't performed in depth. And if some other method was used to prepare one of the dates, all bets are off. For instance, the OP's data shows time in hours. But if some of the raw times were collected via NOW(), then minutes and seconds would be a hidden part of the result.

 

[Ken Sheridan]

I'd suggest joining the tables on the formatted date/time values to ensure equality:

SELECT
    tblFish.*,
    tblFlow.Fish_Count
FROM
    tblFish
    LEFT JOIN tblFlow ON FORMAT(tblFish.Flow_DateTime, "yyyymmddhh")
                       = FORMAT(tblFlow.Count_Date_Time, "yyyymmddhh");