Returning value from other table (1 Viewer)

B

bd528

Registered User.
Local time
Today, 06:01
Joined
May 7, 2012
Messages
111
Hi,

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

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

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

ashleedawg

"Here for a good time"
Local time
Today, 06:01
Joined
Jun 22, 2017
Messages
154
Can't say for sure without seeing more than one record of the source data, but perhaps you're wanting something like:
Code: 
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...
Code: 
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". :rolleyes:
 
B

bd528

Registered User.
Local time
Today, 06:01
Joined
May 7, 2012
Messages
111
ashleedawg said:
Can't say for sure without seeing more than one record of the source data, but perhaps you're wanting something like:
Code: 
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...
Code: 
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". :rolleyes:
Click to expand...

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
 
ashleedawg

ashleedawg

"Here for a good time"
Local time
Today, 06:01
Joined
Jun 22, 2017
Messages
154
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.)

Code: 
[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:

Code: 
[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:

Code: 
[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?
 
B

bd528

Registered User.
Local time
Today, 06:01
Joined
May 7, 2012
Messages
111
Code: 
[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?
Click to expand...

This is exactly what I needed. Thank you for taking the provide such a thorough answer, it's really appreciated.
 
You must log in or register to reply here.

Similar threads

raziel3
Most Recent Cost of an Item Query returning Duplicates
Replies
7
Views
697
arnelgp
arnelgp
A
Optimizing Query
Replies
10
Views
484
Josef P.
J
X
Solved How to delete only the oldest (duplicated) records?
Replies
3
Views
686
XelaIrodavlas
X
R
Dont prompt if only one shipping adress
Replies
1
Views
471
Gasman
Gasman
MajP
Solved Aggregate Query Returns Different Values When a Field is Included not in Group By
2
Replies
27
Views
1,621
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom