Hi Duane,
Thanks for the advice. I've been out of office for a couple of days and am just now getting back to this. MaxID is a bigint field in an SQL Server database according to the documentation however, when I first linked this field, Access didn't handle bigint datatypes so I have it linked as a view and I'm not sure how it is handled. It looks like text to me because it is aligned to the left in the column.
I have edited the SQL per your suggestions so you can sleep better. Here it is now, however the results are the same.
SELECT [Transaction History].MAXID, [Transaction History].PRTNUM_15 AS PartID, [Part Master].PMDES1_01, [Transaction History].TNXDTE_15 AS TDate, [Transaction History].TNXCDE_15, [Part Master].COST_01, [Transaction History].TNXQTY_15, DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "'and TNXCDE_15 = '" & "R" & "' and MAXID >= '" & [MAXID] & "'") AS RunTotal, [Part Master].onhand_01, IIf(Nz((Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MaxID] & "'")-[onhand_01]),0)),0)<=0,True,False) AS D, Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MAXID] & "'")-[onhand_01]),0) AS LeftSum, IIf([RunTotal]<[Onhand_01],[TNXQTY_15],Abs((Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "' and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [MaxID] & "'")-[onhand_01]),0)))) AS AgeQty
FROM [Part Master] INNER JOIN [Transaction History] ON [Part Master].PRTNUM_01 = [Transaction History].PRTNUM_15
WHERE ((([Transaction History].PRTNUM_15)<"1001020") AND (([Transaction History].TNXCDE_15)="R"))
ORDER BY [Transaction History].TNXDTE_15 DESC;
I'm not sure what you mean by "Every time I read about a specification for "buckets" I create a table of "buckets" with MinBucketValue, MaxBucketValue, and BucketTitle". I'm open to changing my approach if I can get it to work.
Thanks for your help!
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
A couple suggestions:
Every time I read about a specification for "buckets" I create a table of "buckets" with MinBucketValue, MaxBucketValue, and BucketTitle.
Is MAXID numeric? You are using a numeric operator ">=" and yet comparing it with a string.
You are calculating LeftSum in the query and also using LeftSum in other expression. I never do this. I would repeat the LeftSum expression/calculation in place of using LeftSum elsewhere.
You do the same thing with SeqID. Why not use [Transaction History].MAXID?
I would always make sure there is a space prior to the "and". You have ..."'and TNXCDE_15...
This might or might not resolve your issue but I would sleep better.
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 4, 2019 2:54 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Inventory Aging
Hello Pros,
I have been ask to come up with a way to "age" our inventory into buckets based on receipt date using FIFO. "Current" inventory has been received in the last 29 days. Inventory received between 30 and 59 days ago would go into the "30 day" bucket and etc. I have approached doing this by going through the receipt transactions in our Transaction History table in descending date order and comparing the transaction Qty to the onhand qty. The following SQL is almost getting me what I want. It works fine as long as there are more than 1 receipt for a part. If it is a part that has been purchased and received only once, the AgeQty calculation fails and returns 0. Here are the fields in the query and a brief description of each:
SeqID is an alias for MaxID which is the primary key in the transaction history table. It is autonumber.
TDate - Transaction Date (Receipt Date)
TNXCDE_15 - Transaction Code. "R" is an inventory receipt
Cost_01 - The cost of the part. Used to value the inventory.
TNXQTY_15 - The quantity of the part being received.
RunTotal - The sum of the parts received starting with the most recent receipt in descending date order
Onhand_01 - The total number of parts we currently have onhand
D - a Boolean as to whether this receipt should be included in the aging.
LeftSum - How many are left to allocate
AgeQty - how many are in this age bucket.
Part Master is the table with the parts records
Transaction History is the table with the receipt records.
Here is the SQL for the query:
SELECT [Transaction History].MAXID AS SeqID, [Transaction History].PRTNUM_15 AS PartID, [Part Master].PMDES1_01, [Transaction History].TNXDTE_15 AS TDate, [Transaction History].TNXCDE_15, [Part Master].COST_01, [Transaction History].TNXQTY_15, DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "'and TNXCDE_15 = '" & "R" & "' and MAXID >= '" & [SeqID] & "'") AS RunTotal, [Part Master].onhand_01, IIf(Nz([leftSum],0)<=0,True,False) AS D, Nz((DSum("tnxqty_15","Transaction History","PrtNum_15='" & [PartID] & "'and TNXCDE_15 = '" & "R" & "' and MAXID > '" & [SeqID] & "'")-[onhand_01]),0) AS LeftSum, IIf([RunTotal]<[Onhand_01],[TNXQTY_15],Abs([Leftsum])) AS AgeQty
FROM [Part Master] INNER JOIN [Transaction History] ON [Part Master].PRTNUM_01 = [Transaction History].PRTNUM_15
WHERE ((([Transaction History].TNXCDE_15)="R"))
ORDER BY [Transaction History].PRTNUM_15, [Transaction History].TNXDTE_15 DESC;
The query results all look good except for the LeftSum calculation and the AgeQty calculation and they are correct for all parts that have more than one receipt. If a part has only been received once, the calculations show zero.
How can I get this query to correctly age all receipts? Is this the best approach to my request? I adapted this approach from this post I found on Experts Exchange. https://www.experts-exchange.com/questions/27920676/Inventory-Aging.html
Doyce
Tidak ada komentar:
Posting Komentar