Transposing Data in Access?

[alsamren]

Hi All,

I am stuck on a query. I have a query that is feeding off another table. The query lists "Code" as one heading and "Support" as another:

code support
abc Jennifer
abc Mark
cde Jeff
fgh Thomas
ijk Chad
ijk Ed

As you can see there is more than one "code" per support name. I need to see the data in the following format:

code Support 1 Support 2 Support 3 Support 4
abc Jennifer Mark
cde Jeff
fgh Thomas
ijk Chad Ed

There can be up to 10 Support names per code. Do you know how to transpose the data in this way?

Thanks!
R.

 

[stopher]

Hi

Suppose your source query is called qryData then the following will do what you want:

TRANSFORM Max(qryData.Support) AS MaxOfSupport
SELECT Code
FROM qryData
GROUP BY Code
PIVOT "Support " & DCount("

","qryData","[Support]<='" & [Support] & "' AND [Code]='" & [Code] & "'")[/Quote]

hth
Chris

 

[alsamren]

Amazing. Thanks!!!

 

[Chas]

Hi

Suppose your source query is called qryData then the following will do what you want:



hth
Chris

Hi Chris:

I have adapted your cross tab query to my situation, but I am getting inconsistent results. Please see the attached mdb. If you run the query, you get blank spaces where there should be a name.

I suspect the problem is in the Pivot part of the query. I have not been able to breakdown the last part of the DCount function.

I have tried modifying the data in the table by changing first names, by changing the SuiteID to alpha characters to try and narrow down what causes the blank spaces, but have not been able to solve this one.

Any help would be appreciated.

Charlie

 

[JHB]

I have tried modifying the data in the table by changing first names, by changing the SuiteID to alpha characters to try and narrow down what causes the blank spaces, but have not been able to solve this one.

Any help would be appreciated.

Charlie

You get blanks for names with the same Lastname or if you have lesser as 3 names with the same SuiteID.

 

[stopher]

You've tried to cook this based on LastName but you really need to do it based on FullName. So create a source that has FullName:

qryFullName:

SELECT SuiteID, Trim(Nz([tblRes.FirstName]) & " " & Nz([tblRes.LastName])) AS FullName
FROM tblRes

Then your main query will look like this (using qryFullName as the source):

TRANSFORM Max(FullName) AS MaxFullName
SELECT qryFullName.SuiteID
FROM qryFullName
GROUP BY qryFullName.SuiteID
PIVOT "FullName" & DCount("[SuiteID]","qryFullName","[FullName]<='" & [FullName] & "' AND [SuiteID]='" & [SuiteID] & "'")

hth
Chris

 

[Chas]

Works perfectly. Thank you Chris!