Solved

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

Posted on 2014-09-19
8
154 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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
gustav,
Error the field "Querydate" refers to one or more tables.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:fordraiders
Comment Utility
gustav...nm ,   its doing what i asked...over thinking it.


Thanks very much !
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
excellent!

Thanks very much !
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now