Time differences in separate tables

[dc_sc]

I asked this in a different thread and got no replies, so I am trying again in case it wasn't understood.

I want to calculate time differences between time fields in two tables. The start time and the end time are in two different tables, but there is no other unique field in both tables that I can link. Each table has the device number (which has several records with same device numbers and different 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.

I cannot copy the table here, but here is an example.
table 1
Number Time
1 6:00 am
1 7:30 am
2 6:30 am
2 9:15 am

table 2
Number Time
1 6:05 am
1 7:36 am
2 6:33 am
2 9:20 am

The results I want here would be 5, 6, 3, and 5 minutes. How do i make sure the first record 1 compares the first record 1 in the other table, and so on.

The more I try to explain this, the more impossible it sounds.

Any ideas?

 

[Jon K]

These three queries should do it:

Query1:-
SELECT a.Number, a.[Time], (Select Count(*) from [Table 1] where [Number]=a.[Number] and [Time]<=a.[Time]) AS Num
FROM [Table 1] AS a;

Query2:-
SELECT a.Number, a.[Time], (Select Count(*) from [Table 2] where [Number]=a.[Number] and [Time]<=a.[Time]) AS Num
FROM [Table 2] AS a;

Query3:-
SELECT Query1.Number, Query1.[Time] AS StartTime, Query2.[Time] AS EndTime,
DateDiff("n",[Query1].[Time],[Query2].[Time]) AS TimeDiff
FROM Query1 INNER JOIN Query2 ON (Query1.Num = Query2.Num) AND (Query1.Number = Query2.Number);

You can open the attached demo DB and run Query3.

The Num fields in the first two queries are used as the record identifier for the linking purpose in Query3.

Note: To open the DB in Access 2000 or 2002, just choose Convert and save as a new name when it is opened for the first time.

 

[dcx693]

The solution is much easier than you think. Given that each beginning time in table1 must have a corresponding end time in table2, what you need is some easy way to compare each record of table1 with the corresponding record from table2. How to tell Access that record1 from table1 should be compared against record1 from table2? You need some way to uniquely identify each record of each table - and that's exactly what a primary key is for.

In this case, adding an autonumber field to each table makes this a simple exercise. (If you are not allowed to alter the structure of the original tables, make copies of them.) Once that's done, record1 on table1 is marked with autonumber1, record2 is marked with autonumber2, etc... The same thing with table2.

Once that is accomplished, just create a query with the two tables, join the two on the autonumber fields you just created, and put this expression into a field line:
TimeDiff: DateDiff("n",[tblBegin].[Time],[tblEnd].[Time])
This assumes your tables are called tblBegin and tblEnd. The DateDiff function above, using the "n" parameter, calculates the difference in minutes between two values.