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

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

P: 26
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.
Expand|Select|Wrap|Line Numbers
  1. Customer   Product_no    Pur_date   Repeats
  2. A           21-22-23    6/1/2008      2
  3. A           49-72-02    6/2/2008      0
  4. B           17-24-04    6/1/2008      3
  5. B           17-24-04    6/5/2008      2
  6. A           21-22-23    6/5/2008      0
  7. C           67-42-20    6/6/2008      0
  8. 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
Jul 31 '08 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,276
What data are you starting with and in what format?
Aug 5 '08 #2

P: 26
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.
Aug 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,276
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.
Expand|Select|Wrap|Line Numbers
  1. Table Name=[tblStudent]
  2. Field; Type; IndexInfo
  3. StudentID; AutoNumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Aug 6 '08 #4

P: 26
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.
Expand|Select|Wrap|Line Numbers
  1. Table Name = [tblGroupedRec]
  2. Field; Type; IndexInfo
  3. Customer, text
  4. Product_no, text
  5. Pur_date, Date/Time
The sample below is just for one product, I hope this help.

Thank you!
Expand|Select|Wrap|Line Numbers
  1. customer  product_no  pur_date        
  2. 17C       33-11-01A   12/29/2007        
  3. 17C       33-11-01A   3/23/2008        
  4. 17C       33-11-01A   5/5/2008        
  5. 54C       33-11-01A   12/31/2007        
  6. 55C       33-11-01A   5/24/2008        
  7. 55C       33-11-01A   11/20/2007        
  8. 55C       33-11-01A   12/26/2007        
  9. 55C       33-11-01A   2/2/2008        
  10. 55C       33-11-01A   2/10/2008        
  11. 12E       33-11-01A   5/23/2008        
  12. 13E       33-11-01A   12/21/2007        
  13. 13E       33-11-01A   5/27/2008        
  14. 18E       33-11-01A   12/12/2007        
  15. 18E       33-11-01A   11/20/2007        
  16. 20E       33-11-01A   6/26/2008        
  17. 22E       33-11-01A   12/25/2007        
  18. 22E       33-11-01A   3/11/2008        
  19. 50E       33-11-01A   12/28/2007        
  20. 50E       33-11-01A   1/3/2008        
  21. 50E       33-11-01A   1/7/2008        
  22. 50E       33-11-01A   1/12/2008        
  23. 50E       33-11-01A   1/21/2008        
  24. 50E       33-11-01A   5/4/2008        
  25. 52E       33-11-01A   2/23/2008        
  26. 54E       33-11-01A   1/23/2008        
  27. 55E       33-11-01A   12/20/2007        
  28. 55E       33-11-01A   3/10/2008        
  29. 55E       33-11-01A   4/19/2008        
  30. 59E       33-11-01A   12/6/2007        
  31. 59E       33-11-01A   6/22/2008        
  32. 60E       33-11-01A   4/30/2008        
  33. 61N       33-11-01A   4/2/2008        
  34. 61N       33-11-01A   4/21/2008
Aug 6 '08 #5

NeoPa
Expert Mod 15k+
P: 31,276
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)?
Aug 6 '08 #6

P: 26
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.
Aug 6 '08 #7

NeoPa
Expert Mod 15k+
P: 31,276
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.
Aug 6 '08 #8

P: 26
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.
Aug 11 '08 #9

Expert Mod 2.5K+
P: 2,545
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
Aug 11 '08 #10

P: 26
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!
Aug 11 '08 #11

NeoPa
Expert Mod 15k+
P: 31,276
(Continues screaming silently into the void :-S)
Aug 11 '08 #12

Post your reply

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