Solved

# MS Access Query Help For Graph Preparing

Posted on 2016-11-12
Medium Priority
31 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 31

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

LVL 1

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

LVL 1

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

LVL 1

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

LVL 1

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

LVL 1

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 2000 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

LVL 1

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

LVL 1

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
###### Suggested Courses
Course of the Month5 days, 19 hours left to enroll

#### 589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.