Solved

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

Posted on 2014-09-19
8
170 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 50

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 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

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 50

Expert Comment

by:Gustav Brock
ID: 40334153
You are welcome!

/gustav
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

710 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