?
Solved

MS Access Query Help For Graph Preparing

Posted on 2016-11-12
13
Medium Priority
?
31 Views
Last Modified: 2016-11-12
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
Comment
Question by:Dustin Stanley
  • 7
  • 5
13 Comments
 
LVL 31

Expert Comment

by:hnasr
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

by:Dustin Stanley
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));

Open in new window

0
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41885043
I can't upload any databases currently
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

by:Rey Obrero (Capricorn1)
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

by:Dustin Stanley
ID: 41885052
It says syntax error highlights as before NSOP
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

by:Rey Obrero (Capricorn1)
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

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

Open in new window


I've been trying trying to figure it out
0
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41885058
Sorry I meant

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

Accepted Solution

by:
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

by:Dustin Stanley
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

by:Dustin Stanley
ID: 41885084
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…

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.

Join & Ask a Question