working out time difference

[moonman84]

I am stuck on a little problems in access...trying to work out time difference...



I am getting data from 3rd party software in to my access table.

Table is arranged as follows:

Name, Fault and Time stamp. The Name is enter from a lookup table and the Fault is a "1" or a "0" from 3rd party software and the time stamp is entered as default Now().

So I ran a query to filter the Name to a single type. which gave me a table with the data but i need to work out the time difference between "1" and "0"

I have tried making a query to filter the name and then fault "1" and then another query same as before but fault being "0" this time then a 3rd query to get the time stamps from both query and use a date diff command to work out the time difference... however this give me duplicated data...

I can not see a way around this... I hope i am clear in what I typed above any help with is well be helpful

Many thanks.

[June7]

So the data is paired records, probably a start and stop? Need unique identifier for each pair for the the two queries to join on. Since you are seeing 'duplicates' I gather the name is not unique to each pair. If this is the case, I expect the only solution would be VBA.

[moonman84]

The data is paired "start" "stop" however because we are getting different faults from different sources with data is not arranged in the table with "start" one row and "stop" next row. So in the main table we get the following:

Name Fault Time
f1 1 12.00
f2 1 12.00
f1 0 12.02
f3 1 12.03
f3 0 12.04
f2 0 12.05

Hence the query to sort by the Name 1st so we can then see the "start" "stop" for that fault. But you have given us somewhere to look in to now.

Thanks for your help June7