Results 1 to 2 of 2
  1. #1
    QuietPenguin is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    12

    Having issues with how to incorporate sample FIFO operations into my DB

    Hi All,

    I've spent a lot of time researching how to incorporate a FIFO (First In First Out) element to my current Database. I buy trading cards and sell them via auction, and have the following relevant tables (I have omitted any irrelevant fields):

    Cards
    CardID / CardName / Expansion / Descriptor

    CardPurchases


    PurchaseID / CardID / CardQTYBought / CardPricePerUnit / Notes / DatePurchased

    Auctions
    AuctionID / DateAuctioned / QTYSold / CardID / SoldFor / Paid / Sent

    CardID is my primary relationship that links everything together, though I have other relationships for customers, etc that I have omitted. I understand that it is good design to have only raw data in your tables, and to use queries and such to calculate anything else. As such I am looking for a way to calculate how much profit I made from any individual auction by linking an auction up with the correct entry in the CardPurchases table. For example if I sold 4 copies of the 'Ace of Spades' on the 20/4/19 for $40 I would have the following record:

    Auctions
    1 / 20/4/19 / 4 / Ace of Spades / $40 / Yes / Yes

    In the CardPurchases table I have entries that detail when and how many Ace of Spades I have bought (and who I bought them from):

    CardPurchases
    1 / Ace of Spades / 1 / $5 / Rob / 13/3/19
    2 / Ace of Spades / 2 / $4.50 / Steve / 14/3/19
    3 / Ace of Spades / 5 / $6 / Jacob / 15/3/19

    I am looking for a way to generate a report that can link the Auction sale entry (where I sold 4 copies) with the oldest entry in the CardPurchases table (13/3/19) and then because I only bought 1 copy in that record it would also need to allocate 2 copies from the 2nd entry, and the final copy from the third entry (and note that there are only 4 remaining copies left in entry #3 now). I also need it to show how much profit was made - in this case it's $40-($5+$4.50+$6*2) = $18.50.

    Once I have the ability to link the Auctions and CardPurchases together I assume I will be able to easily generate monthly profits, or profit reports based on all instances of a specific card being bought and sold.

    Thankfully I have been able to source a sample database that is able to recreate what I am looking for, however I have been unable to successfully reverse engineer how it works. Link here:

    https://access-programmers.co.uk/for...d.php?t=153802

    I am able to use a DSum equation to calculate a running total for both purchase and sales, but I am at a loss at what I should be doing next. Which query is the one I should be working on replicating first? I sadly do not understand what I should be looking at next.

    Any help in this endeavour would be greatly appreciated!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I haven't done any work with FIFO recently, but I did participate in some threads in other forums.
    I did see the link you referenced previously.

    Here are some links that may be helpful.

    https://access-programmers.co.uk/for...highlight=fifo
    https://access-programmers.co.uk/for...fo#post1402774
    https://access-programmers.co.uk/for...d.php?t=267564
    https://www.accessforums.net/showthr...highlight=fifo

    There are some links to youtube videos re FIFO at this link.

    Review the similar threads at the bottom of this page.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. FIFO Inventory
    By Analogkid in forum Access
    Replies: 21
    Last Post: 12-15-2016, 03:35 PM
  2. Incorporate UI into a Query??
    By nick404 in forum Queries
    Replies: 6
    Last Post: 06-07-2015, 09:08 PM
  3. I want incorporate a Floorplan into my form
    By ZachAtaiyan in forum Forms
    Replies: 1
    Last Post: 07-17-2014, 03:23 PM
  4. inventory fifo db
    By betna in forum Access
    Replies: 2
    Last Post: 04-18-2013, 03:37 PM
  5. Fifo
    By Firefighter22 in forum Access
    Replies: 1
    Last Post: 08-29-2011, 01:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums