Jumat, 20 Mei 2011

RE: [MS_AccessPros] How do I add to this SQL code to get expected results?

 

Kim-

Duane already answered you. You cannot do it with SQL alone. You need to us a
VBA function that you call from your query to assemble all the values into one
string. See:

http://tech.groups.yahoo.com/group/MS_Access_Professionals/message/90527

.. which leads you to:

http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-r
ecords_topic16.html

Download that code and follow the instructions about how to use the function.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Kim
Sent: Friday, May 20, 2011 4:34 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] How do I add to this SQL code to get expected results?

Sql query looks like this:

SELECT Table1.Reference AS Reference, First(Table1.Loading) &
IIf(Count(Table1.Loading)>1,"," & Last(Table1.Loading)) AS Loading
FROM Table1
GROUP BY Table1.Reference;

It's only returning 2 loading systems, because of first and last. How do I get
it to return more than 3 or more? How do I tweak this to make it happen?

Original table
REFERENCE LOADING
12345 NDB
12345 PULSE
12345 NICE
12345 DIAMOND

On one line right now it returns:
12345 NDB, PULSE

I want it to return this, on the same line:
12345 NDB, NICE, PULSE, DIAMOND

How do I tweak the query to make it happen??

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.


Find useful articles and helpful tips on living with Fibromyalgia. Visit the Fibromyalgia Zone today!

.

__,_._,___

Tidak ada komentar:

Posting Komentar