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


0
Jim
3/19/2010 6:44:32 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
595 Views

Similar Articles

[PageSpeed] 18

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
> 
> 
> .
> 
0
Utf
3/19/2010 8:00:01 PM
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" <KARLDEWEY@discussions.microsoft.com> wrote in message 
news:E6E76BEE-55FF-4645-B762-48F73695A9C8@microsoft.com...
> 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
>>
>>
>> .
>> 


0
Jim
3/19/2010 9:36:09 PM
>>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" <KARLDEWEY@discussions.microsoft.com> wrote in message 
> news:E6E76BEE-55FF-4645-B762-48F73695A9C8@microsoft.com...
> > 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
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/19/2010 11:40:01 PM
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
> 
> 
0
John
3/20/2010 12:39:43 PM
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" <spencer@chpdm.edu> wrote in message 
news:us0WrpCyKHA.5936@TK2MSFTNGP04.phx.gbl...
> 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
>> 

0
Jim
3/23/2010 6:35:48 PM
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 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" <spencer@chpdm.edu> wrote in message 
> news:us0WrpCyKHA.5936@TK2MSFTNGP04.phx.gbl...
>> 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
>>>
> 
0
John
3/23/2010 8:11:50 PM
I will check these out. They seem to be what I need. Thanks

Jim

"John Spencer" <spencer@chpdm.edu> wrote in message 
news:%23UOwTUsyKHA.928@TK2MSFTNGP05.phx.gbl...
> 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 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" <spencer@chpdm.edu> wrote in message 
>> news:us0WrpCyKHA.5936@TK2MSFTNGP04.phx.gbl...
>>> 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
>>>>
>> 


0
Jim
3/23/2010 8:40:15 PM
Reply:

Similar Artilces:

Creating separate tables for multi-select list box selections
I have read from some of the other posts that it is necessary to create a separate table for each of the selections made in a multi-select list box from a form. I don't understand this concept! I have a form with 4 multi-select list boxes and many other fields that populate a table with a simple query attached to it. How do I create this separate table for the selections from the list boxes and link it to the main table and query? I am not an Access programmer and am learning as I go along. Please explain step by step and as simply as possible. Thanks for any and all help in a...

customization
Hello all, I am created a new VBA form using Visual Basic Editor in Great Plains 8.0, how can i integrate this new form in great plains 8.0. also how i create a custom menu from where i can access this VBA form. Fahad You cannot add to the v8.0 menus with VBA, this requires the use of Dexterity. Your best alternative without using Dexterity is to add a button to an existing window to open your VBA user form. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmu...

how to remove all selected items from CListCtrl
Hi what is wrong with following code: POSITION pos = m_wndList.GetFirtsSelectedItem(); if (NULL == pos) return; int selected_item; while(pos) { selected_item = m_wndList.GetNextSelectedItem(pos); m_wndList.DeleteItem(selected_item); } no all selected items are removed:( how to solve this problem. Regards, Robert In article <#TOnnofpEHA.1712@tk2msftngp13.phx.gbl>, "RobertK" <sftech@interia.pl> wrote: > what is wrong with following code: > > POSITION pos = m_wndList.GetFirtsSelectedItem(); > > if (NULL == pos) > return; > &...

CListCtrl & multiple row selection
How can I enable/disable multiple rows selection from code? Turn the single selection property on or off in the resource editor Miki Peric wrote: > How can I enable/disable multiple rows selection from code? > > -- ___________________________________________ Van Gennep - Media Automation Consulting bv Burg. Stramanweg 105 1101 AA Amsterdam The Netherlands Phone: +31-20-697 6029 Fax: +31-20-697 2249 E-mail: mkools@vangennep.nl WWW: http://www.vangennep.nl WWW: http://www.vangennep.com ___________________________________________ I need it at run time. ...

How can you customize the left Menu?
Hi all, I would like to display only "Opportunity" in the "Sales" area. How can do that? Thanks in advance. Alan Alan, I know you can add items to the menu through the ISV config option. If you can't remove items you may have to develop a small application to just show the opportunities. Calvin Luttrell How e-commerce is done. http://blog.projectthunder.com http://www.projectthunder.com "Alan" <Alan@discussions.microsoft.com> wrote in message news:77D317F7-3065-47F3-9031-DF83A64EA9AA@microsoft.com... > Hi all, > > I would like to dis...

No selection for country/region?
When I attempt to set up my dialing location (e.g. Control Panel > Phone and modem options) and the "Location Information" window appears, I have no selections to choose from in the country/regions pull down menu and therefore cannot set up my dialing location properly. How do I get the countries and regions to appear? I apparently need to fill this in because Outlook "requires" it to be able to simply enter a phone number in a contact, even though I believe it only uses that information to display the phone number in the manner it thinks is best. Seems a lit...

Error 1004 using array in Sheet select.
If I select a sheet with its real name: Sheets("Sheet1").Select >>>>>- Works fine Sheets(Array("Sheet1", "Sheet2")).Select >>>>>- Works fine If I assign the sheet name(s) to a constant Public Const TSGSheet1 = "Sheet1" Public Const TSGSheet2 = "Sheet2" Sheets(TSGSheet1).Select >>>>>- Works fine Sheets(TSGSheet2).Select >>>>>- Works fine Sheets(Array(TSGSheet1, TSGSheet2)).Select >>>>>- fails with Error 1004 My macro's/V...

How to download & use old product key
I bought my version of Office 2007 in Nov. 2008. New laptop came today. I still have the old product key, but can't access my purchase history on the website. How can I download & install Office 2007 on this new computer with my old product key? I'm ONLY going to be using this new laptop from here on out. Did you keep all the downloaded files on the old laptop? Do you have the original e-mail with the download instructions? Have you tried contacting Digital River? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intac...

How do I make a "custom" shape?
I want to make a circle shape with a small "flat" section on one end of the circle. Kind of the way a car tire low on air pressure would look, again a circle with a "flat" section on one end. I tried to do this be exporting a circle to "Paint" and modifying it and then pasting it back into Visio but that did not work. I need to be able to draw this custom shape in Visio. -- Dan Use the drawing toolbar and create a circle, then use the line from the drawing toolbar to mark where you want the "flat". The line can extend beyond edges of the circl...

Export custom properties using the property labels I defined
How do I export custom properties using the property labels that I defined? The export function uses "Prop.R_#" ... how can I get it to pick up the Label that I created ... be it "Mouse Over" or whatever? Also .... how do I have it update an Excel file once I've exported it? Use Export to Database. The next window will show the cells that you have defined to export. Click a cell and choose modify. Where it says Name in the Field Details you can type whatever name you want. After you do it the first time it will remember your entry so you don't have to re...

Customer Names and Adresses in Microsoft Money 2005 Small Business
I have about 70 customers with names and adresses in my Microsoft Money 2005 Small Business program. How do I print out a list of the names and adresses of the customers? It should be easy but is not obvious. Good question. It does not seem to be possible in money 2000 for either. "Dennis_H" wrote: > I have about 70 customers with names and adresses in my Microsoft Money 2005 > Small Business program. How do I print out a list of the names and adresses > of the customers? It should be easy but is not obvious. Okay...I am going to answer my own post because I figured...

Problem with Customization
Hi guys, I created an application in visual.net (COM Component) and the same have all the requirements asked. The problem is when the changes are made, the POS screen no refresh, but if I press any button or execute some event of this window, I can see changes. Can you help me? How can I refresh Pos Screen using qsrules and visual.net? I can do that? Thanks, Joaquín ...

unable to sort in Select Names box
I just migrated from 98se/OL 2000 to XP proff/OL 2002. Now when I open a new email and click the "To..." button, and it opens the "Select Names" box, and I go to select a recipients email the fields won't let me sort like I used to. Plus there are additional entries with fax numbers, how do get it to sort and NOT display the fax number entries? Also, is there any way I can make this "Select Names" box bigger so I don't have to scroll right to see the if it is the correct email address if they have multiple email address's? Thanks Ted No...

Access product activation page
I have a trial Microsoft Office on my laptop. I have bought Microsoft Office Home and Student 2007 from PC World. The Instruction says to 1.Start Word 2. Locate the Product Key on the back of the Media Holder. 3. Enter the 25 digit Product Key to unlock the software. The missing stage in the process is : How do I access the Product Key activation screen with the box in which to enter the Product Key? I seem to recall that the trial provides the option to change the product key from Word Options > Resources, but as you have bought a full version on disc, it might be wiser ...

POS doesn't display customer phone #
Hi all. I'd like the customer's phone number to be displayed along with their address (in the Bill To area), but the POS doesn't do that. I've looked in File Config in Manager for an option. It's such an elementary piece of information that I don't think it should require a customized caption to do this. What gives? Help is greatly appreciated. Please Cc: me directly so I see your answers sooner. Thanks! -- ______________________________________________________ Larry Leveen OlyBikes Bikes, Parts, Repairs & GREAT Customer Service! 124 State Avenue NE Olymp...

Custom Receipt
Has anyone managed to create a receipt that displays instead of the price of the item with the discount amount below, the price of the item - the discount? i.e. the discounted price. Thanks Rob I found it. Receipt Properties - Show Line Discount You know it really helps when one looks with there eyes open. Rob "Rob" <rob@retail-pos.nospam.com> wrote in message news:%23oiEHcWbFHA.3488@tk2msftngp13.phx.gbl... > Has anyone managed to create a receipt that displays instead of the price > of the item with the discount amount below, the price of the item - the >...

Customize Outlook Today
I had some one ask me to fix their "Customize Outlook Today" as it had stopped working. In Outlook 2000 it is the button near the top right of the page you get by pressing the Outlook Today Icon in the Outlook shortcuts toolbar. After trying for a while, I then tried to see if it worked on My PC... It didn't, and nor did it work on ANY of the half dozen I've tried since. I know it DID work at some time in the past, but some Windows update or IE patch must have broken it. I get the same result with Outlook 2000, SR-1 and SP-3 and both on Windows 98 and Windows XP. An...

Customizing the UoM Form!!
Hi, Is it possible to add a custom field to the Uom Form. I couldn't find the Uom form in the System Customization form. I checked the documentation but did not find any positive information in this regard. Just want to confirm that it can't be done. pls reply asap rojer Rojer, anything can be done ;) You can export the schema, edit the form definition in the schema manually, and re-import the schema. Not exactly supported though :) Mike McGuinness Lead Developer GaleForce Solutions Inc. "Rojer" <seanconnary@rediffmail.com> wrote in message news:%23CB...

Selecting the Sales Process
I have set up two sales process, one for inrfastructure, one for development. When I create an opportunity how do I determine which sales process the opportunity will follow. Right now it defaults to the first one on the list. Hi Todd, that is correct - the workflow will kick-off the first sales process that matches the criteria. So if "When opportunity is created" is your only condition, then the first one that matches it will be triggered. The solution could be to add an exit criteria at the beginning of the sales processes to check for more relevant conditions.. Hope this h...

Finding Windows Product Key in MFC
Hello, Currently I am writing an application that requires querying Windows to see what the Windows product key is (that is the 25 character alpha numeric product key, NOT the product id). Currently, I have done research at many of the major development sites (CodeProject,CodeGuru) and googled it without any luck. Does anyone know how to retrieve the Windows product key via MFC/WIN32? Sincerely, James Simpson President Straightway Technologies Inc. I doubt the product key would be stored somewhere. I think the scope of the windows product key is limited to the windows installer.It c...

Need help creating a custom view in appointment view
Can someone help me design a custom view for re-occurring appointments (birthdays) that sorts by month (without the year). I am looking for a view that would list all the Jan events together, all the Feb events together, etc. (the year doesn't matter since the events occur every year). The only view I have been able to create is completely chronological Jan-Dec 2004, Jan-Dec 2005 etc. Thanks Outlook can't do that, because it doesn't separate the date out as a = separate field that you can filter on.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programm...

using office mac on 2nd computer product codes #2
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Hi-I have Microsoft Office 2008 for Mac (Home & Student Edition). It came with 3 product codes. I installed the 1st product code on a mac lap top and then installed the software on a 2nd lap top, which did not ask for product code. Now, I can't use the 1 computer if the other software is in use on the 2nd or vice versa. I tried to delete the software on 1st computer and reload hoping it would ask for new product code which it did not. How can i reload software and have it ask for different product code? ...

ListControl and moving selection
Hi, I'm trying to move selection in listcontrol using two buttons but i'm having some problems. To be precise, i dont want to move item in listcontrol but just only the highlight selection, like if you press cursor keys up or down. The problem is that now it only works once. Selection moves when i press "MoveUp" button but if i press button again, it won't move. Here's the button code: void ClistcontrolDlg::OnBnClickedMoveup() { int curSelection = m_StoreItems.GetSelectionMark(); m_StoreItems.SetItemState(curSelection, 0, LVIS_SELECTED); m_StoreItems.SetItemSt...

Select an entire row to copy after finding a value
I am looking for a way to look for a value in a column and after finding that column, selecting that whole row and copying it. Can anyone help If you mean in a macro activecell.entirerow.select If with an Excel shortcut Shift+SpaceBar --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm <steel_curtai@yahoo.com> wrote in message news:1133912738.647332.209000@g14g2000cwa.googlegroups.com... > I am looking for a w...

Print Quote for Customer 06-30-04
Hi The MS provided word template for Quotes is useless, as it lacks the product lines. I am struggeling to make a decent template with some header information followed by the product lines, total sums and then a closing section. Is there anybody out there willing to share a working template, or send me some pointers, to info about how this can be accomplished? Regards Ketil PS: Yes I know this can be purchased from some MS partners but it seems a bit overkill to have to purchase a .dot template. Ketil The Quote mailmerge template does not lack the product lines. It treats each p...