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!