Calculate difference of values

[dc_sc]

I have a table with a date field that has date and time together. I want to use a query (not code, please) to return only those values that are within five minutes of each other. In other words, if my records are: 5/30/03 5:55 am, 5/30/03 6:05 am and 5/30/03 6:08 am - I want to only return the last two records. My table may have hundreds of records with times ranging throughout a 24 hour period and I only am concerned with those records that occurred close together in time.

I hope that makes sense. Please go easy on me. I am a novice and know very little about modules and code.

 

[FoFa]

Are these to columns in the same row (2 different date columns) or are you trying to get rows within 5 mins of each other?

ColA Dt1 Dt2
and I want all rows where DT1 is within 5 mins of Dt2
Or
Key1 6:05
Key2 6:08
Key3 7:45

And you want rows Key1 and Key2 returned?

 

[dcx693]

This is possible just using a query and no code. I created a sample table, using number values and not time values, but the extension to time values shouldn't be hard.

This is how I structured my sample table, called tblNameTime:
Person Time
John 1
Andy 2
Cindy 5
Joe 10
Bob 20

I created a query to find which person's time would be within 5 units of any other person's.

I created a select query and added the tblNameTime twice. Access will call the second copy of the table tblNameTime_1. I used the alias property to change it to tblNameTimeCopy, but you don't have to.

Why did I add the table twice? Because I wanted to compare the table to itself. Don't create any joins between the tables. I created a non-equi join where values from the first copy of the table would be matched up to values in the second table if they were between 0 and 5. This was the field calculation I created: [tblNameTimeCopy].[Time]-[tblNameTime].[Time]
and the criteria was 0> and <5.

Look at the sample database I uploaded and see if it makes sense to you.

 

[indyaries]

db format ??

dcx693,

What format is your attachment in? Most people post in Access 97 (which is what I have here at work).

Could you repost the db in Access 97? Thanks !!

 

[dcx693]

It was in Access 2000 format. I've attached an Access 97 converted copy.

 

[dc_sc]

DCX693, that looks great. I will try it this weekend. My next hurdle is doing the time diff with that long date format, but I think I know what to do. Thanks again! (FoFa, your second one is correct. The times are all in the same field.)

 

[dc_sc]

It's working, but a little trouble

Your query works great with my table, but I have a small problem. The >0 and <5 did not return values that were exactly the same. (0 difference). I need those values also, so, I changed it to >=0 and it returned a bunch of duplicate values that were not right at all. I think my problem is grouping. I had to change a bunch of stuff to get it to group correctly and I probably messed something up.

So, to explain better, here are the relevant fields in my table. Machine#, Time, amount. I want to know if any transactions occur at a specific machine for the same amount within five minutes of each other. I hope this is clear and I hope you can continue to help.

 

[dcx693]

Can you post a copy of your db here so I can take a look?

 

[dc_sc]

Sorry my reply took so long. i cannot attach anything from work, but here are more details.

My table has device number, date/time, amount, and other stuff. I need to find transactions on the same device for the same amount that occur within five minutes or less of each other. This must include transactions that occurred at exactly the same time.

1234, 6/2/03 6:00:00 pm, $50
1234, 6/2/03 6:00:00 pm, $50
1234, 6/2/03 6:00:55 pm, $50
...and about 900 other transactions on same and different numbers amounts and times.

 

[dcx693]

One problem I can see happening when you change the criteria from 0< And >5 is that you'll also start getting the results where the times from one table get compared to the same times in the other table.

For example, if you have this data:
1234, 6/2/03 6:00:00 pm, $50 (recordA)
1234, 6/2/03 6:00:00 pm, $50 (recordB)
1234, 6/2/03 6:00:55 pm, $50 (recordC)

You'll get recordA being compared to recordA as well as being compared to records B and C. The criteria 0<And>5 conveniently gets rid of those, but at a cost. You actually do want recordA being compared to recordB, but not recordA.

How to get recordA to compare to recordB, but not to itself? You need some way to identify each record uniquely, that's what a primary key is for. If you don't have one in your table, I'd strongly suggest you add one.

With a primary key field, your data would look something like this:
1 1234, 6/2/03 6:00:00 pm, $50 (recordA)
2 1234, 6/2/03 6:00:00 pm, $50 (recordB)
3 1234, 6/2/03 6:00:55 pm, $50 (recordC)

Let's say you call the primary key field TimeID. Add the TimeID field from the tblNameTime table to your query and include this criteria for it:
<> [tblNameTimeCopy].[TimeID].

 

[dc_sc]

That worked great! I do have a primary key field, i just wasn't using it. Thanks so much. How about another one? Similar, but different.

I want to calculate time differences again. This time the start time and the end time are in two different tables, but there is no unique field in both tables that I can link. Each table has the device number (which repeats several times) and the date/time. The only consolation is that each transaction ends before the next one begins, so chronologically it would look like start time, end time, start time, end time, etc.

Thanks