Solved

MS Access Query Help For Graph Preparing

Posted on 2016-11-12
13
16 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 30

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
 

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
 

Author Comment

by:Dustin Stanley
ID: 41885043
I can't upload any databases currently
0
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

by:Dustin Stanley
ID: 41885052
It says syntax error highlights as before NSOP
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

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
 

Author Comment

by:Dustin Stanley
ID: 41885058
Sorry I meant

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

Accepted Solution

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

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
 

Author Closing Comment

by:Dustin Stanley
ID: 41885084
Thanks!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now