Returning value from other table

[bd528]

Hi,

I have the following query which returns Yes or No if a Stage value is found in tblCommissionNew

SELECT Quote_ID
	,Max(IIf(Val([Stage]) = 1, 'Yes', 'No')) AS Stage_1_Calc
	,Max(IIf(Val([Stage]) = 2, 'Yes', 'No')) AS Stage_2_Calc
	,Max(IIf(Val([Stage]) = 3, 'Yes', 'No')) AS Stage_3_Calc
	,Max(IIf(Val([Stage]) = 4, 'Yes', 'No')) AS Stage_4_Calc
	,Max(IIf(Val([Stage]) = 5, 'Yes', 'No')) AS Stage_5_Calc
	,Max(IIf(Val([Stage]) = 6, 'Yes', 'No')) AS Stage_6_Calc
FROM tblCommissionNew
GROUP BY Quote_ID;

What I need to do is rather than return Yes or No, return the Cost from tblCommissionNew.

tblCommissionNew is setup as follows

• CommissionID
• Stage
• Cost

Currently an example output is :-

35235 / Yes / No / No / No / No / No

And what I need is :-

35235 / 13.95 / Null / Null / Null / Null / Null

I have tried :-

SELECT Quote_ID
	,Max(IIf(Val([Stage]) = 1, [Cost], '')) AS Stage_1_Calc
	,Max(IIf(Val([Stage]) = 2, [Cost], '')) AS Stage_2_Calc
	,Max(IIf(Val([Stage]) = 3, [Cost], '')) AS Stage_3_Calc
	,Max(IIf(Val([Stage]) = 4, [Cost], '')) AS Stage_4_Calc
	,Max(IIf(Val([Stage]) = 5, [Cost], '')) AS Stage_5_Calc
	,Max(IIf(Val([Stage]) = 6, [Cost], '')) AS Stage_6_Calc
FROM tblCommissionNew
GROUP BY Quote_ID;

but this did not returned the required results

 

[ashleedawg]

Can't say for sure without seeing more than one record of the source data, but perhaps you're wanting something like:

SELECT Quote_ID, Cost AS Stage_1_Calc, Null AS Stage_2_Calc, Null AS Stage_3_Calc, 
Null AS Stage_4_Calc, Null AS Stage_5_Calc,Null AS Stage_6_Calc 
FROM tblCommissionNew;

or maybe with a sum...

SELECT Quote_ID, sum(Cost) AS Stage_1_Calc,  Null AS Stage_2_Calc, Null AS Stage_3_Calc, 
Null AS Stage_4_Calc, Null  AS Stage_5_Calc,Null AS Stage_6_Calc 
FROM tblCommissionNew GROUP BY Quote_ID;

If that's not what you need please post a little more detail...
I won't ask why you want a query to put out mostly "no data".

 

[bd528]

Can't say for sure without seeing more than one record of the source data, but perhaps you're wanting something like:

SELECT Quote_ID, Cost AS Stage_1_Calc, Null AS Stage_2_Calc, Null AS Stage_3_Calc, 
Null AS Stage_4_Calc, Null AS Stage_5_Calc,Null AS Stage_6_Calc 
FROM tblCommissionNew;

or maybe with a sum...

SELECT Quote_ID, sum(Cost) AS Stage_1_Calc,  Null AS Stage_2_Calc, Null AS Stage_3_Calc, 
Null AS Stage_4_Calc, Null  AS Stage_5_Calc,Null AS Stage_6_Calc 
FROM tblCommissionNew GROUP BY Quote_ID;

If that's not what you need please post a little more detail...
I won't ask why you want a query to put out mostly "no data".

Sorry, maybe I didn't explain myself very well.

Each quote potentially could have a cost at stage 1-6. Null should only e returned if there is not a cost associated with that stage

More sample data is :-

35235 / Yes / No / No / No / No / No
35236 / Yes / Yes / No / No / No / No
35237 / Yes / Yes / Yes / No / No / No

And what I need is :-

35235 / 13.95 / Null / Null / Null / Null / Null
35236 / 11.95 / 4.00 / Null / Null / Null / Null
35237 / 22.00 / 3.00 / 1.50 / Null / Null / Null

 

[plog]

I have 2 thoughts as well:

1. A Cross-tab query: https://support.office.com/en-us/ar...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8

2. Don't put any Else value in your Iif statements:

IIf([Stage] = 1, [Cost])

 

[ashleedawg]

Now I gotcha. As with most tasks in Access, there are a few ways to accomplish that. Here's one - you'll notice it's close to your attempt in Post #1. SUM instead of MAX will ensure that no fields are missed (if, by chance there was multiple records for the same Quote_ID & Stage). Also mathematically, NULL works better than zero-length-string (''). (NULL would be the same idea as Plog's suggestion #2 in post #4.)

[B][COLOR=SeaGreen]qryCommission_calc :[/COLOR][/B]
SELECT Quote_ID, 
    Sum(IIf(Val([Stage])=1,[cost],Null)) AS Stage_1_Calc, 
    Sum(IIf(Val([Stage])=2,[cost],Null)) AS Stage_2_Calc, 
    Sum(IIf(Val([Stage])=3,[cost],Null)) AS Stage_3_Calc, 
    Sum(IIf(Val([Stage])=4,[cost],Null)) AS Stage_4_Calc, 
    Sum(IIf(Val([Stage])=5,[cost],Null)) AS Stage_5_Calc, 
    Sum(IIf(Val([Stage])=6,[cost],Null)) AS Stage_6_Calc
FROM tblCommissionNew
GROUP BY Quote_ID;

[SIZE=2][U]Quote_ID  Stage_1_Calc  Stage_2_Calc  Stage_3_Calc  Stage_4_Calc  Stage_5_Calc  Stage_6_Calc[/U]
35235      $13.95                                                                    
35236      $11.95        $4.00                                                                
35237      $22.00        $3.00         $1.50                                                
[/SIZE]

When transforming a single column of data into multiple column headers a Crosstab (pivot) Query is a good idea because it will allow more flexibility than "hard-coded" expressions; 6 Stages, in this case. Should a Stage #7 (etc) show up, a Crosstab Query will automatically report it under a new column. The built-in Query Wizard quickly put this together:

[COLOR=SeaGreen][B]qryCommission_crosstab :[/B][/COLOR]
TRANSFORM Sum([cost])
    SELECT Quote_ID
    FROM tblCommissionNew
    GROUP BY Quote_ID
    PIVOT 'Stage_' & Val([Stage]) & '_Calc';
[U]
[SIZE=2]Quote_ID  Stage_1_Calc  Stage_2_Calc  Stage_3_Calc[/SIZE][/U][SIZE=2]
35235      $13.95                          
35236      $11.95        $4.00                      
35237      $22.00        $3.00         $1.50      
[/SIZE]

…and if you need to make sure that "at least" all 6 fields are included, there's a "Column Headers" option for that which shows up as a SQL "In" clause:

[COLOR=SeaGreen][B]qryCommission_crosstab_6heads :[/B][/COLOR]
TRANSFORM Sum(cost) 
    SELECT Quote_ID
    FROM tblCommissionNew
    GROUP BY Quote_ID
    PIVOT 'Stage_' & Val([Stage]) & '_Calc' 
    In ("Stage_1_Calc", "Stage_2_Calc", "Stage_3_Calc", 
        "Stage_4_Calc", "Stage_5_Calc", "Stage_6_Calc");

[SIZE=2][U]Quote_ID  Stage_1_Calc  Stage_2_Calc  Stage_3_Calc  Stage_4_Calc  Stage_5_Calc  Stage_6_Calc[/U]
35235      $13.95                                                                    
35236      $11.95        $4.00                                                                
35237      $22.00        $3.00         $1.50                                                
[/SIZE]

...Is that more what you had in mind?

 

[bd528]

[COLOR=SeaGreen][B]qryCommission_crosstab_6heads :[/B][/COLOR]
TRANSFORM Sum(cost) 
    SELECT Quote_ID
    FROM tblCommissionNew
    GROUP BY Quote_ID
    PIVOT 'Stage_' & Val([Stage]) & '_Calc' 
    In ("Stage_1_Calc", "Stage_2_Calc", "Stage_3_Calc", 
        "Stage_4_Calc", "Stage_5_Calc", "Stage_6_Calc");

[SIZE=2][U]Quote_ID  Stage_1_Calc  Stage_2_Calc  Stage_3_Calc  Stage_4_Calc  Stage_5_Calc  Stage_6_Calc[/U]
35235      $13.95                                                                    
35236      $11.95        $4.00                                                                
35237      $22.00        $3.00         $1.50                                                
[/SIZE]

...Is that more what you had in mind?

This is exactly what I needed. Thank you for taking the provide such a thorough answer, it's really appreciated.