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
11 1962 NeoPa 32,556
Expert Mod 16PB
What data are you starting with and in what format?
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 32,556
Expert Mod 16PB
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=[tblStudent]
- Field; Type; IndexInfo
-
StudentID; AutoNumber; PK
-
Family; String; FK
-
Name; String
-
University; String; FK
-
Mark; Numeric
-
LastAttendance; Date/Time
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 = [tblGroupedRec]
- Field; Type; IndexInfo
-
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 32,556
Expert Mod 16PB
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)?
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 32,556
Expert Mod 16PB
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.
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.
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
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 32,556
Expert Mod 16PB
(Continues screaming silently into the void :-S)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Srinath Avadhanula |
last post by:
Hello,
I am wondering if there is a way of counting graphemes (or
glyphs) in python. For example, in the following string:
u'\u0915\u093e\u0915'
(
or equivalently,
u"\N{DEVANAGARI LETTER...
|
by: Elbert Lev |
last post by:
Please correct me if I'm wrong.
Python (as I understand) uses reference counting to determine when to
delete the object. As soon as the object goes out of the scope it is
deleted. Python does...
|
by: ash |
last post by:
hi
does anyone has any experience with flyweight pattern with refernce
counting
i want to share objects between multiple clients and want to delete
the object from shared pool when the last...
|
by: Kalle Rutanen |
last post by:
Hello
I implemented reference counting in my program, and found out many
problems associated with it. I wonder if the following problems can be
solved automatically rather manually ?
1. ...
|
by: ChadDiesel |
last post by:
I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other...
|
by: zets |
last post by:
I need a macro for counting the bits in the odd positions of a given
input (of any type, char, pointer, int, struct, whatever).
Is there any clever way I could not think of, to do it efficiently?
...
|
by: j |
last post by:
Hi,
I've been trying to do line/character counts on documents that are
being uploaded. As well as the "counting" I also have to remove
certain sections from the file.
So, firstly I was working...
|
by: aaronfude |
last post by:
Hi,
Please consider the following class (it's not really my class, but it's
a good example for my question):
class Vector {
int myN;
double *myX;
Vector(int n) : myN(n), myX(new double) { }...
|
by: Nhd |
last post by:
I have a question which involves reading from cin and counting the number of words read until the end of file(eof).
The question is as follows:
Words are delimited by white spaces (blanks,...
|
by: sololoquist |
last post by:
#define COUNT_UP
#include <stdio.h>
#define N 10
int main()
{
int i;
#ifdef COUNT_UP
for (i = 0; i < N; i++)
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |