Combining multiple rows into one in a table

[akgyun]

Dear all SQL Experts!!
I am using ACCESS SQL query to prepare a simple report.

I have these tables that show how each person can have one to many PIN numbers.
Each PIN number assigned can be of 2 types – Personal or Business
[PIN_Mapping] table shows the mapping between a person and PIN number, and its assigned type.
Below are the table structures:


PERSON

Person_ID | FirstName | LastName
-------------------------------------------------
2 Ryan Kang

PIN

PIN_ID | PIN_NO
-----------------------
13 1329
21 2411

PIN_MAPPING

Person_ID | PIN_ID | IsPersonal
-------------------------------------------
2 13 Yes
2 21 No

This is the SQL query used for reporting:

SELECT [LastName] & " " & [FirstName] AS Name, IIf([PIN_MAPPING]![IsPersonal]=True,"",[PIN]![PIN_NO]) AS Business_PIN, IIf([PIN_MAPPING]![IsPersonal]=False,"",[PIN]![PIN_NO]) AS Personal_PIN

FROM [PIN] INNER JOIN ([PERSON] INNER JOIN [PIN_MAPPING] ON [PERSON].[Person_ID]=[PIN_MAPPING].[PersonID]) ON [PIN].[PIN_ID]=[PIN_MAPPING].[PIN_ID]

ORDER BY [LastName] & " " & [FirstName];

This is the result of query:

Name Personal PIN Business PIN
------------------------------------------------------------------
Ryan Kang 1329
Ryan Kang 2411


But This is not what I want!!! How can I make it look like

Name Personal PIN Business PIN
------------------------------------------------------------------
Ryan Kang 1329 2411

By combining the row rows into one

Any idea? It looks rather a straightforward job but I could not come up with SQL commands that can do this.
Any help from SQL experts would be so appreciated
Thanks!!

 

[boblarson]

Do each as a separate query and then use a UNION to pull them together.

 

[boblarson]

But, just a reminder - you'll have to add a bogus column for each query - 1 in the second column and one in the third.

 

[akgyun]

Reply:

What do you mean by Bogus Column?
I am sorry to be a pain, but could you give us a bit more details? as I have tried UNION in many different ways but still couldn't get it working.....

 

[dallr]

http://www.mvps.org/access/modules/mdl0008.htm

dallr