Solved

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

Posted on 2008-10-15
3
394 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
ID: 22719600
Can't be done in Jet SQL.

You have to write code to do the concatenation.
0
 

Author Comment

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

Accepted Solution

by:
peter57r earned 50 total points
ID: 22728486
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 Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Syntax for query to update table 2 39
SQL Syntax Grouping Sum question 7 35
SQL - Simple Pivot query 8 26
Convert string date and select older than 30 days from today 2 40
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 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