UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Start Fifo Pricing, Access 2013    
 
   
bradyman97
post Nov 28 2017, 07:24 PM
Post#1



Posts: 9
Joined: 3-November 17



Trying to figure out how to use FIFO for my database. I need every debit transaction to have a FIFO pricing (No weight avg).

I have a TransactionCD2 table with the following fields (TransactionID (PK), TransactionDate, ProductID, ProductName, Credit, Debit, UnitPrice)

Then I created a query called qryRunningBalance2 with the following fields (ItemID, ProductID, ProductName, TransactionDate, Credit, Debit, Balance)

The qryRunningBalance2 has the following SQL

SELECT T1.[ItemID], T1.ProductID, T1.ProductName, T1.TransactionDate, T1.Credit, T1.Debit, SUM(T2.Credit-T2.Debit) AS Balance
FROM TransactionCD2 AS T1 INNER JOIN TransactionCD2 AS T2 ON (T2.TransactionID<=T1.TransactionID Or T2.TransactionDate<>T1.TransactionDate) AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.[ItemID]=T1.[ItemID])
GROUP BY T1.[ItemID], T1.ProductID, T1.ProductName, T1.TransactionDate, T1.TransactionID, T1.Credit, T1.Debit
ORDER BY T1.[ItemID], T1.TransactionDate DESC , T1.TransactionID DESC;
Go to the top of the page
 
Doug Steele
post Nov 28 2017, 10:10 PM
Post#2


UtterAccess VIP
Posts: 21,495
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Did you have a question?

I'm wondering about the first part of the ON clause for your join. (T2.TransactionID<=T1.TransactionID Or T2.TransactionDate<>T1.TransactionDate). The fact that you're using OR seems a little unusual. Perhaps you can explain what it is you're trying to accomplish.

--------------------
Go to the top of the page
 
theDBguy
post Nov 28 2017, 10:18 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,228
Joined: 19-June 07
From: SunnySandyEggo


Hi bradyman,

Welcome to UtterAccess!
welcome2UA.gif

It might also help if you could post a sample data table.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
bradyman97
post Nov 29 2017, 06:41 AM
Post#4



Posts: 9
Joined: 3-November 17



My question is: How can I get the Ext Amount to calculate correctly because of the different unit price

CODE
TransactionDate    ProductID       ProductName        Credit    Debit     Unit Price      Balance     Ext Amount
3/13/2017          2150-0001       APPLESAUCE             37        0          24.73           37            
3/20/2017          2140-0001       APPLES                 52        0          27.37           52
3/27/2017          2140-0001       APPLES                 43        0          30.10           43
3/27/2017          2150-0001       APPLESAUCE             48        0          26.32           48
3/29/2017          2140-0001       APPLES                  0      103           0.00            8
3/29/2017          2150-0001       APPLESAUCE              0       84           0.00            1

Doug,
I'm not sure why. I watched a Youtube video and change to my info and it worked for my running balance
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 09:30 PM