Query Result Layout I have a database with customer information including contac

Question

Query Result Layout

I have a database with customer information including contacts, addresses etc. Some companies have a number of contacts, some have one. I want to export the data to use but I need it to show as 1 company and then the contacts as e.g. contact1, contact2,contact3 etc in seperate columns in the same row. When I run the query at the moment the data lists maybe 4 entries for a company (A1, A2, A3, A4) and the 4 different contacts vertically in the contact column (B1, B2, B3 and B4). Is it possible to set up a query that will return a unique company in cell A1 and put the different contacts for that company into B1, C1, D1 etc or shoudl i come at this from excel after I export? Thanks in advance

Can I set up a query to show the data in the following layout

so that the information is the same but CustomerName1, Add1, Phone1 etc are all for one contact with CustomerName2,

Add2, Phone2 are for the 2nd contact etc

CompanyName CustomerName1 Add1 Add2 Add3 County ABC John Smith 1 Spruce Avenue Newtown Upstate XYZ ABC Joe Murray 1 Spruce Avenue Newtown Upstate XYZ ABC An Other 3 Cedar Grove Oldtown Over There XYZ

Explanation / Answer

^&^&You would first need to rank the records to get a unique value:

=== qrnkDoie ===
SELECT tblDoie.CompanyName, tblDoie.CustomerName1, tblDoie.Add1, tblDoie.Add2, tblDoie.Add3, tblDoie.Country,

Count(tblDoie_1.CompanyName) AS CountOfCompanyName
FROM tblDoie INNER JOIN tblDoie AS tblDoie_1 ON tblDoie.CompanyName = tblDoie_1.CompanyName
WHERE tblDoie_1.CustomerName1<=[tblDoie].[CustomerName1]
GROUP BY tblDoie.CompanyName, tblDoie.CustomerName1, tblDoie.Add1, tblDoie.Add2,
tblDoie.Add3, tblDoie.Country;

Then create a table of number [tblNums] with a single numeric field [Num] and values 1 - 10.

Then create a crosstab based on the ranking query and tblNums:

=== qxtbDoie ===

TRANSFORM First(IIf([Num]=1,[CustomerName1],[Add1])) AS Expr2
SELECT qrnkDoie.CompanyName
FROM qrnkDoie, tblNums
WHERE (((tblNums.Num) Between 1 And 2))
GROUP BY qrnkDoie.CompanyName
PIVOT IIf([Num]=1,"CustomerName","Add") & [CountOfCompanyName];

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.