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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|