Counting re-occuring items within 7 days for each record.

[freeflyer30339]

I am trying to create code and or a query in Ms Access 03 to calculate the number of times a customer has purchased the same product within 7 days.

Customer   Product_no	Pur_date   Repeats
A           21-22-23    6/1/2008      2
A           49-72-02    6/2/2008      0
B           17-24-04    6/1/2008      3
B           17-24-04    6/5/2008      2
A           21-22-23    6/5/2008      0
C           67-42-20    6/6/2008      0
B           17-24-04    6/7/2008      0

I am trying to produce the Repeats column. But am not able to group the customers and the product number to perform the totals. Any suggestions would greatly be appreciated.

Thanks

[NeoPa]

What data are you starting with and in what format?

[freeflyer30339]

Thanks for replying, the data is imported from an Excel spreadsheet which was from another program that exported the information. The data is located in an Access table and not linked.

Thanks again for your help.

[NeoPa]

You don't seem to be getting what I'm asking. I need some sample input data (I figured if you were trying to create a column which is shown in the example you give, then the data shown is surely not what you are starting with).

Table MetaData wouldn't hurt either - you see your question makes little sense to me at the moment. The concept seems simple enough, but your data doesn't tie in with it too well (not that I can see anyway).

Please post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.

Table Name=[[U]tblStudent[/U]]
[I]Field; Type; IndexInfo[/I]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[freeflyer30339]

This is all new to me and I apologize for the confusion. The table that holds the data was created from a query the extracted just the fields I needed while grouping and sorting the data by the customer and product_no fields. You are correct that I am trying to produce the Repeats column. The fields are pretty basic. I am not sure how to get the MetaData you are referring to, but here are the basics about the data.

Table Name = [[U]tblGroupedRec[/U]]
[I]Field; Type; IndexInfo[/I]
Customer, text
Product_no, text
Pur_date, Date/Time

The sample below is just for one product, I hope this help.

Thank you!

customer  product_no  pur_date        
17C       33-11-01A   12/29/2007        
17C       33-11-01A   3/23/2008        
17C       33-11-01A   5/5/2008        
54C       33-11-01A   12/31/2007        
55C       33-11-01A   5/24/2008        
55C       33-11-01A   11/20/2007        
55C       33-11-01A   12/26/2007        
55C       33-11-01A   2/2/2008        
55C       33-11-01A   2/10/2008        
12E       33-11-01A   5/23/2008        
13E       33-11-01A   12/21/2007        
13E       33-11-01A   5/27/2008        
18E       33-11-01A   12/12/2007        
18E       33-11-01A   11/20/2007        
20E       33-11-01A   6/26/2008        
22E       33-11-01A   12/25/2007        
22E       33-11-01A   3/11/2008        
50E       33-11-01A   12/28/2007        
50E       33-11-01A   1/3/2008        
50E       33-11-01A   1/7/2008        
50E       33-11-01A   1/12/2008        
50E       33-11-01A   1/21/2008        
50E       33-11-01A   5/4/2008        
52E       33-11-01A   2/23/2008        
54E       33-11-01A   1/23/2008        
55E       33-11-01A   12/20/2007        
55E       33-11-01A   3/10/2008        
55E       33-11-01A   4/19/2008        
59E       33-11-01A   12/6/2007        
59E       33-11-01A   6/22/2008        
60E       33-11-01A   4/30/2008        
61N       33-11-01A   4/2/2008        
61N       33-11-01A   4/21/2008

[NeoPa]

How is a repeat determined? Is it a count of the records for a customer? Is it a count of how many complete duplicates (as in which records are the same across all three fields)?

Lastly, I notice you had a number of entries where repeat = 0. Is this literally a repeat count (number of items - 1) or should it be a simple count of the items (where all entries have a Repeat value of at least 1)?

[freeflyer30339]

It would be a total number of repeats for each customer and product within seven days. Customer 50E purchased a unit on 1-3-08, 1-7-08, and 1-12-08. So the repeat for 1-3-08 would be 1, on 1-7-08 it would be 1. The number of purchases for 1-12-08 would be 0 since no other purchases were made within 7 days. So each repeating record would be counted as 1, other wise has a value of 0.

[NeoPa]

If you could answer the questions directly that would help. I'm afraid your explanations leave me with even more unexplained items than there were before.

Remember - I don't know what you know unless you tell me.

[freeflyer30339]

Sorry for the delay in getting back to you. Had to unexpectedly go out of town. Thanks for continuing to assist me. I tried to breakdown your questions that you asked earlier.

How is a repeat determined?
When the Customer number and the product number are the same, and there is another purchase after it that falls within 7 days it is considered a repeat.


Is it a count of the records for a customer?
No, only when the customer number, product number match and the records that follow fall within 7 days.

Is it a count of how many complete duplicates (as in which records are the same across all three fields)? Only the first two fields have to match.

Lastly, I notice you had a number of entries where repeat = 0. Is this literally a repeat count (number of items - 1) or should it be a simple count of the items (where all entries have a Repeat value of at least 1)? Repeat would equal 0 unless another transaction falls with 7 days that has the same product number and customer number.

Hope this makes a little more sense.

[Stewart Ross]

HI freeflyer. Is this thread related to your other, now answered, thread on a very similar topic? (This is the link to the other thread) If it is, please help us by not posting duplicate threads on the same topic, which unnecessarily take up the time of the expert volunteers who staff this site.

MODERATOR

[freeflyer30339]

Sorry, you are correct. I was trying to reword the problem I was having. The responses on the other thread worked perfectly. Thank you so much!

[NeoPa]

(Continues screaming silently into the void :-S)