VBA Code to look at 2 tables and work out time difference

[moonman84]

Hi there all

I have 2 tables, table1 and the the other table2.

They have the same fields:

Name, Fault and timestamp

I want to get the time difference from table1.timestamp to table2.timestamp

I have tried running this a query but get duplication on data.

wonder if anyone can help.

Many thanks

 

[Andrzejek]

I have tried running this a query

Could you show the query?

Are the records in table1 and table2 unique (no duplicates)?

Have fun.

---- Andy

 

[moonman84]

Hi Andy

thanks for looking at my post.

in the query i pulled in both tables, and used sec: DateDiff('s',[table1].timestamp,[table2].timestamp)

results are below:

Name Fault table1.timestamp Name Fault Table2.timestamp sec
gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:41:35 60
gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:41:35 60
gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:43:35 180
gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:43:35 180

there should be just 2 records but there are 4...

abdul

 

[PHV]

What is your SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[moonman84]

SELECT table1.[Name], table1.[Fault], table1.[timestamp], table2.Name, table2.Fault, table2.timestamp, DateDiff('s',[table1].[timestamp],[table2].[timestamp]) AS sec
FROM table1, table2;

 

[PHV]

Replace this:
FROM table1, table2
with this:
FROM table1 INNER JOIN table2 ON table1.Name = table2.Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[moonman84]

just tried it still got 4 records results is below:

Name Fault table1.timestamp Name Fault table2.timestamp sec
gas1 1 27/06/2011 15:42:35 gas1 0 27/06/2011 15:41:35 -60
gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:41:35 60
gas1 1 27/06/2011 15:42:35 gas1 0 27/06/2011 15:43:35 60
gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:43:35 180

SQL code as below:

SELECT table1.Name, table1.Fault, table1.timestamp, table2.Name, table2.Fault, table2.timestamp, DateDiff('s',[table1].[timestamp],[table2].[timestamp]) AS sec
FROM table1 INNER JOIN table2 ON table1.Name = table2.Name;

 

[PHV]

still got 4 records
Yes, but no duplicate.
Which result did you expect ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[moonman84]

if you look at the time stamps there are duplicates.

the original tables are below:
table 1:
gas1 1 27/06/2011 15:40:35
gas1 1 27/06/2011 15:42:35

table 2:
gas1 0 27/06/2011 15:41:35
gas1 0 27/06/2011 15:43:35

the results of the query puts table 1 and 2 in same row which i wanted but the time stamps are messed up... and i was expecting only 2 rows not 4....

 

[Andrzejek]

Try:
[tt]SELECT [blue]DISTINCT[/blue] table1.Name, table1.Fault, table1.timestamp, table2.Name, table2.Fault, table2.timestamp, DateDiff('s',[table1].[timestamp],[table2].[timestamp]) AS sec
FROM table1 INNER JOIN table2 ON table1.Name = table2.Name;
[/tt]

Have fun.

---- Andy

 

[PHV]

i was expecting only 2 rows
Which rows ?
Just a guess:

SELECT A.Name, A.Fault, A.timestamp, B.Name, B.Fault, B.timestamp, DateDiff('s',A.timestamp,B.timestamp) AS sec
FROM (table1 A
INNER JOIN table2 B ON A.Name = B.Name)
INNER JOIN (
SELECT Name, MIN(timestamp) AS ts FROM table2 WHERE Name=table1.Name AND timestamp>table1.timestamp
GROUP BY Name) AS X ON table2.Name=X.Name AND table2.timestamp=X.ts

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

moonman84,

You have been VERY STINGY with information!

It would have been helpful, TO YOU ultimately, if you were to have posted 1) sample data from table1 & table2, 2) the query that you have been using and 3) the expected results.

Anything short of that, leaves the members who want to help you, GUESSING, WASTING time and DELAYING YOUR SOLUTION!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[moonman84]

i did post the information from the tables listed above...
the original tables are below:
table 1:
row name fault time
1. gas1 1 27/06/2011 15:40:35
2. gas1 1 27/06/2011 15:42:35

table 2:
row name fault time
1. gas1 0 27/06/2011 15:41:35
2. gas1 0 27/06/2011 15:43:35

im using the following SQL code:

SELECT DISTINCT Table1.Name, Table1.Fault, Table1.Timestamp, Table2.Name, Table2.Fault, Table2.Timestamp, DateDiff('s',[Table1].[Timestamp],[Table2].[Timestamp]) AS sec
FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name;

the results gives me 4 rows. rows 1 and 4 are correct, row 2 and 3 are not. results are as follows:

row name.table1 fault.table1 time.table1 name.table2 fault.table2 time.table2 sec
1. gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:41:35 60
2. gas1 1 27/06/2011 15:40:35 gas1 0 27/06/2011 15:43:35 180
3. gas1 1 27/06/2011 15:42:35 gas1 0 27/06/2011 15:41:35 -60
4. gas1 1 27/06/2011 15:42:35 gas1 0 27/06/2011 15:43:35 60

 

[PHV]

And what about my suggestion timestamped 6 Jul 11 8:50 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[moonman84]

getting syntax errors trying to work them out lol will get back to you.

thanks PHV

 

[PHV]

OOps, sorry for the typos:
SELECT A.Name, A.Fault, A.timestamp, B.Name, B.Fault, B.timestamp, DateDiff('s',A.timestamp,B.timestamp) AS sec
FROM (table1 A
INNER JOIN table2 B ON A.Name = B.Name)
INNER JOIN (
SELECT Name, MIN(timestamp) AS ts FROM table2 WHERE Name=A.Name AND timestamp>A.timestamp
GROUP BY Name) AS X ON B.Name=X.Name AND B.timestamp=X.ts

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LarrySteele]

moonman84,

PHV's working with you to help identify a resolution. I just wanted to help you understand why you're getting the results you got.

the original tables are below:
table 1:
gas1 1 27/06/2011 15:40:35
gas1 1 27/06/2011 15:42:35

table 2:
gas1 0 27/06/2011 15:41:35
gas1 0 27/06/2011 15:43:35

the results of the query puts table 1 and 2 in same row which i wanted but the time stamps are messed up... and i was expecting only 2 rows not 4....

You linked the two tables on the first column.

Go to the first record from table 1 and get the value of name - 'gas1', then find all records in table 2 with where name='gas1' - you get two records in your result set. Now, move to table 1, record 2. Once again, name = 'gas1', so find all records in table 2 where name = 'gas1' and as before, you get two records in your result set, giving you a total of four records.

What you need is the ability to uniquely identify a single row in table 2 given values from a given row in table 1 - and that's what PHV is trying to do for you.

Hopefully that helps explain why you got the results you got and that PHV will be able to help you find a solution. Don't forget to click the link "Thank PHV for this valuable post!" once you're query's all set.