Move Values to Fields in Query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kramer31 - 20 Mar 2007 06:40 GMT

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.

Reply to this Message

Michel Walsh - 20 Mar 2007 11:25 GMT

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

> Hi. I'm not even sure that this is possible, but here's my
> situation. I have data whose attributes may increase as follows.
[quoted text clipped - 34 lines]
>
> Thanks.

Reply to this Message

kramer31 - 20 Mar 2007 20:42 GMT

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?

> Sure, that is the job of a crosstab:
>
[quoted text clipped - 56 lines]
>
> > Thanks.

Reply to this Message

Michel Walsh - 20 Mar 2007 21:02 GMT

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

> Thanks again, and one more question:
>
[quoted text clipped - 76 lines]
>>
>> > Thanks.

Reply to this Message

kramer31 - 21 Mar 2007 19:33 GMT

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.

> 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.
[quoted text clipped - 95 lines]
>
> >> > Thanks.

Reply to this Message

Michel Walsh - 21 Mar 2007 22:32 GMT

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

> 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.
[quoted text clipped - 135 lines]
>>
>> >> > Thanks.

Reply to this Message

Kramer - 22 Mar 2007 00:28 GMT

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.

On Mar 21, 2:32 pm, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:

> 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
[quoted text clipped - 150 lines]
>
> >> >> > Thanks.

Reply to this Message