Select unique products per customer?

[Jim]

Looking at the Northwind database as an example, I need to take all the
products from the Orders table and put them into one field in a table for
each customer so that I end up with each customer and the products they've
ordered in row with no duplicate products for each customer. Thanks for any
help/suggestions.



Jim

 

[KARL DEWEY]

Putting them in a table is the wrong thing to do as it will be out of date
the next time something is ordered.
Just use this query each time --
SELECT CustomerID, Product
FROM YourTable
GROUP BY CustomerID, Product;

 

[Jim]

I need the records in a table because it's for a customer lookup on a
website that's not attached to the original data source. When I run the
query you suggest, it gives me multiple entries for every product they've
ordered. So if a customer orders 10 different products, it lists the same
customer 10 times. I need to have a list of what products they bought with
each customer listed only once.
Thanks

Jim

 

[KARL DEWEY]

I need to have a list of what products they bought with each customer
listed only once.
You have to have the customer for each of their purchased product otherwise
you would not know which product goes with a customer.
You can produce a report and set the Hide Duplicates property to Yes for
that text box.

 

[John Spencer]

SELECT DISTINCT CustomerID, Product
FROM YourTable

Will give you a unique list of products for each customer with no repeats.

If you want something other than a list, post back and try to post a short
example of the starting data and the end result you desire.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim]

What I currently get when I run a query is:



Col A Col B

Customer1 Product 1

Customer1 Product 2

Customer1 Product 3



What I need is:



Col A Col B

Customer1 Product1, Product2, Product3

 

[John Spencer]

That will require a bit of VBA code.

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim]

I will check these out. They seem to be what I need. Thanks

Jim