Transposing Data in Access?

alsamren · Feb 1, 2012

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 · Feb 2, 2012

Hi

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

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

Code:

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

alsamren · Feb 2, 2012

Amazing. Thanks!!!

Chas · Jul 15, 2013

stopher said:
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 · Jul 15, 2013

Chas said:
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 · Jul 16, 2013

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:

Code:

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):

Code:

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 · Jul 16, 2013

Works perfectly. Thank you Chris!

Transposing Data in Access?

alsamren

Registered User.

stopher

AWF VIP

alsamren

Registered User.

Chas

New member

Attachments

JHB

Have been here a while

stopher

AWF VIP

Chas

New member

Similar threads

Users who are viewing this thread