Duplicate records - eliminate from totals

[Doug]

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[KARL DEWEY]

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

 

[Michael J. Strickland]

One quick and dirty way to delete duplicates is:

1. Make an empty copy of the table (Copy, structure only).

2. For the new (empty) table, In Design View, set the field(s) which
comprise a duplicate (e.g. client name, date) as the primary key. Do
this by holding Ctl down and clicking on these two fields. Then click on
the "Primary Key" button on the top menu (or ribbon).

3. Copy the original table to the new (empty) table.

Since the primary key of a table cannot contain duplicates, the copy
operation will generate error messages. Answer Ok, Yes, Yes, Ok to these
4 messages.

The unique records will be copied to your new table and the duplicates
will be placed in a separate table call Paste Errors.

 

[Doug]

Mike, thanks for the info.
I would like to keep the duplicate records. They contain other specialized
details and are used in other reports. The Table has 10-15 individual fields,
i.e product, price, coupon, etc.
Is there an easy way to compare client and date, and if those are the same,
count the item only once. Ingoring any duplicates, but not deleting the
records.

Thanks

 

[Doug]

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[KARL DEWEY]

Create a select query with your fields substituting for my CustomerID and
SaleDate.
Open the query in design view, click on icon the looks like an 'M' on its
side - ∑ - and save the query.
Then create a select query with just CustomerID. Open the query in design
view, click on icon the looks like an 'M' on its side - ∑ - and save the
query. Click on the CustomerID field in the table and drag down to the
FIELD row of the grid. In the TOTALS row of the grid under the second
CustomerID change the GROUP BY to COUNT and save.
Run the second query.


--
Build a little, test a little.

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[Doug]

Karl, that worked. Thank you!
I have the second query which counts the purchases by client.
i.e. customer #1 purchased 7 items

I'd like to take that one step further, and ask if customer #1 purchased 3
items on their first visit and 4 items on their second visit, I would like to
return the number 2. The number of times they made a purchase. Using the date
to isolate their visits to our store. is that possible?

Create a select query with your fields substituting for my CustomerID and
SaleDate.
Open the query in design view, click on icon the looks like an 'M' on its
side - ∑ - and save the query.
Then create a select query with just CustomerID. Open the query in design
view, click on icon the looks like an 'M' on its side - ∑ - and save the
query. Click on the CustomerID field in the table and drag down to the
FIELD row of the grid. In the TOTALS row of the grid under the second
CustomerID change the GROUP BY to COUNT and save.
Run the second query.


--
Build a little, test a little.

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.


:

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[KARL DEWEY]

For second query create a select query with CustomerID and SaleDate. Open
the query in design view, click on icon the looks like an 'M' on its side - ∑
- and save the
query. In the TOTALS row of the grid under the SaleDate change the GROUP
BY to COUNT and save.
Run the second query.

--
Build a little, test a little.

Karl, that worked. Thank you!
I have the second query which counts the purchases by client.
i.e. customer #1 purchased 7 items

I'd like to take that one step further, and ask if customer #1 purchased 3
items on their first visit and 4 items on their second visit, I would like to
return the number 2. The number of times they made a purchase. Using the date
to isolate their visits to our store. is that possible?

Create a select query with your fields substituting for my CustomerID and
SaleDate.
Open the query in design view, click on icon the looks like an 'M' on its
side - ∑ - and save the query.
Then create a select query with just CustomerID. Open the query in design
view, click on icon the looks like an 'M' on its side - ∑ - and save the
query. Click on the CustomerID field in the table and drag down to the
FIELD row of the grid. In the TOTALS row of the grid under the second
CustomerID change the GROUP BY to COUNT and save.
Run the second query.


--
Build a little, test a little.

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

:

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.


:

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[Doug]

Its counting the dates, but not the unique dates. It is returning the same
number as in the purchases.

For second query create a select query with CustomerID and SaleDate. Open
the query in design view, click on icon the looks like an 'M' on its side - ∑
- and save the
query. In the TOTALS row of the grid under the SaleDate change the GROUP
BY to COUNT and save.
Run the second query.

--
Build a little, test a little.

Karl, that worked. Thank you!
I have the second query which counts the purchases by client.
i.e. customer #1 purchased 7 items

I'd like to take that one step further, and ask if customer #1 purchased 3
items on their first visit and 4 items on their second visit, I would like to
return the number 2. The number of times they made a purchase. Using the date
to isolate their visits to our store. is that possible?

Create a select query with your fields substituting for my CustomerID and
SaleDate.
Open the query in design view, click on icon the looks like an 'M' on its
side - ∑ - and save the query.
Then create a select query with just CustomerID. Open the query in design
view, click on icon the looks like an 'M' on its side - ∑ - and save the
query. Click on the CustomerID field in the table and drag down to the
FIELD row of the grid. In the TOTALS row of the grid under the second
CustomerID change the GROUP BY to COUNT and save.
Run the second query.


--
Build a little, test a little.


:

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

:

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.


:

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[KARL DEWEY]

If you re-read my post I said 'second query'. You still have to have the
first query to feed this 2nd second query.

--
Build a little, test a little.

Its counting the dates, but not the unique dates. It is returning the same
number as in the purchases.

For second query create a select query with CustomerID and SaleDate. Open
the query in design view, click on icon the looks like an 'M' on its side - ∑
- and save the
query. In the TOTALS row of the grid under the SaleDate change the GROUP
BY to COUNT and save.
Run the second query.

--
Build a little, test a little.

Karl, that worked. Thank you!
I have the second query which counts the purchases by client.
i.e. customer #1 purchased 7 items

I'd like to take that one step further, and ask if customer #1 purchased 3
items on their first visit and 4 items on their second visit, I would like to
return the number 2. The number of times they made a purchase. Using the date
to isolate their visits to our store. is that possible?

:

Create a select query with your fields substituting for my CustomerID and
SaleDate.
Open the query in design view, click on icon the looks like an 'M' on its
side - ∑ - and save the query.
Then create a select query with just CustomerID. Open the query in design
view, click on icon the looks like an 'M' on its side - ∑ - and save the
query. Click on the CustomerID field in the table and drag down to the
FIELD row of the grid. In the TOTALS row of the grid under the second
CustomerID change the GROUP BY to COUNT and save.
Run the second query.


--
Build a little, test a little.


:

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

:

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.


:

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks

 

[Paul Shapiro]

Mike, thanks for the info.
I would like to keep the duplicate records. They contain other specialized
details and are used in other reports. The Table has 10-15 individual
fields,
i.e product, price, coupon, etc.
Is there an easy way to compare client and date, and if those are the
same,
count the item only once. Ingoring any duplicates, but not deleting the
records.

Thanks

Others have suggested how a Group By query will solve the problem for now.
Additionally, you might want to review your table design. It sounds like you
have a single table which should be at least 2 related tables. One table
would contain the fields that you describe as duplicates, and the related
child table would contain the "other specialized details". Then the queries
become very simple, you eliminate the current redundancy and you enforce
better data quality control.

 

[Doug]

Karl, thank you very much! After reading and re-reading your instructions, I
found my mistake. You were correct, my 2nd query was pulling from the table
and not my 1st query. Thanks for taking the time to assit.

If you re-read my post I said 'second query'. You still have to have the
first query to feed this 2nd second query.

--
Build a little, test a little.

Its counting the dates, but not the unique dates. It is returning the same
number as in the purchases.

For second query create a select query with CustomerID and SaleDate. Open
the query in design view, click on icon the looks like an 'M' on its side - ∑
- and save the
query. In the TOTALS row of the grid under the SaleDate change the GROUP
BY to COUNT and save.
Run the second query.

--
Build a little, test a little.


:

Karl, that worked. Thank you!
I have the second query which counts the purchases by client.
i.e. customer #1 purchased 7 items

I'd like to take that one step further, and ask if customer #1 purchased 3
items on their first visit and 4 items on their second visit, I would like to
return the number 2. The number of times they made a purchase. Using the date
to isolate their visits to our store. is that possible?

:

Create a select query with your fields substituting for my CustomerID and
SaleDate.
Open the query in design view, click on icon the looks like an 'M' on its
side - ∑ - and save the query.
Then create a select query with just CustomerID. Open the query in design
view, click on icon the looks like an 'M' on its side - ∑ - and save the
query. Click on the CustomerID field in the table and drag down to the
FIELD row of the grid. In the TOTALS row of the grid under the second
CustomerID change the GROUP BY to COUNT and save.
Run the second query.


--
Build a little, test a little.


:

Karl, thanks, but I'm really new to access and don't follow exactly.
I've attempted to build a totals query, but I don't think its done
correctly. Sorry to ask, but could you go into more detail? thanks again.

:

Use a totals query with all fields set to group by. Follow this with another
totals query to count customer entries.
qryCustSales --
SELECT CustomerID, SaleDate
FROM YourTable
GROUP BY CustomerID, SaleDate;

SELECT CustomerID, Count([CustomerID]) AS CountOfCustomerID
FROM qryCustSales
GROUP BY CustomerID;

--
Build a little, test a little.


:

I am trying to count how many times a customer made a purchase.
If they bought 2 items, it appears as 2 records in my table.

How can I eliminate records were the client name and the date are both the
same?

I can find which ones are duplicates, from the duplicate query wizzard, but
I want to count the first record and ignore any duplicate records.

Thanks