asked on

Crosstab Query Pivot Values

I have a Crosstab query below:

TRANSFORM First(qryPath_Goal.Status) AS FirstOfStatus
SELECT qryPath_Goal.[GOAL CODE], GOALS.[GOAL DESCRIPTION], Len([GOAL DESCRIPTION]) AS Length
FROM qryPath_Goal INNER JOIN GOALS ON qryPath_Goal.[GOAL CODE]=GOALS.[GOAL CODE]
WHERE (((qryPath_Goal.PATH)="CCM" Or (qryPath_Goal.PATH)="DBT" Or (qryPath_Goal.PATH)="AST" Or (qryPath_Goal.PATH)="CAD" Or (qryPath_Goal.PATH)="CHF" Or (qryPath_Goal.PATH)="BRT" Or (qryPath_Goal.PATH)="PRS" Or (qryPath_Goal.PATH)="CRB" Or (qryPath_Goal.PATH)="CLR" Or (qryPath_Goal.PATH)="OVR" Or (qryPath_Goal.PATH)="COP" Or (qryPath_Goal.PATH)="PVR" Or (qryPath_Goal.PATH)="LBP"))
GROUP BY qryPath_Goal.[GOAL CODE], GOALS.[GOAL DESCRIPTION], Len([GOAL DESCRIPTION])
PIVOT qryPath_Goal.PATH In ("CAD","CHF","AST","DBT","CCM","BRT","PRS","OVR","CLR","CRB","COP","PVR");

As you can see, I have several 3 letter codes which I am using as my criteria:
("CAD","CHF","AST","DBT","CCM","BRT","PRS","OVR","CLR","CRB","COP","PVR");

I need to specify which codes this query will use. I am doing this in the WHERE clause and in the PIVOT IN Clause.
Eventually I'm going to have about 30 of these codes. I currently have about 10 queries similar to this one.

Instead of manually updating all of the queries everytime a new code gets added, can I store these strings somewhere in one place so I can update all of the queries at once when I need to add a new code?

Leigh Purvis

You should really have a table of these codes.
tblCodeList
With a simple field CodeNo

Then you can have
...In (SELECT CodeNo FROM tblCodeList);

dsg138

ASKER

LPurvis, Thanks for the suggestion.
I was thinking along those lines but I still can't get the syntax quite right.

When I put the values in the table, and add:
(SELECT CodeNo FROM tblCodeList);
I get only one additional column that is empty and contains no values.
The header of the column says: "SELECT CodeNo FROM tblCodeList"
instead of the codes being the headers.

dsg138

ASKER

Leigh, the Crosstab is still not returning any values when I use the follwing line:

PIVOT qryPath_Goal.PATH In (SELECT CodeNo FROM tblCodeList);

Do you know if this is possible with the PIVOT IN clause?

Rey Obrero (Capricorn1)

dsg138
you need a function to do this, place this codes in a module

function pivotstring(tablename as string) as string
dim rs as dao.recordset, s as string
set rs=currentdb.openrecordset ("select codeno from " & [tablename] &" ")
do until rs.eof
s=s & chr(34) & rs(0) & chr(34) & ","
rs.movenext

loop
pivotstring=left(s,len(s)-1)
end function

now you can use it this way in a query

PIVOT qryPath_Goal.PATH In (" & pivotstring("tblCodeList") & ");

dsg138

ASKER

Thanks Capricorn1,
I've created the function and my code now says:
PIVOT qryPath_Goal.PATH In (" & pivotstring("tblCodeList") & ");

I get a syntax error, missing operator message.

Any thoughts?

Rey Obrero (Capricorn1)

can you post the codes in the module that you created?
also, post the data from codeno in tblCodeList

dsg138

ASKER

Sure. Here you go!

tblCodeList
----------------------
Order      CodeNo
1      CAD
2      CHF
3      AST
4      DBT
5      CCM
6      BRT
7      PRS
8      OVR
9      CLR
10      CRB
11      COP
12      PVR
13      LBP

Code in Module1...
------------------------------------
Option Compare Database

Function pivotstring(tablename As String) As String
Dim rs As DAO.Recordset, s As String
Set rs = CurrentDb.OpenRecordset("select CodeNo from " & [tablename] & " ")
Do Until rs.EOF
s = s & Chr(34) & rs(0) & Chr(34) & ","
rs.MoveNext

Loop
pivotstring = Left(s, Len(s) - 1)
End Function

Rey Obrero (Capricorn1)

looks ok, error must be in your query

can you post the whole query

dsg138

ASKER

Here is the Crosstab Query
I think the syntax error is coming from the last line.

TRANSFORM First(qryPath_Goal.Status) AS FirstOfStatus
SELECT qryPath_Goal.[GOAL CODE], GOALS.[GOAL DESCRIPTION], Len([GOAL DESCRIPTION]) AS Length
FROM qryPath_Goal INNER JOIN GOALS ON qryPath_Goal.[GOAL CODE] = GOALS.[GOAL CODE]
WHERE (((qryPath_Goal.PATH)="CCM" Or (qryPath_Goal.PATH)="DBT" Or (qryPath_Goal.PATH)="AST" Or (qryPath_Goal.PATH)="CAD" Or (qryPath_Goal.PATH)="CHF" Or (qryPath_Goal.PATH)="BRT" Or (qryPath_Goal.PATH)="PRS" Or (qryPath_Goal.PATH)="CRB" Or (qryPath_Goal.PATH)="CLR" Or (qryPath_Goal.PATH)="OVR" Or (qryPath_Goal.PATH)="COP" Or (qryPath_Goal.PATH)="PVR" Or (qryPath_Goal.PATH)="LBP"))
GROUP BY qryPath_Goal.[GOAL CODE], GOALS.[GOAL DESCRIPTION], Len([GOAL DESCRIPTION])
PIVOT qryPath_Goal.PATH In (" & pivotstring("tblCodeList") & ");

Rey Obrero (Capricorn1)

ok, try this
----------------------------------------------------v------------v single quote
PIVOT qryPath_Goal.PATH In (" & pivotstring('tblCodeList') & ");

dsg138

ASKER

We're getting closer....
That fixed the syntax error.

The Query now Runs, but instead of having the 13 extra columns for each code, I have one extra column titled:
& pivotstring('tblCodeList') & and with no data.

dsg138

ASKER

Capricorn1,

I am still unable to call this function from the PIVOT IN statement.

Here are the syntax variations that I've tried and the results:
1. PIVOT qryPath_Goal.PATH In (" & pivotstring('tblCodeList') & ");
Result: Column is added to the crosstab named: & pivotstring('tblCodeList') &
Contains no values

2. PIVOT qryPath_Goal.PATH In (' & pivotstring('tblCodeList') & ');
Result: Syntax Error, query does not generate.

3. PIVOT qryPath_Goal.PATH In (& pivotstring('tblCodeList') &);
Result: Syntax Error, query does not generate.

4. PIVOT qryPath_Goal.PATH In (pivotstring('tblCodeList'));
Result: Missing (,], or item in Query Expression Message.

I believe the function you created should work correctly.
The problem seems to be finding a way to call it.
Do you know if the PIVOT IN condition allows this?
Any thoughts?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dsg138

ASKER

Capricorn1,
That solution worked perfectly. I made similar functions for my other queries and now I can updated them all with one button.

I'm going to add more points to this question since you spent a lot of time on this and went beyond what my original question was asking. Thanks for your help!

Rey Obrero (Capricorn1)

U R Welcome!!! Thanks for the points.