By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Counting grouped repeats within date range.

P: 26
I have been pulling my hair out for a week on this one! In a query using Ms Access 2003, I am trying to group two columns. The third column is the date of the transaction. I would like the query to tell me the number of repeats for each record that fall within a 7 day period of each records date. Hope this makes sense, I can sure use some help.

Thanks.
Aug 4 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
Hi. As it stands, you need two queries to achieve what you ask. If I understand you correctly, you need to count how many records within the current grouping are within 7 days of the current record's date. I do not know whether you mean 7 days overall, or 7 days before or after the current date. I have taken you to mean 7 days before or after, but only you will know if this is correct. If in fact it is just up to 7 days before the current date the BETWEEN criterion would change to BETWEEN (x.trDate - 7) AND x.trDate

In the example below there are two queries. The first uses a self join to find all records within seven days of the current record date, and the second counts the results. Please note that the table and field names will need to be replaced in any testing you do with your actuals.

DateGroupQuery
Expand|Select|Wrap|Line Numbers
  1. SELECT X.Grp1, X.Grp2, X.trDate AS [Rec Date], Y.trDate AS [Other Date]
  2. FROM DateGroup AS X INNER JOIN DateGroup AS Y ON (X.Grp2 = Y.Grp2) AND (X.Grp1 = Y.Grp1)
  3. GROUP BY X.Grp1, X.Grp2, X.trDate, Y.trDate
  4. HAVING (((X.trDate)<>[Y].[trDate]) AND ((Y.trDate) Between ([x].[trDate]-7) And ([x].[trDate]+7)));
Count query
Expand|Select|Wrap|Line Numbers
  1. SELECT DateGroupQuery.Grp1, DateGroupQuery.Grp2, DateGroupQuery.[Rec Date], Count(DateGroupQuery.[Rec Date]) AS [Range count]
  2. FROM DateGroupQuery
  3. GROUP BY DateGroupQuery.Grp1, DateGroupQuery.Grp2, DateGroupQuery.[Rec Date];
The results of this query are appended below, on the test data shown.

-Stewart

Test Data
Expand|Select|Wrap|Line Numbers
  1. Grp1    Grp2    trDate
  2. A    A    01/01/2008
  3. A    A    02/01/2008
  4. A    A    03/01/2008
  5. A    A    04/01/2008
  6. A    A    05/01/2008
  7. A    A    06/01/2008
  8. A    A    07/01/2008
  9. A    A    08/01/2008
  10. A    A    09/01/2008
  11. A    B    01/02/2008
  12. A    B    02/02/2008
  13. A    B    03/02/2008
  14. A    B    04/02/2008
  15. A    B    05/02/2008
  16. A    B    10/02/2008
  17. A    B    11/02/2008
  18. A    B    01/03/2008
  19. A    B    08/03/2008
  20. A    B    09/03/2008
DateGroupQuery (extract)
Expand|Select|Wrap|Line Numbers
  1. Grp1    Grp2    Rec Date    Other Date
  2. A    A    01/01/2008    02/01/2008
  3. A    A    01/01/2008    03/01/2008
  4. A    A    01/01/2008    04/01/2008
  5. A    A    01/01/2008    05/01/2008
  6. A    A    01/01/2008    06/01/2008
  7. A    A    01/01/2008    07/01/2008
  8. A    A    01/01/2008    08/01/2008
  9. A    A    02/01/2008    01/01/2008
  10. A    A    02/01/2008    03/01/2008
  11. A    A    02/01/2008    04/01/2008
  12. A    A    02/01/2008    05/01/2008
  13. A    A    02/01/2008    06/01/2008
  14. A    A    02/01/2008    07/01/2008
  15. A    A    02/01/2008    08/01/2008
  16. A    A    02/01/2008    09/01/2008
  17. A    A    03/01/2008    01/01/2008
  18. A    A    03/01/2008    02/01/2008
  19. A    A    03/01/2008    04/01/2008
Count query
Expand|Select|Wrap|Line Numbers
  1. Grp1    Grp2    Rec Date    Range count
  2. A    A    01/01/2008    7
  3. A    A    02/01/2008    8
  4. A    A    03/01/2008    8
  5. A    A    04/01/2008    8
  6. A    A    05/01/2008    8
  7. A    A    06/01/2008    8
  8. A    A    07/01/2008    8
  9. A    A    08/01/2008    8
  10. A    A    09/01/2008    7
  11. A    B    01/02/2008    4
  12. A    B    02/02/2008    4
  13. A    B    03/02/2008    5
  14. A    B    04/02/2008    6
  15. A    B    05/02/2008    6
  16. A    B    10/02/2008    4
  17. A    B    11/02/2008    3
  18. A    B    01/03/2008    1
  19. A    B    08/03/2008    2
  20. A    B    09/03/2008    1
Aug 10 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
A perfectly good answer Stewart.

I would simply add that it is possible to combine the two queries into one larger query using a subquery.

This is not necessarily a desirable approach, but there are situations where it is necessary.

I should make clear that this is principally Stewart's SQL and I just made a few changes. The subquery [subQ] ([DateGroupQuery] in Stewart's post) has been changed not to GROUP at all (HAVING clause replaced by code in ON sub-clause), and the query [DateGroupQuery] has been incorporated into the single SQL code as [subQ].
Expand|Select|Wrap|Line Numbers
  1. SELECT subQ.Grp1,
  2.        subQ.Grp2,
  3.        subQ.[Rec Date],
  4.        Count(subQ.[Rec Date]) AS [Range Count]
  5.  
  6. FROM (SELECT DG1.Grp1,
  7.              DG1.Grp2,
  8.              DG1.trDate AS [Rec Date],
  9.              DG2.trDate AS [Other Date]
  10.  
  11.       FROM DateGroup AS DG1 INNER JOIN DateGroup AS DG2
  12.         ON ((DG1.Grp1=DG2.Grp1)
  13.        AND  (DG1.Grp2=DG2.Grp2)
  14.        AND  (DG1.trDate<>DG2.trDate)
  15.        AND  (DG2.trDate Between DG1.trDate-7 And DG1.trDate+7))) AS subQ
  16.  
  17. GROUP BY subQ.Grp1,
  18.          subQ.Grp2,
  19.          subQ.[Rec Date]
(Please excuse the plagiarism ;->)
Aug 10 '08 #3

Expert Mod 2.5K+
P: 2,545
Lovely subquery solution, NeoPa. Excellent formatting of your SQL too, as always - how do you do the lining up??

I remain a tad wary of subqueries as I find that the speed of execution of many equivalent queries much poorer than that achieved by a cascaded solution. To freeflyer I would simply suggest trying it out to see what happens - it is certainly neater and conceptually simpler than the cascaded two-query version.

Cheers

Stewart
Aug 11 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
Lovely subquery solution, NeoPa. Excellent formatting of your SQL too, as always - how do you do the lining up??
...
Thank you. Coming from you that's an accolade indeed :)

I use TextPad - a text based editor, configured to use a non-proportional font. Critically, I also use spaces. Tab characters are only reliable when the tab columns defined for the output display exactly match those where you're typing it in.
...
I remain a tad wary of subqueries as I find that the speed of execution of many equivalent queries much poorer than that achieved by a cascaded solution. To freeflyer I would simply suggest trying it out to see what happens - it is certainly neater and conceptually simpler than the cascaded two-query version.
...
No argument there Stewart. Access suffers particularly from poor optimisation where subqueries or UNION queries (and probably various others) are concerned. I repeat this was simply an option, not a recommendation. That's for the OP to decide on.

QueryDefs (saved Access Queries) typically store within them the optimisation info that has previously been determined for them. This is almost always helpful for determining how to optimise a query based on them (for the engine I mean. This is not a manual process in Access).
Aug 11 '08 #5

P: 26
Thank you very much for your time and assistance, the query worked perfectly. I apologize for the duplicate posting.

Would it be too much to ask for a little more help with this?

I would like to add one more field [location] in the results as to where the product was purchased. The location field is in the same table as the rest of the data. Just not sure how to incorporate the field into the query as to not skew the data.

Thanks again for all your help.
Aug 11 '08 #6

Expert Mod 2.5K+
P: 2,545
Hi. Whilst there is no problem at all with adding a location field to the main and subquery (or both of the cascade versions) you will inevitably change the totals, as you are introducing a further grouping.

Just add the location field in the SELECT and GROUP BY clauses of each query/subquery as necessary. You need to make sure you add the location field in both (i.e. that you select the field and that you group by it) for this to work.

-Stewart
Aug 11 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.