Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

count totals from 2 different queries  using dates from first query as parameters

Posted on 2014-09-19
8
Medium Priority
?
179 Views
Last Modified: 2014-09-20
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
0
Comment
Question by:Fordraiders
  • 4
  • 4
8 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40333701
Try this:

Select
    QueryDate,
    Count(Mgr_Name) As RecordsInTimePeriodGap
From
    Search_Terms_Hourly,
    SecondQueryName
Where
    ActualQueryTime Between QueryDate And NextTime    
Group By
    QueryDate;

/gustav
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 40333971
gustav,
Error the field "Querydate" refers to one or more tables.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40334107
Not according to your sample data. But just prefix the table/query name:

Select
     Search_Terms_Hourly.QueryDate,
     Count(Mgr_Name) As RecordsInTimePeriodGap
 From
     Search_Terms_Hourly,
     SecondQueryName
 Where
     ActualQueryTime Between Search_Terms_Hourly.QueryDate And Search_Terms_Hourly.NextTime    
 Group By
     Search_Terms_Hourly.QueryDate;

/gustav
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:Fordraiders
ID: 40334132
gustav,
SELECT Count(Search_Terms_Hourly.Mgr_Name) AS RecordTimeGap, Searched_And_Posted_Single_Date_TimeGap_Variance.QueryDate, Searched_And_Posted_Single_Date_TimeGap_Variance.NextTime
FROM Search_Terms_Hourly, Searched_And_Posted_Single_Date_TimeGap_Variance
WHERE (((Search_Terms_Hourly.ActualQueryTime) Between [Searched_And_Posted_Single_Date_TimeGap_Variance].[QueryDate] And [Searched_And_Posted_Single_Date_TimeGap_Variance].[NextTime]))
GROUP BY Searched_And_Posted_Single_Date_TimeGap_Variance.QueryDate, Searched_And_Posted_Single_Date_TimeGap_Variance.NextTime;


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.
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 40334149
gustav...nm ,   its doing what i asked...over thinking it.


Thanks very much !
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 40334151
excellent!

Thanks very much !
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40334152
If it is Search_Terms_Hourly.ActualQueryTime having seconds, you can expand that to:

Int(Search_Terms_Hourly.ActualQueryTime)

or - if the interval don't have time parts - add time to the interval end:

Between [Searched_And_Posted_Single_Date_TimeGap_Variance].[QueryDate] And ([Searched_And_Posted_Single_Date_TimeGap_Variance].[NextTime] + #23:59:59#)

If it the interval, you can likewise do:

Between Int([Searched_And_Posted_Single_Date_TimeGap_Variance].[QueryDate]) And Int([Searched_And_Posted_Single_Date_TimeGap_Variance].[NextTime])

/gustav
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40334153
You are welcome!

/gustav
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question