Query to consolidate records

[Chas]

I have a table of tenants with data as follows:

Suite............Ten1Last .............Ten1First
101..............Smith...................Joe
101..............Smith...................Mary

I want to do this to the data with a query:

Suite.......Ten1Last..........Ten1First.......Ten2Last......Ten2 First
101.........Smith...............Joe.............. Smith...........Mary

You might say I want to un-normalize the data. Any help is appreciated.

 

[Galaxiom]

You probably need something like this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=221397

You will have extra complications with the first and last name but the principle should be similar.

 

[nanscombe]

It might make it easier if you concatenate the First and Last names in a query first,
i.e. Occupant: Trim(Nz([LastName]) & " " & Nz([FirstName])).

Nz() to account for Null fields, and Trim() to deal with leading and trailing spaces.

 

[Chas]

Thank you Galaxiom and Nigel for the reply. I will continue with the thread that Galaxiom suggests.

Charlie