Merge two tables in query by date/time, preserving hourly time in one of the tables (1 Viewer)

RoGo

New member
Local time
Yesterday, 22:18
Joined
Aug 5, 2025
Messages
5
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
 
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.
 
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.
 
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.
 
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:

Code:
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.
 
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 :(.
 
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:

Code:
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.
 

Users who are viewing this thread

Back
Top Bottom