Having Subquery Trouble

[sumnicme]

I have the following query:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item No[/TD]
[TD]Item Class[/TD]
[TD]Dept Code[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]Raw Material[/TD]
[TD]1[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]Raw Material[/TD]
[TD]2[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]Raw Material[/TD]
[TD]3[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Raw Material[/TD]
[TD]1[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Raw Material[/TD]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Raw Material[/TD]
[TD]3[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]0004[/TD]
[TD]WIP Item[/TD]
[TD]4[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]WIP Item[/TD]
[TD]4[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]WIP Item[/TD]
[TD]5[/TD]
[TD]175[/TD]
[/TR]
</tbody>[/TABLE]


I want to add another column showing what percentage of each item's total consumption is related to the department:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item No[/TD]
[TD]Item Class[/TD]
[TD]Dept Code[/TD]
[TD]Quantity[/TD]
[TD]Item Total[/TD]
[TD]% of Quantity[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]Raw Material[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD]80[/TD]
[TD]62.5%[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]Raw Material[/TD]
[TD]2[/TD]
[TD]30[/TD]
[TD]80[/TD]
[TD]37.5%[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]Raw Material[/TD]
[TD]3[/TD]
[TD]90
[/TD]
[TD]90[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Raw Material[/TD]
[TD]1[/TD]
[TD]75[/TD]
[TD]195[/TD]
[TD]38.5%[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Raw Material[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]195[/TD]
[TD]51.3%[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Raw Material[/TD]
[TD]3[/TD]
[TD]20[/TD]
[TD]195[/TD]
[TD]10.3%[/TD]
[/TR]
[TR]
[TD]0004[/TD]
[TD]WIP Item[/TD]
[TD]4[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]WIP Item[/TD]
[TD]4[/TD]
[TD]100[/TD]
[TD]275[/TD]
[TD]36.4%[/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]WIP Item[/TD]
[TD]5[/TD]
[TD]175[/TD]
[TD]275[/TD]
[TD]65.6%[/TD]
[/TR]
</tbody>[/TABLE]

I've tried the below text, but I get a syntax error

Item Total: (Select Sum(Quantity)
FROM [Consumption by Item & Dept] AS [Total Consumption by Item]
WHERE [Total Consumption by Item].[Item No] = [Consumption by Item & Dept].[Item No])

I am very new to access and have tried to correct this a few ways, but don't know what I'm doing wrong.

Thanks!

 

[Micron]

Did you review anything like this http://allenbrowne.com/subquery-01.html
Not 100% sure, but I think it has to be a simpler Select query. You are trying to perform an aggregate function at the same time (Sum).

 

[xenou]

I have the following query:
Item No Item Class Dept Code Quantity
0001 Raw Material 1 50
0001 Raw Material 2 30
0002 Raw Material 3 100
0003 Raw Material 1 75
0003 Raw Material 2 100
0003 Raw Material 3 20
0004 WIP Item 4 100
0005 WIP Item 4 100
0005 WIP Item 5 175

An independent second query would be useful:
-------------------------------------
| Item No | Item Class   | Quantity |
-------------------------------------
|       1 | Raw Material |       80 |
|       2 | Raw Material |      100 |
|       3 | Raw Material |      195 |
|       4 | WIP Item     |      100 |
|       5 | WIP Item     |      275 |
-------------------------------------

using those two queries together with a simple join statement would get you to the results you want I think. This would be an alternative to using subqueries (probably there is more than one possible solution to this one).

 

[sumnicme]

I've tried using an independent query and it pulls in the total number, but when it comes time to calculate the percentage dividing column 4 by column 5, it asks me to enter a parameter value and I can't figure out why.

 

[xenou]

what is your SQL?

 

[sumnicme]

Expr1: [SumOfQuantity]/[Sum Of Quantity]

 

[xenou]

What is the entire SQL?

 

[sumnicme]

SELECT [ILE - Consumption].[Item No], [Item List].[Item Type], [Document Dept].[Dept Code], Sum([ILE - Consumption].Quantity) AS SumOfQuantity, Sum([ILE - Consumption].[Cost Amount (Actual)]) AS [SumOfCost Amount (Actual)], [Consumption by Item].[Sum Of Quantity], [SumOfQuantity]/[Sum Of Quantity] AS Expr1
FROM ([Document Dept] INNER JOIN ([Item List] INNER JOIN [ILE - Consumption] ON [Item List].[Item No] = [ILE - Consumption].[Item No]) ON [Document Dept].[No] = [ILE - Consumption].[Document No]) INNER JOIN [Consumption by Item] ON [Item List].[Item No] = [Consumption by Item].[Item No]
GROUP BY [ILE - Consumption].[Item No], [Item List].[Item Type], [Document Dept].[Dept Code], [Consumption by Item].[Sum Of Quantity], [SumOfQuantity]/[Sum Of Quantity];

 

[xenou]

This was not my suggestion. For starters, you have four tables here not two. You also haven't worked out the sum in an independent query (a single query all by itself that would give you three columns - Item No, Item Class, and Quantity.

 

[xenou]

To reiterate, the suggestion was to use two queries. One was your first query in your first post. The other is the one I suggested you create in my first post.

Then you simply join them (ignoring for now the inevitable division by zero errors and also whether or not you need to be careful about integer vs. floats in your quantity values).

/* 
COMMENT
    Query1: returns ItemNo, ItemClass, DeptCode, Quantity 
    Query2: returns ItemNo, ItemClass, ItemTotalQuantity
END COMMENT
*/

select 
	Query1.ItemNo, 
	Query1.ItemClass, 
	Query1.DeptCode, 
	Query1.Quantity, 
	Query2.ItemTotalQuantity, 
	(Query1.Quantity/Query2.ItemTotalQuantity) as PCTOfQuantity
from
	Query1
	inner join Query2
	on Query1.ItemNo = Query2.ItemNo
	and Query1.ItemClass = Query2.ItemClass

Don't use the comments in MSAccess and adjust columns names as needed - columns with spaces or special characters must be enclosed in [brackets]