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  1994  NeoPa 32,573
Recognized Expert Moderator MVP
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,573
Recognized Expert Moderator MVP
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,573
Recognized Expert Moderator MVP
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,573
Recognized Expert Moderator MVP
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
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 KA}\N{DEVANAGARI VOWEL SIGN AA}\N{DEVANAGARI LETTER KA}"
)
|
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 not use garbage collection (as Java does).
So if the script runs a loop:
for i in range(100):
f = Obj(i)
|
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 client deletes a refernce to
it
|
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. In its member function, an object manages to destroy last
reference to itself and thus destructs before the end of the member
function.
|
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 employee knows anything about
Access. I've searched Google Groups, and that has been a lot of help, but
there are some questions that I just can't find the answer to. I'll try to
take it easy on the group after this question.
I have one more...
| | |
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?
#define COUNT(num, count) \
do { \
unsigned char *safe ## num ## count = (unsigned
char *)(&(num)); \
|
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 with uploaded MS WORD .doc files. Using code
like that below:
strLine = sr.ReadLine
While Not IsNothing(strLine) 'Not eof
If Trim(strLine) <> "" Then 'Not blank
|
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) { }
double &operator()(int i) { return myX; }
|
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, tabs, linefeeds, returns). It will print 3 pieces of information
before exit: the first word, the last word, and the number of words read, with one blank in between fields.
Input
lines of words
Output
|
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: 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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
