Move Values to Fields in Query?

[kramer31]

Hi. I'm not even sure that this is possible, but here's my
situation. I have data whose attributes may increase as follows.

Table Defects
Integer: StepId
Integer: DefectType
Double: AmountOfDefects

Table StepInfo
Integer: StepId
Text: StepName

Table DefectInfo
Integer: DefectType
Text: DefectName

Now Defects.DefectType links to DefectInfo.DefectType and
Defects.StepId links to StepInfo.StepId. Both are integers that start
with 1 and go up to N.

I need to write at least a query and preferably a report (although I'm
not sure that's even possible) that joins these three tables and that
has the following columns:

StepName Defects1 Defects2 Defects3 Defects4 ... DefectsN

where Defects1 is the AmountOfDefects in the record in the table
Defects such that Defects.DefectType=1. And StepName is the StepName
from the table StepInfo that has the StepId that corresponds to the
StepId in the table Defects. And there should be a DefectsX column
for each record in the table DefectsInfo regardless of whether there
is a corresponding record in the table defects (but that value should
be zero).

Is it even possible to generate columns in a query from values in a
table?

Thanks.

 

[Michel Walsh]

Sure, that is the job of a crosstab:


TRANSFORM Nz(SUM(AmountOfDefects), 0)
SELECT StepName
FROM (StepInfo INNER JOIN Defects
ON StepInfo.StepID=Defects.StepID)
INNER JOIN DefectInfo
ON DefectInfo.DefectType=Defects.DefectType
GROUP BY StepName
PIVOT DefectName



To make it really available to a report, you have to add an IN clause in the
PIVOT:


PIVOT DefectName IN("Default1", ... )


where the list is a list of constant (here, strings) that would correspond
to the new fields name.



Hoping it may help,
Vanderghast, Access MVP

 

[kramer31]

Thanks again, and one more question:

What if I needed to put more than one field as a result column in the
query. For example, I have a similar table that holds defect
statistical information rather than just the raw numbers, so I tried
to do this:

TRANSFORM Nz(SUM(AmountOfDefects), 0), Nz(SUM(AmountOfDefects), 0)
SELECT StepName
FROM (StepInfo INNER JOIN Defects
ON StepInfo.StepID=Defects.StepID)
INNER JOIN DefectInfo
ON DefectInfo.DefectType=Defects.DefectType
GROUP BY StepName
PIVOT DefectName

But Access just yelled at me. Any ideas?

 

[Michel Walsh]

You can, sometimes, with a trick, or, more general, doing two crosstabs and
next, in a third query, join them on the first two on their StepName.

The trick, once explained by Duane, I think, is to get a table, one field,
f1, with two records, one field, values 1 and 2, for simplicity, then:


TRANSFORM Nz(SUM( iif(f1=1, AmountOfDefects, OtherFieldName) ), 0)
SELECT stepName
FROM originalTable, tableWithTwoRecords
GROUP BY stepName
PIVOT DefectName & "_" & f1



Hoping it may help,
Vanderghast, Access MVP

 

[kramer31]

Hi. Thanks for all of your help. I really appreciate it. I got most
of the crosstab stuff to work except for the IN clause.

I get the error:

"Data type mismatch in criteria expression."

from the following query:


TRANSFORM Nz(SUM(Detected_Value), 0)
SELECT Phase_Data_IJ_Project_Data_DefectInfo.Project_Id,
Phase_Data_IJ_Project_Data_DefectInfo.Project_Name,
Phase_Data_IJ_Project_Data_DefectInfo.Phase_Id,
Phase_Data_IJ_Project_Data_DefectInfo.Phase_Name
FROM Phase_Defects RIGHT JOIN Phase_Data_IJ_Project_Data_DefectInfo ON
Phase_Defects.Phase_Id=Phase_Data_IJ_Project_Data_DefectInfo.Phase_Id
GROUP BY Phase_Data_IJ_Project_Data_DefectInfo.Project_Id,
Phase_Data_IJ_Project_Data_DefectInfo.Project_Name,
Phase_Data_IJ_Project_Data_DefectInfo.Phase_Id,
Phase_Data_IJ_Project_Data_DefectInfo.Phase_Name,
Phase_Data_IJ_Project_Data_DefectInfo.Project_Id,
Phase_Data_IJ_Project_Data_DefectInfo.Project_Name
PIVOT Phase_Data_IJ_Project_Data_DefectInfo.DefectTypeID
IN('Type1Defects', 'Type2Defects', 'Type3Defects', 'Type4Defects',
'Type5Defects', 'Type6Defects', 'Type7Defects');


When I take out the IN clause, it works just fine and there are
exactly 7 unnamed columns. I've tried both single and double quotes,
but no luck.

Any further help would be much appreciated.

 

[Michel Walsh]

It is quite unusual to get un-named columns with a crosstab... you can get
<> if the PIVOT expression is null, but SEVEN un-named columns imply Jet
see 7 different 'things' as pivot. Your field

Phase_Data_IJ_Project_Data_DefectInfo.DefectTypeID


is really a string? or is it a number that is DISPLAYED (formatted) as a
string through a LOOKUP ?



Vanderghast, Access MVP

 

[Kramer]

Never mind, I figured it out. I just have to make sure that IN
contains the actual column names. You are also right. They are
named. I was pivoting on the wrong variable which was giving me
numbers rather than the strings I was expecting.