Solved

Access query:contantenate "page number" if part ID filed is duplicate

Posted on 2008-10-15
3
360 Views
Last Modified: 2012-05-05
Hi, Please give me the SQL  to concatenate the page# with a comma, if the part ID is a duplicate.  in the  examples below, I would like to see widget_001 only display 1 row and to concatenate pages "2" and "10"  as  " 2, 10"  

example of non concatenate:            
      A                     B
1      part_ID      page#
2      widget-001      2
3      widget-001      10
4      widget-002      6`
5      widget-003      50
           
           
desired results:            
      A                   B
1      part_ID      page#
2      widget-001      2, 10
4      widget-002      6`
5      widget-003      50


SELECT C.PART_ID, C.[PAGE #]

FROM C;

Open in new window

0
Comment
Question by:gringotani
  • 2
3 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Can't be done in Jet SQL.

You have to write code to do the concatenation.
0
 

Author Comment

by:gringotani
Comment Utility
I want to do it in Access. what is the SQL code?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 50 total points
Comment Utility
JET SQL = Access SQL.

It can't be done in SQL.

You have to create a function in VBA and use the function in your sql statement.
There is an example with generic code here:
http://www.eggheadcafe.com/software/aspnet/32316648/concatenate-values-from-m.aspx
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now