Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-19
8
Medium Priority
?
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 51

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 51

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 51

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 51

Expert Comment

by:Gustav Brock
ID: 40334153
You are welcome!

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 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