A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Select unique products per customer?



 
 
Thread Tools Display Modes
  #1  
Old March 19th, 2010, 07:44 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default Select unique products per customer?

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


  #2  
Old March 19th, 2010, 09:00 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Select unique products per customer?

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;

--
Build a little, test a little.


"Jim" wrote:

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


.

  #3  
Old March 19th, 2010, 10:36 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default Select unique products per customer?

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" wrote in message
...
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;

--
Build a little, test a little.


"Jim" wrote:

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


.



  #4  
Old March 20th, 2010, 12:40 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Select unique products per customer?

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.

--
Build a little, test a little.


"Jim" wrote:

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" wrote in message
...
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;

--
Build a little, test a little.


"Jim" wrote:

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


.



.

  #5  
Old March 20th, 2010, 01:39 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Select unique products per customer?

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 wrote:
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


  #6  
Old March 23rd, 2010, 07:35 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default Select unique products per customer?

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" wrote in message
...
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 wrote:
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


  #7  
Old March 23rd, 2010, 09:11 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Select unique products per customer?

That will require a bit of VBA code.

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.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 wrote:
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" wrote in message
...
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 wrote:
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


  #8  
Old March 23rd, 2010, 09:40 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default Select unique products per customer?

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

Jim

"John Spencer" wrote in message
...
That will require a bit of VBA code.

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.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 wrote:
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" wrote in message
...
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 wrote:
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.