Solved

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

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

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 49

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 49

Expert Comment

by:Gustav Brock
ID: 40334153
You are welcome!

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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 …

820 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