querry of 2 different times -- I need the 6 minutes before and aft

  • Thread starter Thread starter Ryan Arete
  • Start date Start date
R

Ryan Arete

I have 2 tables that have coorsponding data that are gathered in time. In
table A the time is (EX. 12:34:17pm) in table B the time is calculated every
6 minutes for the required data (EX 12:30:00pm next 12:36:00pm)

The Question: how do I querry the two tables so that when I ask for a
certain time in table A I aslo get the data in table B with the closest timed
data just prior and after the requested data and time from table A
 
Try this in the Field row in query design:
DateDiff("n", #0:00:00#, TimeValue(Nz([MyDate], #0:00:00#))) \ 6
substituting your field name for MyDate.

That should yield a number indicating which 6-minute bracket of the day the
time belongs to. Betwen midnight and 0:05:59 will be zero; next 6 minutes
will be 1, and so on.

If you do that in two queries, you can then match them on this field.

Explanation of the expression:
- TimeValue() strips out any date component, leaving just the time.
- TimeValue() spits the dummy with Nulls, hence Nz().
- DateDiff() with "n" gives the number of minutes between 2 date/times.
- backslash is the integer divisor, discarding any fractional remainder.
 
Do you live in a country with a different time format?
Or a different list separator?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ryan Arete said:
Allen,

Thank you for the help. I tried the information you sent me but I get a
syntax error at the following location (between the "*": DateDiff("n",
#0:00***:00#***, TimeValue(Nz([Time UTC], #0:00:00#))) \ 6


Thanks so much

Allen Browne said:
Try this in the Field row in query design:
DateDiff("n", #0:00:00#, TimeValue(Nz([MyDate], #0:00:00#))) \ 6
substituting your field name for MyDate.

That should yield a number indicating which 6-minute bracket of the day
the
time belongs to. Betwen midnight and 0:05:59 will be zero; next 6 minutes
will be 1, and so on.

If you do that in two queries, you can then match them on this field.

Explanation of the expression:
- TimeValue() strips out any date component, leaving just the time.
- TimeValue() spits the dummy with Nulls, hence Nz().
- DateDiff() with "n" gives the number of minutes between 2 date/times.
- backslash is the integer divisor, discarding any fractional remainder.

Ryan Arete said:
I have 2 tables that have coorsponding data that are gathered in time.
In
table A the time is (EX. 12:34:17pm) in table B the time is calculated
every
6 minutes for the required data (EX 12:30:00pm next 12:36:00pm)

The Question: how do I querry the two tables so that when I ask for a
certain time in table A I aslo get the data in table B with the closest
timed
data just prior and after the requested data and time from table A
Click to expand...
Click to expand...
Click to expand...
 
Here are examples of the fields I have in 2 different tables. I need to
match each of Table B's lines up with the nearest lines in time from table A.

I have two scientific intruments that collect data at two differnet rates.
I want to compare the data from both at a single point in time. ( 6 minutes
due to low data rate on one of the instruments)

I hope this helps and agian thanks for you time helping someone halfway
around the world. Virginia, USA

Table A ( 2 consecutive lines)
5/16/2007 2:21:02 PM
5/16/2007 2:27:02 PM

Table B ( 2 consecutive lines)
5/16/2007 2:21:13 PM
5/16/2007 2:21:58 PM
Allen Browne said:
Do you live in a country with a different time format?
Or a different list separator?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ryan Arete said:
Allen,

Thank you for the help. I tried the information you sent me but I get a
syntax error at the following location (between the "*": DateDiff("n",
#0:00***:00#***, TimeValue(Nz([Time UTC], #0:00:00#))) \ 6


Thanks so much

Allen Browne said:
Try this in the Field row in query design:
DateDiff("n", #0:00:00#, TimeValue(Nz([MyDate], #0:00:00#))) \ 6
substituting your field name for MyDate.

That should yield a number indicating which 6-minute bracket of the day
the
time belongs to. Betwen midnight and 0:05:59 will be zero; next 6 minutes
will be 1, and so on.

If you do that in two queries, you can then match them on this field.

Explanation of the expression:
- TimeValue() strips out any date component, leaving just the time.
- TimeValue() spits the dummy with Nulls, hence Nz().
- DateDiff() with "n" gives the number of minutes between 2 date/times.
- backslash is the integer divisor, discarding any fractional remainder.

I have 2 tables that have coorsponding data that are gathered in time.
In
table A the time is (EX. 12:34:17pm) in table B the time is calculated
every
6 minutes for the required data (EX 12:30:00pm next 12:36:00pm)

The Question: how do I querry the two tables so that when I ask for a
certain time in table A I aslo get the data in table B with the closest
timed
data just prior and after the requested data and time from table A
Click to expand...
Click to expand...
Click to expand...
 
From this example, you want to match:
2:21:02 from A = 2:21:13 from B
2:27:02 from A = 2:21:58 from B

I don't see an algorithm for determining which record matches which. There
is no way to determine which table holds the earlier record of the pair to
be matched, and it is not necessarily the nearest time.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ryan Arete said:
Here are examples of the fields I have in 2 different tables. I need to
match each of Table B's lines up with the nearest lines in time from table
A.

I have two scientific intruments that collect data at two differnet rates.
I want to compare the data from both at a single point in time. ( 6
minutes
due to low data rate on one of the instruments)

I hope this helps and agian thanks for you time helping someone halfway
around the world. Virginia, USA

Table A ( 2 consecutive lines)
5/16/2007 2:21:02 PM
5/16/2007 2:27:02 PM

Table B ( 2 consecutive lines)
5/16/2007 2:21:13 PM
5/16/2007 2:21:58 PM
Allen Browne said:
Do you live in a country with a different time format?
Or a different list separator?

Ryan Arete said:
Allen,

Thank you for the help. I tried the information you sent me but I get
a
syntax error at the following location (between the "*":
DateDiff("n",
#0:00***:00#***, TimeValue(Nz([Time UTC], #0:00:00#))) \ 6


Thanks so much

:

Try this in the Field row in query design:
DateDiff("n", #0:00:00#, TimeValue(Nz([MyDate], #0:00:00#))) \ 6
substituting your field name for MyDate.

That should yield a number indicating which 6-minute bracket of the
day
the
time belongs to. Betwen midnight and 0:05:59 will be zero; next 6
minutes
will be 1, and so on.

If you do that in two queries, you can then match them on this field.

Explanation of the expression:
- TimeValue() strips out any date component, leaving just the time.
- TimeValue() spits the dummy with Nulls, hence Nz().
- DateDiff() with "n" gives the number of minutes between 2
date/times.
- backslash is the integer divisor, discarding any fractional
remainder.

I have 2 tables that have coorsponding data that are gathered in
time.
In
table A the time is (EX. 12:34:17pm) in table B the time is
calculated
every
6 minutes for the required data (EX 12:30:00pm next 12:36:00pm)

The Question: how do I querry the two tables so that when I ask for
a
certain time in table A I aslo get the data in table B with the
closest
timed
data just prior and after the requested data and time from table A
Click to expand...
Click to expand...
Click to expand...
 
Back
Top