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    
post Nov 28 2017, 07:24 PM

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

UtterAccess VIP
Posts: 21,818
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.

Doug Steele, Microsoft Access MVP (2000-2018)
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
post Nov 28 2017, 10:18 PM

Access Wiki and Forums Moderator
Posts: 72,770
Joined: 19-June 07
From: SunnySandyEggo

Hi bradyman,

Welcome to UtterAccess!

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
Access Website | Access Blog | Email
Go to the top of the page
post Nov 29 2017, 06:41 AM

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

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

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    22nd July 2018 - 10:48 PM