When inventory is not yet sold

[cmo187265]

Intro:

I am still (painfully) trying to create a sportscard database. I have other questions related to this endeavor in other threads in this forum.

Background:

I need to database to be able to store Order data (items I buy) and Sales data (items I sell).
One Order can later be part of many Sales (i.e. many items can be bought in one Order transaction and be split up later into multiple Sale transactions. The same is true for sales - One Sale transaction can be related to multiple Order transactions. This leads me to create a many to many junction table labeled TBL_InventoryTransactions.

TBL_Sales
PK_Sale ID (auto#)
SaleDate
SaleAmount

TBL_Orders
PK_Order ID (auto#)
OrderDate
OrderAmount

TBL_InventoryTransactions
PK_InventoryID (auto#)
Item Description
FK_OrderID
FK_SaleID

Help:
Access requires that a Sale ID record exist in the TBL_Sales as part of the many to many relation when entering data into the TBL_InventoryTransactions. How can this be handled since there will be times when Orders and subsequently Inventory do not have a corresponding Sale transaction yet?

As always, thank you for all your help for anyone who wishes to jump in. I will add an image or the relation after submitting as I cannot easily find where the manage attachments button is located.

[NeoPa]

It seems as if your Inventory data is being forced into restrictions that don't necessarily pertain. Essentially TBL_InventoryTransactions is both a container for stock held as well as a M2M linking these with sales & orders. As you've just told us this is not reliably true, I suggest you need a table to handle your inventory as well as two M2M tables - one each to handle the sales v inventory relationship and the order v inventory relationship. Only records that represent actual relationships would need to be created. IE. You could have inventory records with links to sales &/or orders or you could have some without either.

It's hard to know if that matches your requirement very closely but certainly the idea of mandatory links when none exist - simply in order to represent stock that does exist - is not a design that can work for what you say you want.

[cmo187265]

As always Neopa, thanks for your help.

Are you suggesting to create 2 MTM Juntions, one between TBL_InventoryTransactions & TBL_Sales as well as one between TBL_InventoryTransactions & TBL_Orders?

I don't have Inventory as having a MTM with either Order or Sales because each item in inventory is a unique item. A card is appraised/graded, encased by a grader, and then stamped with a unique barcode that doesn't duplicate.

Thanks again.

[NeoPa]

Happy to help where I can :-)

What I'm suggesting really is that you design the structure to match the reality of what you have and are managing. Your explanation doesn't clarify that for me I'm afraid.

For each individual item of inventory :

1. Can it have multiple related sales?
2. Can it have no related sales?
3. Can it have multiple related orders?
4. Can it have no related orders?

I assume that each sale & each order are able to be associated with multiple items of inventory.
The answers to these questions will determine how your tables are designed.

[cmo187265]

Thank you Neopa. Sorry I was not clear perhaps.

One order can contain many inventory items, one sale can also contains many inventory items.

Concerning 1 unique inventory item:
Can it have multiple related sales? - NO
Can it have no related sales? - YES
Can it have multiple related orders? - NO
Can it have no related orders? - NO

So when I enter an order transaction I will simultaneously be entering a unique one of a kind item into inventory. This item is serial numbered as unique and will only ever 1) sit in inventory or 2) be sold.

My original design assumed Sales and Orders needed to be relationed in order to run a query to sum transaction values between to the two to arrive at a profit report.

I would like the ability eventually to be able to query those items that are not sold, by some certain parameters, to give lists to collectors based on certain players or years of card they may be collecting. This may be a topic for another thread but I like to try myself before overthinking or not understanding and ultimately posting to the forum.

Thanks again...I wish I wasn't so slow to grasp this stuff...

[cmo187265]

I believe I found a solution by reading through another person's similar issue on another forum.

It is likely against etiquette to post a link to another forum but the solution is to clear out what Access autopopulates as 0 in the default value property in the field of the TBL_Sales that is located as a FK in the TBL_InventoryTransactions.

[NeoPa]

I swear I replied to this since post #5 but I see no signs of it. It must never have made it to post. Apologies for that. I'll have a look again tomorrow when I have more time. I remember thinking post #5 was well done and included the important information.

Certainly you're right about the etiquette (and explicit rules to that effect as well in fact) so your reply was well done.

[cmo187265]

No problem NeoPa. I can assure you that I continue to chase rabbits that are at least initially relevant to my questions while waiting for replies so I'm no just waiting idly.

Makes sense about external links so that the body of knowledge can grow within the forum.

Look forward to any feedback on my posts #5 or #6 when you have time.

Thanks as always.