Counting grouped repeats within date range.

[freeflyer30339]

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.

[Stewart Ross]

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

SELECT X.Grp1, X.Grp2, X.trDate AS [Rec Date], Y.trDate AS [Other Date]
FROM DateGroup AS X INNER JOIN DateGroup AS Y ON (X.Grp2 = Y.Grp2) AND (X.Grp1 = Y.Grp1)
GROUP BY X.Grp1, X.Grp2, X.trDate, Y.trDate
HAVING (((X.trDate)<>[Y].[trDate]) AND ((Y.trDate) Between ([x].[trDate]-7) And ([x].[trDate]+7)));

Count query

SELECT DateGroupQuery.Grp1, DateGroupQuery.Grp2, DateGroupQuery.[Rec Date], Count(DateGroupQuery.[Rec Date]) AS [Range count]
FROM DateGroupQuery
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

Grp1    Grp2    trDate
A    A    01/01/2008
A    A    02/01/2008
A    A    03/01/2008
A    A    04/01/2008
A    A    05/01/2008
A    A    06/01/2008
A    A    07/01/2008
A    A    08/01/2008
A    A    09/01/2008
A    B    01/02/2008
A    B    02/02/2008
A    B    03/02/2008
A    B    04/02/2008
A    B    05/02/2008
A    B    10/02/2008
A    B    11/02/2008
A    B    01/03/2008
A    B    08/03/2008
A    B    09/03/2008

DateGroupQuery (extract)

Grp1    Grp2    Rec Date    Other Date
A    A    01/01/2008    02/01/2008
A    A    01/01/2008    03/01/2008
A    A    01/01/2008    04/01/2008
A    A    01/01/2008    05/01/2008
A    A    01/01/2008    06/01/2008
A    A    01/01/2008    07/01/2008
A    A    01/01/2008    08/01/2008
A    A    02/01/2008    01/01/2008
A    A    02/01/2008    03/01/2008
A    A    02/01/2008    04/01/2008
A    A    02/01/2008    05/01/2008
A    A    02/01/2008    06/01/2008
A    A    02/01/2008    07/01/2008
A    A    02/01/2008    08/01/2008
A    A    02/01/2008    09/01/2008
A    A    03/01/2008    01/01/2008
A    A    03/01/2008    02/01/2008
A    A    03/01/2008    04/01/2008

Count query

Grp1    Grp2    Rec Date    Range count
A    A    01/01/2008    7
A    A    02/01/2008    8
A    A    03/01/2008    8
A    A    04/01/2008    8
A    A    05/01/2008    8
A    A    06/01/2008    8
A    A    07/01/2008    8
A    A    08/01/2008    8
A    A    09/01/2008    7
A    B    01/02/2008    4
A    B    02/02/2008    4
A    B    03/02/2008    5
A    B    04/02/2008    6
A    B    05/02/2008    6
A    B    10/02/2008    4
A    B    11/02/2008    3
A    B    01/03/2008    1
A    B    08/03/2008    2
A    B    09/03/2008    1

[NeoPa]

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].

SELECT subQ.Grp1,
       subQ.Grp2,
       subQ.[Rec Date],
       Count(subQ.[Rec Date]) AS [Range Count]
 
FROM (SELECT DG1.Grp1,
             DG1.Grp2,
             DG1.trDate AS [Rec Date],
             DG2.trDate AS [Other Date]
 
      FROM DateGroup AS DG1 INNER JOIN DateGroup AS DG2
        ON ((DG1.Grp1=DG2.Grp1)
       AND  (DG1.Grp2=DG2.Grp2)
       AND  (DG1.trDate<>DG2.trDate)
       AND  (DG2.trDate Between DG1.trDate-7 And DG1.trDate+7))) AS subQ
 
GROUP BY subQ.Grp1,
         subQ.Grp2,
         subQ.[Rec Date]

(Please excuse the plagiarism ;->)

[Stewart Ross]

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

[NeoPa]

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).

[freeflyer30339]

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.

[Stewart Ross]

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