Solved

# MS Access Query Help For Graph Preparing

Posted on 2016-11-12
19 Views
I need help with setting up a query. I don't know if I need multiple queries or if I can get this all in one.

I have 4 conditions for products. I am wanting to make a query that shows all inventory cost. Quantity * Price kind of thing.

I have successfully been able to do this with all inventory conditions. But I can't figure out how to show them all separate in a single query. I am preparing for a graph page. 4 Conditions are NSOP, NSSP, USOP, USSP.

Total: [qtyloc]*[Price]
(ProdLocations.QtyLoc)>0)

NSOP Total: [qtyloc]*[Price]
[Products].[ConditionId]=1

NSOP Total: [qtyloc]*[Price]
[Products].[ConditionId]=2

USOP Total: [qtyloc]*[Price]
[Products].[ConditionId]=3

USSP Total: [qtyloc]*[Price]
[Products].[ConditionId]=4

Thanks!
0
Question by:Dustin Stanley
• 7
• 5

LVL 30

Expert Comment

ID: 41885040
I have successfully been able to do this with all inventory conditions
Share with us what you have done, by uploading a sample database, to take off from that point.
0

Author Comment

ID: 41885042
I have this but it shows all the same values. Which is the Total Value of All Conditions:

``````SELECT [qtyloc]*[Price] AS Total, ProdLocations.QtyLoc, [qtyloc]*[Price] AS [NSOP Total], [qtyloc]*[Price] AS [NSSP Total], [qtyloc]*[Price] AS [USOP Total], [qtyloc]*[Price] AS [USSP Total]
FROM Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
WHERE (((ProdLocations.QtyLoc)>0)) OR (((Products.ConditionId)=1)) OR (((Products.ConditionId)=2)) OR (((Products.ConditionId)=3)) OR (((Products.ConditionId)=4));
``````
0

Author Comment

ID: 41885043
I can't upload any databases currently
0

LVL 120

Expert Comment

ID: 41885048
try creating a total query like this

select Products.productid, sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0) as Total
, sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0) as Total
,sum(iif([Products].[ConditionId]=1, [qtyloc]*[Price],0) as [NSOP Total]
,sum(iif([Products].[ConditionId]=2, [qtyloc]*[Price],0) as [NSSP Total]
,sum(iif([Products].[ConditionId]=3, [qtyloc]*[Price],0) as [USOP Total]
,sum(iif([Products].[ConditionId]=4, [qtyloc]*[Price],0) as [USSP Total]
From Products
group by  Products.productid

change productID field with correct field name
0

LVL 120

Expert Comment

ID: 41885051
try this one

select Products.productid, sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0) as Total
,sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0) as Total
,sum(iif([Products].[ConditionId]=1, [qtyloc]*[Price],0) as [NSOP Total]
,sum(iif([Products].[ConditionId]=2, [qtyloc]*[Price],0) as [NSSP Total]
,sum(iif([Products].[ConditionId]=3, [qtyloc]*[Price],0) as [USOP Total]
,sum(iif([Products].[ConditionId]=4, [qtyloc]*[Price],0) as [USSP Total]
From Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
group by  Products.productid
0

Author Comment

ID: 41885052
It says syntax error highlights as before NSOP
0

LVL 120

Expert Comment

ID: 41885055
missing ")"

select Products.productid, sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0)) as Total
,sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0)) as Total
,sum(iif([Products].[ConditionId]=1, [qtyloc]*[Price],0)) as [NSOP Total]
,sum(iif([Products].[ConditionId]=2, [qtyloc]*[Price],0)) as [NSSP Total]
,sum(iif([Products].[ConditionId]=3, [qtyloc]*[Price],0)) as [USOP Total]
,sum(iif([Products].[ConditionId]=4, [qtyloc]*[Price],0)) as [USSP Total]
From Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
group by  Products.productid
0

LVL 120

Expert Comment

ID: 41885056
try this one

select Products.productid
, sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0)) as Total
,sum(iif([Products].[ConditionId]=1, [qtyloc]*[Price],0)) as [NSOP Total]
,sum(iif([Products].[ConditionId]=2, [qtyloc]*[Price],0)) as [NSSP Total]
,sum(iif([Products].[ConditionId]=3, [qtyloc]*[Price],0)) as [USOP Total]
,sum(iif([Products].[ConditionId]=4, [qtyloc]*[Price],0)) as [USSP Total]
From Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
group by  Products.productid
0

Author Comment

ID: 41885057
it keeps saying wrong number of arguments for:
``````sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0) as Total
``````

I've been trying trying to figure it out
0

Author Comment

ID: 41885058
Sorry I meant

sum(iif(ProdLocations.QtyLoc)>0,[qtyloc]*[Price],0) as Total
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 41885059
select Products.productid
, sum(iif([ProdLocations].[QtyLoc]>0,[qtyloc]*[Price],0)) as Total
,sum(iif([Products].[ConditionId]=1, [qtyloc]*[Price],0)) as [NSOP Total]
,sum(iif([Products].[ConditionId]=2, [qtyloc]*[Price],0)) as [NSSP Total]
,sum(iif([Products].[ConditionId]=3, [qtyloc]*[Price],0)) as [USOP Total]
,sum(iif([Products].[ConditionId]=4, [qtyloc]*[Price],0)) as [USSP Total]
From Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
group by  Products.productid
0

Author Comment

ID: 41885083
Sorry I didn't respond right back as I had to leave for a bit. Also Thanks so much Rey that last one nailed it. Thanks.
0

Author Closing Comment

ID: 41885084
Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Itâ€™s been over a monthÂ into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targetiâ€¦
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if youâ€™re going to set referential integrity: Decide if you want cascade updaâ€¦
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Usâ€¦