Calculating Amount On Hand

[Yam84]

Hello,

I am having some trouble using a calculated field to accurately determine the amount on hand. I have a UNION query which joins two queries.

The first query uses the amount of stock of an item joined with the items that have been checked out. I use these numbers to calculate the amount on hand.

SELECT [AcqDetailQuery].SumOfstockQuantity, (([AcqDetailQuery].[SumOfstockQuantity])+([Expr1])) AS AOH, IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])) AS Expr1, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID
FROM AcqDetailQuery INNER JOIN (PurchaseType INNER JOIN CheckedOutQry ON PurchaseType.purchasetypeID = CheckedOutQry.purchasetypeID) ON [AcqDetailQuery].productID = CheckedOutQry.productID
GROUP BY [AcqDetailQuery].SumOfstockQuantity, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID;

the second query uses the amount of stock of an item joined with the items that have been checked in. I use these numbers to calculate the amount on hand.
SELECT [AcqDetailQuery].SumOfstockQuantity, (([AcqDetailQuery].[SumOfstockQuantity])+([Expr1])) AS AOH, IIf([PurchaseType].[purchasetypeID]<=3,[CheckInQry]![qtycheckedIn]*-1,([CheckInQry]![qtycheckedIn])) AS Expr1, PurchaseType.purchasetypeID, CheckInQry.productID, CheckInQry.qtycheckedin
FROM (PurchaseType INNER JOIN CheckInQry ON PurchaseType.purchasetypeID = CheckInQry.purchasetypeID) INNER JOIN AcqDetailQuery ON CheckInQry.productID = [AcqDetailQuery].productID
GROUP BY [AcqDetailQuery].SumOfstockQuantity, PurchaseType.purchasetypeID, CheckInQry.productID, CheckInQry.qtycheckedin;

After I union these queries, this is my result:
Query3
productID/ SumOfstockQuantity/ AOH/ Expr1/ qtycheckedout/ purchasetypeID/
321/ 10/ 8/ -2/ 2 1/
321/ 10/ 8/ -2/ 2/ 2/
96/ 8/ 7/ -1/ 1/ 2/
96/ 8/ 6/ -2/ 2/ 3/
385/ 4/ 3/ -1/ 1/ 3/
96/ 8/ 9/ 1/ 1/ 4/
88/ 7/ 8/ 1/ 1/ 4/

As you can see, the AOH is incorrect. Beyond the first instance of the item being checked out, the AOH is calculated based on the initial amount of stock. I would like for it to subtract from the sumof stock first, and beyond that, add or subtract from the current AOH. Is there anyway to accomplish this?

Thank you in Advance for you assistance

 

[lespaul]

you can't refer to an alias within the same section of the query:

SELECT [AcqDetailQuery].SumOfstockQuantity, (([AcqDetailQuery].[SumOfstockQuantity])+[b]([Expr1])[/b]) AS AOH, IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])) AS Expr1, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID
FROM AcqDetailQuery INNER JOIN (PurchaseType INNER JOIN CheckedOutQry ON PurchaseType.purchasetypeID = CheckedOutQry.purchasetypeID) ON [AcqDetailQuery].productID = CheckedOutQry.productID
GROUP BY [AcqDetailQuery].SumOfstockQuantity, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID;

you have to write the entire SQL that makes Expr1:

SELECT [AcqDetailQuery].SumOfstockQuantity, (([AcqDetailQuery].[SumOfstockQuantity])+[b](IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])))[/b]) AS AOH, IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])) AS Expr1, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID
FROM AcqDetailQuery INNER JOIN (PurchaseType INNER JOIN CheckedOutQry ON PurchaseType.purchasetypeID = CheckedOutQry.purchasetypeID) ON [AcqDetailQuery].productID = CheckedOutQry.productID
GROUP BY [AcqDetailQuery].SumOfstockQuantity, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID;

Leslie

Have you met Hardy Heron?

 

[Yam84]

Leslie,

Thank you for responding. I tried your code, however it produced the same results mine did.



SELECT [AcqDetailQuery].SumOfstockQuantity, (([AcqDetailQuery].[SumOfstockQuantity])+(IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])))) AS AOH, IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])) AS Expr1, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID
FROM AcqDetailQuery INNER JOIN (PurchaseType INNER JOIN CheckedOutQry ON PurchaseType.purchasetypeID = CheckedOutQry.purchasetypeID) ON [AcqDetailQuery].productID = CheckedOutQry.productID
GROUP BY [AcqDetailQuery].SumOfstockQuantity, CheckedOutQry.productID, CheckedOutQry.qtycheckedout, PurchaseType.purchasetypeID;

Produces:

qtyout Expr1 SumQty AOH Item(s) purchtypeID
1 -1 4 3 1/2 Finished Hex Nut 3
1 -1 8 7 "1 1/2"" Putty Knife" 2
2 -2 8 6 "1 1/2"" Putty Knife" 3
2 -2 10 8 Safety Glasses 1
2 -2 10 8 Safety Glasses 2

Which is still incorrect for the AOH. For instance the second record for Putty Knife should read to reflect the correct AOH:
qtyout Expr1 SumQty AOH Item(s) purchtypeID
1 -1 8 7 "1 1/2"" Putty Knife" 2
>>2 -2 8 5 "1 1/2"" Putty Knife" 3<<

Its looks like it does not subtract from the New Quantity on Hand (7) after the first transaction takes place, which it should (7 - 2 = 5).

 

[lespaul]

first it's hard to tell what should be where for the columns. If you surround that in [ignore][tt] and [/tt] [/ignore] tags you can line the information up so that it's easier to read:
[tt]
qtyout Expr1 SumQty AOH Item(s) purchtypeID
1 -1 4 3 1/2 Finished Hex Nut 3
1 -1 8 7 1 1/2"" Putty Knife" 2
2 -2 8 6 1 1/2"" Putty Knife" 3
2 -2 10 8 Safety Glasses 1
2 -2 10 8 Safety Glasses 2[/tt]

(there are other tags [ignore]

, [b] to bold, [i] for italic,[/ignore] etc..check out the Process TGML link below for the whole list)

So your issue is that if you want those two records combined you need to leave out the qtyout field from your select because you are grouping by that field.

May be easier for you to post some of your raw data and expected results.  You may be able to get what you want in a single query depending on your table structure.


Leslie

[url=http://www.ubuntu.com/products/whatisubuntu/804features/]Have you met Hardy Heron?[/url]

 

[Yam84]

OK, thank you for the tip,it looks like when i use the [tt] and [/tt] tags, then preview the message, I do not see the tabs separating the data...I tried to use [tab] instead.

I am unsure whether or not I should include the qty out in the result set. I thought the qty was important, as it is being used to calculate the AOH.

When you say 'leave out', do you mean take it out of the query or have it on the query but unchecked? I ask because when i take it out i get an error saying You tried to execute a query that does not include the specified expression "])+(IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut]))))". If i simply uncheck it, I get the same results, minus the column showing because I chose not to have it displayed.

Raw Data (Comes from transaction table):

OutInID [tab]RecDt[tab]DueDt[tab][tab]Requester[tab][tab]Item(s)[tab]Qty[tab]purchID

86 [tab]5/22/2008[tab]5/23/2008[tab][tab]Thomas Johnson[tab][tab]Hammer[tab]1[tab]4

87 [tab]5/7/2008[tab]6/2/2008[tab][tab]Thomas Johnson[tab][tab]Putty Knife[tab]1[tab]4

97 [tab]5/30/2008[tab]5/31/2008[tab][tab]john x[tab][tab]1/2 Finished Hex Nut[tab]1[tab]3

98 [tab]6/2/2008[tab]6/3/2008[tab][tab]Sergio Rangel[tab][tab]Putty Knife[tab]2[tab]3

99 [tab]6/2/2008[tab]6/3/2008[tab][tab]Alex Cruz[tab][tab]Putty Knife[tab]1[tab]2

I would like to see displayed:

qtyout[tab]expr1[tab]sumqty[tab][tab]AOH[tab]Items[tab][tab]PurchType

1[tab][tab]-1[tab][tab]4[tab][tab]3[tab][tab]Hex Nut[tab][tab]3
1[tab][tab]-1[tab][tab]8[tab][tab]7[tab][tab]Putty Knife[tab][tab]2
1[tab][tab]-2[tab][tab]8[tab][tab]5[tab][tab]Putty Knife[tab][tab]3

If you look at the last two records, the AOH in the second record is derived by subtracting expr1 (-2) from the AOH of the previous record (7), leaving me w/ the accurate AOH.
I am not grouping by item because of the purchase type differences. Purchase types 1-3 result in deductions from stock, while purchtype 4 is an addition to stock.

 

[lespaul]

well it looks like some of your tags are working in the first paragraph. Your [tt]and[/tt] looks like you used the [ignore][tt] and [/tt][/ignore].....

Still not sure how you are "calculating" things...in this latest example you have 2 records for the Putty Knife? and in your results you don't have the 1 1/2" Hex Nut that's in your sample data?

so if this is your raw data:
[tt]
OutInID RecDt DueDt Requester Item(s) Qty purchID
86 5/22/2008 5/23/2008 Thomas Johnson Hammer 1 4
87 5/7/2008 6/2/2008 Thomas Johnson Putty Knife 1 4
97 5/30/2008 5/31/2008 john x 1/2 Finished Hex Nut 1 3
98 6/2/2008 6/3/2008 Sergio Rangel Putty Knife 2 3
99 6/2/2008 6/3/2008 Alex Cruz Putty Knife 1 2[/tt]

you want a single record for each item, right?
[tt]
qtyout expr1 sumqty AOH Items PurchType
Putty Knife
1/2 Finished Hex Nut
Hammer
[/tt]

now using the sample data above, what would you need in the other fields? I find it impossible to get 8 for the sumqty of Putty Knifes with the data you showed above....

Leslie

Have you met Hardy Heron?

 

[Yam84]

No, I do not want a single record for every item because every item has a purchase type on it. They transactions are distinguished by purchase types, because they are different circumstances under which stock is removed or added. So i would need to have a record for each time an item is moved in or out. Items are moved out for purchase types 1-3, 1-Purchased Item, 2-Credit future purchase, 3-Check out. items are moved in for purchase type 4-Return checked out item. So as you can see, the AOH will be determined by they purchase type.

In my raw data, I have THREE records for putty knife. the first record was initially a check out, however the way my system is set up, i just edit the purchtype (change from 3 to 4) to checkin when the item is returned. that is what the first instance of the putty knife indicates.
the second instance (98) is where it was checked out
the third instance (99) is when it was purchased.

My result set was not a complete version of the raw data, as it only took into account items that were taken from stock more and more than once, just to show you. But the Hex Nut is in my result set...it's the first entry there. I have not included the query where i sum the amount of stock, i just included the result from the query in the query i posted, as I use the query in conjunction w/ the one listed.

Just in case you wanted to see it:
SELECT Sum(StockTake.stockQuantity) AS SumOfstockQuantity, StockTake.productID
FROM StockTake
GROUP BY StockTake.productID
ORDER BY StockTake.productID;


Anywho, I am calculating using this logic:
Sum of stockqty (calculated n a separate query) - qty taken out = AOH

in the case of the putty knife (what should b calculated and is not):

8 - 1= 7 FIRST ENTRY
7 - 2= 8 SECOND ENTRY

The problem is that with the second entry is using the sum of stockqty to determine the new AOH, when it should use the AOH from the first entry as the sum of stockqty.