Issue with Subqueries (Nested Queries)

Question

Hi, I am working on a project but I am running into trouble with one of the queries:

SELECT Stock.[Lot Number], Stock.Stone, Stock.Shape, Stock.Size, Stock.Cut, Stock.Mins, Stock.[Color], Sum(Query1.AdjQty) AS Stock, Sum(Query1.TotalAmt)/Sum(Query1.AdjQty) AS [Average Cost], Sum(Query2.TotalAmount)/Sum(Query2.AdjustedQty) AS [Purchase Price]

FROM (Stock INNER JOIN

(SELECT Transactions.[Transaction ID], Transactions.[Lot Number], Transactions.Date, Transactions.[Transaction Type], Transactions.Quantity, Transactions.[Price], IIf([Transaction Type]="Sale",[Transactions.Quantity]*-1,[Transactions.Quantity]) AS AdjQty, AdjQty*Transactions.[Price] AS TotalAmt FROM Transactions

GROUP BY Transactions.[Transaction ID], Transactions.[Lot Number], Transactions.Date, Transactions.[Transaction Type], Transactions.Quantity, Transactions.[Price], IIf([Transaction Type]="Sale",[Transactions.Quantity]*-1,[Transactions.Quantity])) AS Query1

ON Stock.[Lot Number] = Query1.[Lot Number])

INNER JOIN

(SELECT Transactions.[Transaction ID], Transactions.[Lot Number], Transactions.Date, Transactions.[Transaction Type], Transactions.Quantity, Transactions.[Price], IIf([Transaction Type]="Sale",[Transactions.Quantity]*0,[Transactions.Quantity]) AS AdjustedQty, AdjustedQty*Transactions.[Price (THB)] AS TotalAmount FROM Transactions

GROUP BY Transactions.[Transaction ID], Transactions.[Lot Number], Transactions.Date, Transactions.[Transaction Type], Transactions.Quantity, Transactions.[Price], IIf([Transaction Type]="Sale",[Transactions.Quantity]*0,[Transactions.Quantity])) AS Query2

ON Stock.[Lot Number] = Query2.[Lot Number]

GROUP BY Stock.[Lot Number], Stock.Stone, Stock.Shape, Stock.Size, Stock.Cut, Stock.Mins, Stock.[Color];

Maybe its impossible to tell anything from this mess of code but I am hoping its just a syntax issue that I am missing. The problem is the value returned by 'Sum(Query1.AdjQty) AS Stock ' is wrong. I just can't figure out what I am missing.

I have already tried separating these into separate queries and still getting the same results.

Answer 1

Thanks, I think your way might simply things a lot.

Kind of a basic question, how do I change Transaction Type (renamed TransType) field form a text field to the lookup table you suggested.

Thanks

Answer 2

OK, So it looks like you have built your tables pretty well. But there are some issues. First, Date is a reserved word in Access and should not be used as an Object. Also using spaces in object names is not recommended, so your Date field should be named TransDate.

Another thing I would change is to not store Transaction Type (renamed TransType) as a text field. I would have a lookup table like this:

TransTypeID	TransType
-1	Sale
1	Purchase
0	Other

This way you can easily calculate the AdjQty with the expression TransTypeID*Quantity.

To calculate the AvgCost of a Lot use:

SELECT LotNumber, Avg(Quantity*UnitCost) As AvgCost

FROM Transactions

GROUP BY LotNumber

WHERE TransType = 1;

So now you have 2 queries (qryQOH and qryAvgCost). So you join them on LotNumber and you the QOH and the Avg Cost. You can add additional columns by joining on LotNumber.

That is a simplification because you probably want to use a FIFO model and only include the cost of QOH that hasn't been sold. So your query has to figure what lots have already been sold.

Answer 3

The purpose is to process the information in a transactions table to give me various information such as Stock on Hand, Average costs of each item (as the same item can often be bought at different prices, we need to average them to find out the true cost to us).

SELECT [Lot Number], Sum(AdjQty) As Stock

FROM Query1

GROUP BY [Lot Number];

The above code will return the total Stock on hand, after summing all the transactions, the purchases and the sales (in the query1 code I have basically converted all sales to negative numbers so when the purchases and sales are added up, I get the actual stock on hand)

When I remove the whole 'Query2' portion of the code, the results are perfect, so the problem is because of the query2 part, I just can't figure out what it is.

The purpose of the query2 code is a little difficult to explain but I'll try. Its similar to query1 except I convert all sales to 0. So I can average out all our buying prices for each item.

Thanks for all your help.

Answer 4

Can you explain the purpose of this query?

When I encounter a similar problem I try to build my queries little by little to see if I can pinpoint the problem.

So Lot number is in Query1. So what do you get if you run this query?

SELECT [Lot Number], Sum(AdjQty) As Stock

FROM Query1

GROUP BY [Lot Number];

Answer 5

Hi

Thank you for posting your query. My name is Clarence, I'll be more than happy to assist you with your concern.

To better assist you with may I know what version of SQL Server are you using?

Kindly check the link I provided below for the article with issue same as yours and solution is provided.

https://answers.microsoft.com/en-us/msoffice/forum/all/sum-value-in-query-returning-very-wrong/7c0e48c0-8a63-49b9-b586-c2d25142379e

Do not hesitate to message us if you need further assistance.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Clarence