Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Combining multiple rows into one in a table

Author  Topic  akgyun Starting Member 2 Posts Posted - 2008-05-07 : 19:48:29 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!!