473,581 Members | 2,761 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

When inventory is not yet sold

43 New Member

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


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_InventoryTr ansactions.

PK_Sale ID (auto#)

PK_Order ID (auto#)

TBL_InventoryTr ansactions
PK_InventoryID (auto#)
Item Description

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_InventoryTr ansactions. 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.

Jan 5 '22 #1
7 15097
32,566 Recognized Expert Moderator MVP
It seems as if your Inventory data is being forced into restrictions that don't necessarily pertain. Essentially TBL_InventoryTr ansactions 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.
Jan 5 '22 #2
43 New Member
As always Neopa, thanks for your help.

Are you suggesting to create 2 MTM Juntions, one between TBL_InventoryTr ansactions & TBL_Sales as well as one between TBL_InventoryTr ansactions & 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.
Jan 5 '22 #3
32,566 Recognized Expert Moderator MVP
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.
Jan 6 '22 #4
43 New Member
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...
Jan 6 '22 #5
43 New Member
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_InventoryTr ansactions.
Jan 13 '22 #6
32,566 Recognized Expert Moderator MVP
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.
Jan 14 '22 #7
43 New Member
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.
Jan 14 '22 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

by: simon | last post by:
Hi I know this has been done to death....but obviously at some point in the dim and distant past! I wish to program a retail database for my shop, I have trawled many forums and I only manage to see "search for inventory, this has been done many times before...", without ever actually coming across the method ;( Could some kind person...
by: Frank Ruffolo | last post by:
i've got a basic order entry d-base w/ a form for adding products, etc. can anyone help me w/ inventory control? i want to be able to not only enter a new product but also enter the number of units. then once a sale is made on a given item it subtracts the number of units sold from the inventory. is this a big task. help
by: angel duran | last post by:
hi... i have a database and i wan to accomplish something like this. productID store1_in store1_out store1_total store2_in store2_out store2_total 1 2 1 1 4 2 2 2 3 1 2 8 3 5 .... .... .... 2173 10 ...
by: Rolan | last post by:
I know what I want to do regarding the average cost for inventory, but need some assistance to sort out some of the details to finalize an inventory table and query. Essentially, the information is needed for year end reporting and not for on-going review. Below is an example that summarizes it all. tblMain/frmMain /IDTag...
by: DS | last post by:
Hi, I'm building an Inventory form. I have a Product Form and ono that form I want to place a sub-form showing the sold products along with the Products that were received. I have a calculated field to show me what my current inventory is. However, I want to only use one Sub-Form as oppossed to two. When I try to set up a query using the...
by: Stella Pieters via AccessMonster.com | last post by:
Let me start by saying that I'm a newbie in VBA. I've created an application in access where the user enters the quantity of the items sold in a form. I would like to have these records created in a table, mentioning the qty sold, the transaction date etc. this table I'm planning to use afterward to calculate the quantity on hand. I have...
by: ChriS99 | last post by:
Hello people, i am Chris from Jamaica W.I. and i have been having a problem with write vector and stack programs (but mostly vectors). Anyway, the program i am writing involves the whole pushing and popping theory. I have that in check. My problem is that i dont kno how to set the width and set precision between the fields such as ID No#, Date,...
by: pkfloyd | last post by:
I am looking to create a database which will keep track of a warehouse inventory as well as inventory on sales trucks and track customer sales. Want to link tables so that totals are linked when inventory is transferred to trucks the warehouse inventory reflects this transfer. Also truck inventory to reflect custormer sales. Any suggestions as...
by: Bigdaddrock | last post by:
I have a database similar to Northwind Traders. I need to reduce the INVENTORY Field of my PRODUCT Table daily, by the number of Products sold each day. (In essence I am keeping a running inventory - which my client requires.) I have two TABLES called PRODUCT and ITEMS: The PRODUCT Table contains fields called PRODUCT ID, INVENTORY and...
by: Jason Duong | last post by:
Dim Inventory() As String = IO.File.ReadAllLines("inventory.txt") Dim query = From line In Inventory Let data = line.Split(","c) Let name = data(0) Let cost = FormatCurrency(data(1)) Let quantitysold = data(2) Let EndInv = 45 - data(2) Let totalCostperproduct = FormatCurrency(data(1) * data(2)) Select name, cost, quantitysold,...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.