I have the following query:
<tbody>
</tbody>
I want to add another column showing what percentage of each item's total consumption is related to the department:
<tbody>
</tbody>
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!
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 |
<tbody>
</tbody>
I want to add another column showing what percentage of each item's total consumption is related to the department:
Item No | Item Class | Dept Code | Quantity | Item Total | % of Quantity |
0001 | Raw Material | 1 | 50 | 80 | 62.5% |
0001 | Raw Material | 2 | 30 | 80 | 37.5% |
0002 | Raw Material | 3 | 90 | 90 | 100% |
0003 | Raw Material | 1 | 75 | 195 | 38.5% |
0003 | Raw Material | 2 | 100 | 195 | 51.3% |
0003 | Raw Material | 3 | 20 | 195 | 10.3% |
0004 | WIP Item | 4 | 100 | 100 | 100% |
0005 | WIP Item | 4 | 100 | 275 | 36.4% |
0005 | WIP Item | 5 | 175 | 275 | 65.6% |
<tbody>
</tbody>
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!