By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Compare times in two tables in MS Access

P: 8
I have 2 tables with date and time as separate fields. Table A has records every hour on the hour, Table B has records once a day (not on the hour). How do I compare data from the record from Table A that corresponds as close as possible to the record from Table B?
Jan 11 '17 #1

✓ answered by NeoPa

Datasets in databases are not items which are relative to each other unless tweaked to be so. Your data doesn't lend itself to that as it stands. If you read the article you'll have noticed that to compare overlapping time periods you need a time period. IE. From one time to another. Nearest means nearer to a value than all others, which doesn't compute as each record is treated independently of all the others.

Nearest and other comparitive functions can be designed in code, but you miss out on the power that databases were designed for. It seems to me it would make more sense to define the layout of the data in such a way as to allow it to be used more naturally by a database. That means either defining from and to fields in your data or asserting an assumption such that the data is always taken on the hour and a particular record from table B matches a record in table A if it comes between the time -30' and that same time +29'59".

Sometimes when designing systems it's about finding a way that is approximately what you need when what you need is not supported.

PS. For anyone else reading this I should finish off Lisa's explanation by saying that records #1 & #3 in table B are red herrings with no matching example data in table A. What is required for record #2 though, is to match this with record #3 in table A. This is the closest date/time available from table A to the value in table B.

Share this Question
Share on Google+
15 Replies


PhilOfWalton
Expert 100+
P: 1,430
Are the tables related?

What sort of output are you looking for? Is it the basically the date time from Table B (and any other relevant table B information in the left hand columns of a query and the closest date/ time & relevant data from Table A in the right hand columns of the query?

Phil
Jan 11 '17 #2

P: 8
I have tried to join the tables using the Date field.

The tables are data from two different instruments and I need to use a parameter from Table A and a parameter from Table B at the same date and time to perform a calculation
Jan 11 '17 #3

NeoPa
Expert Mod 15k+
P: 31,186
Hi Lisa.

Why don't you try to explain exactly what you're trying to achieve and include some example data from the two tables. You can further explain what you expect as a result from the sample data and we would probably be able to form an idea of what you want.

We can offer a whole bunch of suggestions, but without a clear understanding of what you're after they will mainly be stabs in the dark.

To give you something to help you understand here (Time Interval Overlapping (MS Access 2003)) is a hopefully helpful link.
Jan 12 '17 #4

P: 8
So here’s what I’m trying to do:
I have data from a weather station in Table A: Date, Time, Temperature, Pressure…and a whole lot more parameters, eg.
Expand|Select|Wrap|Line Numbers
  1.  Date  Time  Temp Pressure
  2. 1/1/16 11:00 31.0 1012
  3. 1/1/16 12:00 31.1 1012
  4. 1/1/16 13:00 32.0 1011
I also have data from depth logger in Table B: Date, Time, Level, eg.
Expand|Select|Wrap|Line Numbers
  1.   Date   Time  Level
  2. 31/12/16 12:32 8.32
  3.  1/ 1/16 12:32 8.35
  4.  2/ 1/16 12:32 8.27
I need to use the Pressure value from Table A to correct the Level from Table B, so I need to select the value for pressure at the date and time closest to the time the water level was logged. Does that make more sense? I’m not very good with code (maybe this is the wrong forum?), so I was hoping I could achieve this with queries.
Jan 12 '17 #5

NeoPa
Expert Mod 15k+
P: 31,186
Datasets in databases are not items which are relative to each other unless tweaked to be so. Your data doesn't lend itself to that as it stands. If you read the article you'll have noticed that to compare overlapping time periods you need a time period. IE. From one time to another. Nearest means nearer to a value than all others, which doesn't compute as each record is treated independently of all the others.

Nearest and other comparitive functions can be designed in code, but you miss out on the power that databases were designed for. It seems to me it would make more sense to define the layout of the data in such a way as to allow it to be used more naturally by a database. That means either defining from and to fields in your data or asserting an assumption such that the data is always taken on the hour and a particular record from table B matches a record in table A if it comes between the time -30' and that same time +29'59".

Sometimes when designing systems it's about finding a way that is approximately what you need when what you need is not supported.

PS. For anyone else reading this I should finish off Lisa's explanation by saying that records #1 & #3 in table B are red herrings with no matching example data in table A. What is required for record #2 though, is to match this with record #3 in table A. This is the closest date/time available from table A to the value in table B.
Jan 12 '17 #6

P: 8
So, I should change the Time in Table B from 12:32 to 12:00 - then I could use
Expand|Select|Wrap|Line Numbers
  1. LIKE TblA.Time
in a query?
Jan 12 '17 #7

P: 8
I have renamed the Time fields to have different names in each table. I have also renamed the Date field to SampleDate (as per your other post). I formatted the Time fields to be "hh" and then applied this query:
Expand|Select|Wrap|Line Numbers
  1. SELECT TableB.Location, TableB.SampleDate, TableB.GWTime, TableB.LEVEL, TableA.WeatherTime, TableA.Pressure
  2. FROM TableB LEFT JOIN TableA ON TableB.SampleDate = TableA.SampleDate
  3. WHERE (((TableA.WeatherTime) Like [TableB].[GWTime]));
  4.  
Unfortunately I get zero results
Jan 12 '17 #8

P: 8
So, of course formatting wouldn't work!! I've replaced the time values with hourly values in TableB and using the above query I now have one record for TableA corresponding to the record from TableB.

Thanks - I just took a quantum leap in understanding code!
Jan 12 '17 #9

NeoPa
Expert Mod 15k+
P: 31,186
Hi Lisa.

When I refreshed the page earlier I saw I'd caught your earlier post within a minute of you posting it. After posting that I got busy with some other stuff. Now I come back and find that my not responding so quickly has encouraged you to delve a little deeper and find answers for yourself.

Excellent. It doesn't always work that way but it's so great when it does. I'm very happy for you.

Let me add a couple of ideas while I'm here :
  1. Formatting of data should only ever be done to display to a human. It almost never makes sense to process formatted data. Like most things there are rare exceptions.
  2. Consider storing dates with times in the same field. It makes comparisons easier, even if it takes a little more brain power to understand how best to use them that way. Just because humans think of them as separate items doesn't mean they should be stored or processed that way. They seem like two items of data but they're just an identification of a specific point in time.
  3. Points in time (instants) and periods of time are as incomparable as lines are with positions. You can see when two periods overlap, and even when an instant falls within a period, but instants can only be matched if they're exact matches.
Lastly, take your progress step by step. Once you have one concept understood and applied make sure you keep a copy of that until you go to the next level and get that working. Even now I jump into the next level sometimes and get into a mess because I struggle when I thought it would all be straightforward.
Jan 13 '17 #10

P: 8
Thanks for the advice. Regarding point 2: The dates and times are downloaded from the instruments as separate files, so to store them together I would have to join the fields, which seemed to be a step not needed in this instance.
My next step will be to extract a range of data from the Weather table to compare to a different instrument that runs for 24 hours, so your last 2 pieces of advice are very timely!.
Thanks again.
Jan 13 '17 #11

NeoPa
Expert Mod 15k+
P: 31,186
Always a pleasure. What else would I get up to in freezing London at this time of the morning. I hope you're somewhere warmer and comfortable.
Jan 13 '17 #12

P: 8
Ha ha - It's a balmy 42 degrees here in Newcastle, NSW, Australia!
Jan 13 '17 #13

NeoPa
Expert Mod 15k+
P: 31,186
:-( Nooooooo!

I'm off to bed.
Jan 13 '17 #14

PhilOfWalton
Expert 100+
P: 1,430
Hi Lisa. 42 is not balmy, it's as hot as hell.

My first thoughts were using VBA to create an outer loop of dates based on table B sorted in date order. Then an inner loop based on table A again based on date/time order.
Use the DateDiff function to find the Absolute difference between the two dates (we are not interested if the difference is positive or negative i.e. which date is first). Store that date and the difference in seconds as well as the temperature & pressure. Read the next date/time from table A and if that difference is less than the previous one, store that date and difference in seconds. If the difference is greater, then we are moving away from our goal, so break out of the inner loop and we have our closest date/time.
Read the next date from Table B and repeat (i.e. the next record in the outer loop)

Without seeing your actual date, it is difficult to know if the next refinement would work, but assuming that data is available from both tables every day, the inner loop could be refined to only check dates in the range of -1 day and +1 day of the date found in the outer loop (Table B)

Phil
Jan 14 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
Further to my last post, I have played about with some tables that I have in a test database.
In my Table Payments1, equivalent to your table B I have almost 4000 records.
In my table HourlyOutputNew equivalent to your table A, I have nearly 300,000 records, so that's a lot of comparisons to make.

There are 2 versions of both query & VBA, so I will deal with them one at a time.

This is the SQL for a normal query
Expand|Select|Wrap|Line Numbers
  1. SELECT Payments1.PaymentAmount, Payments1.PaymentDate, Payments1.PaymentID, ClosestTime([PaymentDate]) AS ClosestDate
  2. FROM Payments1
  3. ORDER BY Payments1.PaymentDate;
  4.  
You need to substitute TableB for Payments1 and Level for PaymentAmount. The PaymentDate is eqivalent to your Date & Time in table B.

Here is the corresponding code:-
Expand|Select|Wrap|Line Numbers
  1. Function ClosestTime(DateIn As Date) As Date
  2. 'debug.Print ClosestTime(#31/07/2011 01:00:00#)
  3.  
  4.     Dim MyDb As Database
  5.     Dim TimeSet As Recordset
  6.     Dim SQLStg As String
  7.     Dim SecsDiff As Double
  8.     Dim SaveSecs As Double
  9.     Dim SaveDate As Date
  10.     Dim SaveOutput As Integer
  11.  
  12.     SQLStg = "SELECT HourlyOutputNew.* FROM HourlyOutputNew ORDER BY DateTime;"
  13.  
  14.     Set MyDb = CurrentDb
  15.     Set TimeSet = MyDb.OpenRecordset(SQLStg)
  16.  
  17.     SaveSecs = 1E+30                    ' Large number
  18.  
  19.     With TimeSet
  20.         Do Until .EOF
  21.             SecsDiff = Abs(DateDiff("s", DateIn, !DateTime))
  22.            '    Debug.Print SecsDiff
  23.             If SecsDiff < SaveSecs Then
  24.                 SaveSecs = SecsDiff           ' Save lower value
  25.                 SaveDate = !DateTime
  26.             Else
  27.                 Exit Do
  28.             End If
  29.             .MoveNext
  30.         Loop
  31. CloseIt:
  32.         .Close
  33.         Set TimeSet = Nothing
  34.     End With
  35.  
  36.     ClosestTime = SaveDate
  37.  
  38. End Function
  39.  
This works OK but takes a fair number of minutes to run as potentially there are 4000 * 300,000 comparisons to make. In practice it will be less as we exit the loop when the comparisons start to get worse.

Second way is similar but much faster (still minutes rather than seconds)

Here is the SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT Payments1.PaymentAmount, Payments1.PaymentDate, Payments1.PaymentID, ClosestTime2([PaymentDate],2) AS ClosestDate2
  2. FROM Payments1
  3. ORDER BY Payments1.PaymentDate;
  4.  
And here the corresponding VBA
Expand|Select|Wrap|Line Numbers
  1. Function ClosestTime2(DateIn As Date, DayRange As Integer) As Date
  2. 'debug.Print ClosestTime2(#31/07/2012#,4)
  3.  
  4.     Dim MyDb As Database
  5.     Dim TimeSet As Recordset
  6.     Dim SQLStg As String
  7.     Dim SecsDiff As Double
  8.     Dim SaveSecs As Double
  9.     Dim SaveDate As Date
  10.     Dim SaveOutput As Integer
  11.     Dim FromDate As Date, ToDate As Date
  12.  
  13.     FromDate = DateAdd("d", -DayRange, DateIn)
  14.     ToDate = DateAdd("d", DayRange + 1, DateIn)
  15.  
  16.     SQLStg = "SELECT HourlyOutputNew.* From HourlyOutputNew "
  17.     SQLStg = SQLStg & "WHERE HourlyOutputNew.DateTime Between #" & FromDate & "# AND #" & ToDate & "#;"
  18.  
  19.     Set MyDb = CurrentDb
  20.     Set TimeSet = MyDb.OpenRecordset(SQLStg)
  21.  
  22.     SaveSecs = 1E+30                    ' Large number
  23.  
  24.     With TimeSet
  25.         Do Until .EOF
  26.             SecsDiff = Abs(DateDiff("s", DateIn, !DateTime))
  27.            '    Debug.Print SecsDiff
  28.             If SecsDiff < SaveSecs Then
  29.                 SaveSecs = SecsDiff           ' Save lower value
  30.                 SaveDate = !DateTime
  31.             Else
  32.                 Exit Do
  33.             End If
  34.             .MoveNext
  35.         Loop
  36. CloseIt:
  37.         .Close
  38.         Set TimeSet = Nothing
  39.     End With
  40.  
  41.     ClosestTime2 = SaveDate
  42.  
  43. End Function
  44.  
The difference here is that in the query we use a parameter (in this case 2) and we only search dates between your table B dates + or - 2 days. Play around with this number till you get the results you require. The smaller the number the less comparisons are made, so the quicker the query will run. Ideally set it at 0.

Hope this helps

Phil
Jan 14 '17 #16

Post your reply

Sign in to post your reply or Sign up for a free account.