Group by not working as expected for query

[easyit]

Hi,

I have a query:

SELECT q_NM_QUOTE_ITEM_MXA.NUON_ACCOUNT_ID, q_NM_QUOTE_ITEM_MXA.QUOTE_NAME, q_NM_QUOTE_ITEM_MXA.[SALES FEE], q_NM_QUOTE_ITEM_MXA.[Margin Fee], q_NM_QUOTE_ITEM_MXA.[ES Profile Costs]
FROM q_NM_QUOTE_ITEM_MXA
GROUP BY q_NM_QUOTE_ITEM_MXA.NUON_ACCOUNT_ID, q_NM_QUOTE_ITEM_MXA.QUOTE_NAME, q_NM_QUOTE_ITEM_MXA.[SALES FEE], q_NM_QUOTE_ITEM_MXA.[Margin Fee], q_NM_QUOTE_ITEM_MXA.[ES Profile Costs]
HAVING (((q_NM_QUOTE_ITEM_MXA.QUOTE_NAME)='U12.048396'));

Which gives this result:

ID      nuonaccountID   QUOTE NAME      Column1       column2  column3
1	32526634	U12.048396	0,662			
2	32526634	U12.048396	0,711			
3	32526634	U12.048396	0,862			
4	32526634	U12.048396			0,3	
5	32526634	U12.048396			0,4	
6	32526634	U12.048396			0,5	
7	32526634	U12.048396				0,311
8	32526634	U12.048396				0,362

My issue is that I would expect the values to group by into this:

ID      nuonaccountID   QUOTE NAME      Column1       column2  column3
1	32526634	U12.048396	0,662		0,3	0,311		
2	32526634	U12.048396	0,711		0,4	0,362
3	32526634	U12.048396	0,862		0,5

But that is not happening, the NULL values are not grouped. Can anybody help me with this?




EasyIT

"Do you think that’s air you're breathing?

 

[dhookom]

Your SQL statement has no SUM. Try:

SELECT 
  NUON_ACCOUNT_ID, QUOTE_NAME, Sum([SALES FEE]) As SalesSum, 
  Sum([Margin Fee]) AS MarginSum, Sum([ES Profile Costs]) AS CostsSum
FROM q_NM_QUOTE_ITEM_MXA
WHERE QUOTE_NAME='U12.048396'
GROUP BY 
  NUON_ACCOUNT_ID, QUOTE_NAME;

Duane
Hook'D on Access
MS Access MVP

 

[easyit]

Hello Duane,

Yes, but I do not need a sum. The individual values are wanted.

Maarten


EasyIT

"Do you think that’s air you're breathing?

 

[dhookom]

Considering the fact that records are like marbles in a bag with no real order, how do you identify these values being related:

Column1       column2  column3
0,662		0,3	0,311

I think this can be resolved with multiple ranking queries outer-joined in a single query. Some type of ranking query with a crosstab might also work.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Assuming all the values are unique by fee type, you could try this.

First, create a normalizing union query [quniEasyIT] like:

SELECT ID, nuonaccountID, [quote=NAME], "Sales" AS Fee, [SALES FEE] AS Amt
FROM q_NM_QUOTE_ITEM_MXA
WHERE [SALES FEE] Is Not Null
UNION ALL
SELECT ID, nuonaccountID, [quote=NAME], "Margin", [Margin FEE] AS Amt
FROM q_NM_QUOTE_ITEM_MXA
WHERE [Margin FEE] Is Not Null
UNION ALL
SELECT ID, nuonaccountID, [quote=NAME], "Profile", [ES Profile Costs] AS Amt
FROM q_NM_QUOTE_ITEM_MXA
WHERE [ES Profile Costs] Is Not Null;

Then, create a ranking query [qrnkEasyID] with the following SQL

SELECT quniEasyIT.ID, quniEasyIT.nuonaccountID, quniEasyIT.[quote=NAME], 
quniEasyIT.Fee, quniEasyIT.Amt, Count(quniEasyIT.ID) AS CountOfID1
FROM quniEasyIT INNER JOIN quniEasyIT AS quniEasyIT_1 ON (quniEasyIT.Fee = quniEasyIT_1.Fee)
 AND (quniEasyIT.[quote=NAME] = quniEasyIT_1.[quote=NAME])
 AND (quniEasyIT.nuonaccountID = quniEasyIT_1.nuonaccountID)
WHERE (((quniEasyIT.ID)>=[quniEasyIT_1].[ID]))
GROUP BY quniEasyIT.ID, quniEasyIT.nuonaccountID, quniEasyIT.[quote=NAME],
 quniEasyIT.Fee, quniEasyIT.Amt;

Then, create a crosstab.

TRANSFORM Sum(qrnkEasyID.Amt) AS SumOfAmt
SELECT qrnkEasyID.CountOfID1 Rank, qrnkEasyID.nuonaccountID, qrnkEasyID.[quote=NAME]
FROM qrnkEasyID
GROUP BY qrnkEasyID.CountOfID1, qrnkEasyID.nuonaccountID, qrnkEasyID.[quote=NAME]
PIVOT qrnkEasyID.Fee;

Your result would be

Rank   nuonaccountID  QUOTE NAME   Margin   Profile    Sales
1      32526634       U12.048396    0.3      0.311     0.662
2      32526634       U12.048396    0.4      0.362     0.711
3      32526634       U12.048396    0.5	               0.862

Duane
Hook'D on Access
MS Access MVP