Possible Crosstab query Address Labels?

[rgm949]

I have created a database for my company to track a various amount of things. I am now struggling to produce a Mailing list for sending out letters to our clients.

A simplified version of my clients table looks like this:

Clients Table
ClientID HouseholdID Prefix First Name Last Name Gender Relationship Address
1 1 Mr. John Doe Male Spouse 12 way ln
2 1 Mrs. Jane Doe Female Spouse 12 way ln
3 2 Dr. Jake blah Male Single 23 way ln
4 3 Mr. Rick one Male Spouse 34 way ln
5 3 Mrs. Justine two Female Spouse 34 way ln

I want my result to look like this:

HouseholdID Prefix1 First Name1 Last Name1 Prefix2 First Name2 Last Name2 Address
1 Mr. John Doe Mrs. Jane Doe 12 way ln
2 Dr. Jake Blah 23 way ln
3 Mr. Rick one Mrs. Justine Two 34 way ln

From here I am able to create another query with if statements that can finish the label to look like:

Mr & Mrs. John Doe Dr. Jake Blah Mr. Rick One & Mrs. Justine Two
12 way ln 23 way ln 34 way ln

I recently came across cross tab queries which i eventually used to track client meetings but for this I don't know how to handle the multiple columns and text values. Can someone point me in the right direction. Maybe a crosstab query isn’t the right way to do this. I am a beginner in SQL

Any help is greatly appreciated!

 

[arnelgp]

you need a function not a Crosstab, BUT if there are only 2 repeating HouseholdID, meaning presumed couples, and are

living on same address, and not necessarily in good terms with each other:


SELECT FirstOfClientID AS ClientID, HouseHoldID, [FirstOfPrefix] As Prefix1, [FirstOfFirst Name] As [First Name1], (SELECT TOP 1 Prefix FROM HouseHoldTable WHERE HouseHoldTable.HouseHoldID = T1.HouseHoldID And HouseHoldTable.ClientID>T1.FirstOfClientID) AS Prefix2, (SELECT TOP 1 [First Name] FROM HouseHoldTable WHERE HouseHoldTable.HouseHoldID = T1.HouseHoldID And HouseHoldTable.ClientID>T1.FirstOfClientID) AS [First Name2], FirstOfAddress As Address FROM (SELECT First(HouseHoldTable.ClientID) AS FirstOfClientID, HouseHoldTable.HouseHoldID, First(HouseHoldTable.Prefix) AS FirstOfPrefix, First(HouseHoldTable.[First Name]) AS [FirstOfFirst Name], First(HouseHoldTable.[Last Name]) AS [FirstOfLast Name], First(HouseHoldTable.Address) AS FirstOfAddress
FROM HouseHoldTable
GROUP BY HouseHoldTable.HouseHoldID) AS T1




***
Note: replace HouseHoldTable with the correct name of your table

 

[rgm949]

Thank you! I had to change the code a little to get the last names to show up but this ended up getting exactly what I needed.

SELECT T1.FirstOfClientID AS ClientID, T1.HouseHoldID, T1.[FirstOfPrefix] AS Prefix1, T1.[FirstOfFirstName] AS [First Name1], (SELECT TOP 1 Prefix FROM ClientTbl WHERE ClientTbl.HouseHoldID = T1.HouseHoldID And ClientTbl.ClientID>T1.FirstOfClientID) AS Prefix2, (SELECT TOP 1 [FirstName] FROM ClientTbl WHERE ClientTbl.HouseHoldID = T1.HouseHoldID And ClientTbl.ClientID>T1.FirstOfClientID) AS [First Name2], T1.FirstOfAddress AS Address, T1.[FirstOfLastName] AS [Last Name1], (SELECT TOP 1 [LastName] FROM ClientTbl WHERE ClientTbl.HouseHoldID = T1.HouseHoldID And ClientTbl.ClientID>T1.FirstOfClientID) AS [Last Name2]
FROM (SELECT First(ClientTbl.ClientID) AS FirstOfClientID, ClientTbl.HouseHoldID, First(ClientTbl.Prefix) AS FirstOfPrefix, First(ClientTbl.[FirstName]) AS FirstOfFirstName, First(ClientTbl.[LastName]) AS FirstOfLastName, First(ClientTbl.Address) AS FirstOfAddress FROM ClientTbl GROUP BY ClientTbl.HouseHoldID) AS T1;