count totals from 2 different queries using dates from first query as parameters
access 2010
I have 2 queries.
In this first query called : "Search_Terms_Hourly"
QueryDate NextTime Gap
9/18/2014 8:23 9/18/2014 8:41 18
9/18/2014 8:53 9/18/2014 9:10 17
I need to take the fields
QueryDate (as a "start' DateTime) and NextTime( "End" DateTime)
Use those 2 fields a parameters in the second query. using "ActualQuerytime" as the lookup.
I need a result set containing how many records were between the start and end time for each record in the first query.
Example: How many records in the second query were between:
Query would look like this:
QueryDate NextTime RecordsInTimePeriodGap
9/18/2014 8:23 9/18/2014 8:41 7
9/18/2014 8:53 9/18/2014 9:10 19
Second Query Result Set
Mgr_Name ActualQueryTime SearchTerm
No Name 9/18/14 8:21 AM 1NB42
No Name 9/18/14 8:21 AM 135715
No Name 9/18/14 8:21 AM 135715
No Name 9/18/14 8:22 AM 2DV27
No Name 9/18/14 8:23 AM 1NB38
No Name 9/18/14 8:27 AM "*20448*"
No Name 9/18/14 8:27 AM "*20448*"
No Name 9/18/14 8:39 AM 539 3M
No Name 9/18/14 8:41 AM 610 3M
No Name 9/18/14 8:41 AM 610 3M TAPE
No Name 9/18/14 8:41 AM 2517 3M
No Name 9/18/14 8:43 AM 2519 3M
No Name 9/18/14 8:44 AM 4365997
No Name 9/18/14 8:44 AM "*43659*"
No Name 9/18/14 8:44 AM 4703138
No Name 9/18/14 8:44 AM 78801791738
No Name 9/18/14 8:45 AM 00 021200 03416 9
No Name 9/18/14 8:45 AM 00 021200 48596 1
No Name 9/18/14 8:45 AM 00 021200 61261 9
No Name 9/18/14 8:45 AM 00 051115 25355 3
No Name 9/18/14 8:45 AM 00 051115 31406 3
No Name 9/18/14 8:45 AM 1200 04310
No Name 9/18/14 8:46 AM 3M 4310
No Name 9/18/14 8:47 AM 411DL 105294 013N
No Name 9/18/14 8:47 AM 411DL 105294 013N
No Name 9/18/14 8:47 AM 62 4946 4950 2
No Name 9/18/14 8:47 AM 62 4946 4950 2
No Name 9/18/14 8:47 AM R3287 3M
No Name 9/18/14 8:47 AM R3287 3M
No Name 9/18/14 8:47 AM 70 0016 0842 4
No Name 9/18/14 8:47 AM 70 0016 0842 4
No Name 9/18/14 8:47 AM 70 0064 0354 0
No Name 9/18/14 8:48 AM 70 0064 0354 0
No Name 9/18/14 8:48 AM 70 0067 1206 4
No Name 9/18/14 8:48 AM 70 0067 1206 4
No Name 9/18/14 8:48 AM 78 8068 4143 9
No Name 9/18/14 8:48 AM 78 8068 4144 7
No Name 9/18/14 8:49 AM 78 8069 3959 7
No Name 9/18/14 8:49 AM 78 8069 3959 7
No Name 9/18/14 8:49 AM 78 8069 3960 5
No Name 9/18/14 8:49 AM 78 8069 3960 5
No Name 9/18/14 8:49 AM 78 8069 3991 0
No Name 9/18/14 8:49 AM 78 8069 3991 0
No Name 9/18/14 8:49 AM 78 8070 1273 3
No Name 9/18/14 8:50 AM 78 8070 1431 7
No Name 9/18/14 8:50 AM 78 8070 1574 4
No Name 9/18/14 8:50 AM 78 8070 1575 1
No Name 9/18/14 8:51 AM 78 8076 4862 7
No Name 9/18/14 8:51 AM 78 8114 0828 1
No Name 9/18/14 8:52 AM 917K 3736451
No Name 9/18/14 8:52 AM 917K 3736451
No Name 9/18/14 8:52 AM 9738R
No Name 9/18/14 8:52 AM 9952 W
No Name 9/18/14 8:52 AM R3127B
No Name 9/18/14 8:53 AM R3187
No Name 9/18/14 8:53 AM R3187
No Name 9/18/14 8:53 AM R3287
No Name 9/18/14 8:53 AM R3287
No Name 9/18/14 8:53 AM R3287 1
No Name 9/18/14 8:53 AM R3287 1
No Name 9/18/14 8:53 AM R3287 25 50 25
No Name 9/18/14 8:53 AM R3287 25 50 25
No Name 9/18/14 8:53 AM R3287W
No Name 9/18/14 8:53 AM R3287W
No Name 9/18/14 8:53 AM 17610
No Name 9/18/14 8:58 AM 60YD LG 3IN WD
No Name 9/18/14 8:58 AM 60YD LG 3IN WD
No Name 9/18/14 9:04 AM 539 3M
No Name 9/18/14 9:06 AM HOOKIE REGALITE
No Name 9/18/14 9:06 AM HOOKIE REGALITE
No Name 9/18/14 9:10 AM 2519 3M
No Name 9/18/14 9:10 AM 4365997
No Name 9/18/14 9:16 AM 3M PACKING FINISHING
No Name 9/18/14 9:16 AM 3M PACKING FINISHING
Thanks...
fordraiders
I have 2 queries.
In this first query called : "Search_Terms_Hourly"
QueryDate NextTime Gap
9/18/2014 8:23 9/18/2014 8:41 18
9/18/2014 8:53 9/18/2014 9:10 17
I need to take the fields
QueryDate (as a "start' DateTime) and NextTime( "End" DateTime)
Use those 2 fields a parameters in the second query. using "ActualQuerytime" as the lookup.
I need a result set containing how many records were between the start and end time for each record in the first query.
Example: How many records in the second query were between:
Query would look like this:
QueryDate NextTime RecordsInTimePeriodGap
9/18/2014 8:23 9/18/2014 8:41 7
9/18/2014 8:53 9/18/2014 9:10 19
Second Query Result Set
Mgr_Name ActualQueryTime SearchTerm
No Name 9/18/14 8:21 AM 1NB42
No Name 9/18/14 8:21 AM 135715
No Name 9/18/14 8:21 AM 135715
No Name 9/18/14 8:22 AM 2DV27
No Name 9/18/14 8:23 AM 1NB38
No Name 9/18/14 8:27 AM "*20448*"
No Name 9/18/14 8:27 AM "*20448*"
No Name 9/18/14 8:39 AM 539 3M
No Name 9/18/14 8:41 AM 610 3M
No Name 9/18/14 8:41 AM 610 3M TAPE
No Name 9/18/14 8:41 AM 2517 3M
No Name 9/18/14 8:43 AM 2519 3M
No Name 9/18/14 8:44 AM 4365997
No Name 9/18/14 8:44 AM "*43659*"
No Name 9/18/14 8:44 AM 4703138
No Name 9/18/14 8:44 AM 78801791738
No Name 9/18/14 8:45 AM 00 021200 03416 9
No Name 9/18/14 8:45 AM 00 021200 48596 1
No Name 9/18/14 8:45 AM 00 021200 61261 9
No Name 9/18/14 8:45 AM 00 051115 25355 3
No Name 9/18/14 8:45 AM 00 051115 31406 3
No Name 9/18/14 8:45 AM 1200 04310
No Name 9/18/14 8:46 AM 3M 4310
No Name 9/18/14 8:47 AM 411DL 105294 013N
No Name 9/18/14 8:47 AM 411DL 105294 013N
No Name 9/18/14 8:47 AM 62 4946 4950 2
No Name 9/18/14 8:47 AM 62 4946 4950 2
No Name 9/18/14 8:47 AM R3287 3M
No Name 9/18/14 8:47 AM R3287 3M
No Name 9/18/14 8:47 AM 70 0016 0842 4
No Name 9/18/14 8:47 AM 70 0016 0842 4
No Name 9/18/14 8:47 AM 70 0064 0354 0
No Name 9/18/14 8:48 AM 70 0064 0354 0
No Name 9/18/14 8:48 AM 70 0067 1206 4
No Name 9/18/14 8:48 AM 70 0067 1206 4
No Name 9/18/14 8:48 AM 78 8068 4143 9
No Name 9/18/14 8:48 AM 78 8068 4144 7
No Name 9/18/14 8:49 AM 78 8069 3959 7
No Name 9/18/14 8:49 AM 78 8069 3959 7
No Name 9/18/14 8:49 AM 78 8069 3960 5
No Name 9/18/14 8:49 AM 78 8069 3960 5
No Name 9/18/14 8:49 AM 78 8069 3991 0
No Name 9/18/14 8:49 AM 78 8069 3991 0
No Name 9/18/14 8:49 AM 78 8070 1273 3
No Name 9/18/14 8:50 AM 78 8070 1431 7
No Name 9/18/14 8:50 AM 78 8070 1574 4
No Name 9/18/14 8:50 AM 78 8070 1575 1
No Name 9/18/14 8:51 AM 78 8076 4862 7
No Name 9/18/14 8:51 AM 78 8114 0828 1
No Name 9/18/14 8:52 AM 917K 3736451
No Name 9/18/14 8:52 AM 917K 3736451
No Name 9/18/14 8:52 AM 9738R
No Name 9/18/14 8:52 AM 9952 W
No Name 9/18/14 8:52 AM R3127B
No Name 9/18/14 8:53 AM R3187
No Name 9/18/14 8:53 AM R3187
No Name 9/18/14 8:53 AM R3287
No Name 9/18/14 8:53 AM R3287
No Name 9/18/14 8:53 AM R3287 1
No Name 9/18/14 8:53 AM R3287 1
No Name 9/18/14 8:53 AM R3287 25 50 25
No Name 9/18/14 8:53 AM R3287 25 50 25
No Name 9/18/14 8:53 AM R3287W
No Name 9/18/14 8:53 AM R3287W
No Name 9/18/14 8:53 AM 17610
No Name 9/18/14 8:58 AM 60YD LG 3IN WD
No Name 9/18/14 8:58 AM 60YD LG 3IN WD
No Name 9/18/14 9:04 AM 539 3M
No Name 9/18/14 9:06 AM HOOKIE REGALITE
No Name 9/18/14 9:06 AM HOOKIE REGALITE
No Name 9/18/14 9:10 AM 2519 3M
No Name 9/18/14 9:10 AM 4365997
No Name 9/18/14 9:16 AM 3M PACKING FINISHING
No Name 9/18/14 9:16 AM 3M PACKING FINISHING
Thanks...
fordraiders
ASKER
gustav,
Error the field "Querydate" refers to one or more tables.
Error the field "Querydate" refers to one or more tables.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gustav,
SELECT Count(Search_Terms_Hourly.
FROM Search_Terms_Hourly, Searched_And_Posted_Single
WHERE (((Search_Terms_Hourly.Act
GROUP BY Searched_And_Posted_Single
ok this is working but..its being literal about the seconds. but thats not your fault, the seconds did not carry over in the posting in the question.
SELECT Count(Search_Terms_Hourly.
FROM Search_Terms_Hourly, Searched_And_Posted_Single
WHERE (((Search_Terms_Hourly.Act
GROUP BY Searched_And_Posted_Single
ok this is working but..its being literal about the seconds. but thats not your fault, the seconds did not carry over in the posting in the question.
ASKER
gustav...nm , its doing what i asked...over thinking it.
Thanks very much !
Thanks very much !
ASKER
excellent!
Thanks very much !
Thanks very much !
If it is Search_Terms_Hourly.Actual
Int(Search_Terms_Hourly.Ac
or - if the interval don't have time parts - add time to the interval end:
Between [Searched_And_Posted_Singl
If it the interval, you can likewise do:
Between Int([Searched_And_Posted_S
/gustav
Int(Search_Terms_Hourly.Ac
or - if the interval don't have time parts - add time to the interval end:
Between [Searched_And_Posted_Singl
If it the interval, you can likewise do:
Between Int([Searched_And_Posted_S
/gustav
You are welcome!
/gustav
/gustav
Select
QueryDate,
Count(Mgr_Name) As RecordsInTimePeriodGap
From
Search_Terms_Hourly,
SecondQueryName
Where
ActualQueryTime Between QueryDate And NextTime
Group By
QueryDate;
/gustav