Many to Many Relationships and Inventory Levels

[MaryJane]

I have what I think is a unique inventory situation. I have batches of product material that are unique every time but are then turned into different product types. Each batch has unique characteristics and unique pricing that follows on to the product.



I have two tables of information to track inventory: PackagingDetail and OrderDetail. Each have multiple records of the same batch and product type. And I am having trouble finding a way to relate them so I can query to find the inventory level of each batch and product type combination (sum of packaged minus sum of ordered). In the packaging table, there are multiple records for each batch/productID because I want to know the date each was packed. There will be multiple if it was packaged over the course of several days.

So I have two questions:
1. How do I relate and query the two tables since it is a many to many relationship. There is not really anything to line up, I just want the totals so I can see how many packaged, how many sold, and how many available. I'm looking for something like a stacked table where BatchID = BatchID and ProductID = ProductID.
2. I have tried to create an intermediate table, but I run in to the problem of unique IDs because I'm using two fields to find one product type (the batchID and the productID). I have tried creating a calculated field that creates a unique ID from the concatenation of both fields but Access does not allow calculated fields in table relationships.

I feel like I am missing something or going about this the wrong way and I have been unable to find anyone with a similar situation in all the Internets. And so this is my first forum post.

Please halp.

[Homegrownandy]

I'm just about to leave the office. But with a name like that I have to offer some help!

Junction tables are what you need. It seems you have attempted this and been unsuccessful. Could you provide a screenshot of this? I'm sure you will get all the advice you need on here.

[aytee111]

The reason you are struggling is because the table structure is not "normalized". Google it and read what it is. Such as each table contains only one set of data; data is never repeated. You explanation seems to have broken those two rules.

Such as, tables for:
- batches - containing BatchID and batch-specific information (such as pricing)
- products - containing ProductID, BatchID, and product-specific information
- order header - order number, date, etc
- order detail - carrying order number, ProductID, quantity, etc
- packaging - order number, order item, date packed

[MaryJane]

Hi aytee111

I will definitely read in to normalized table structure, because I am aware of that but I didn't think I was doing it wrong here. The only data that is repeating are the ProductID and BatchID on the two tables. I could combine them in to one ProductID/BatchID but the easiest way to do this is with a calculated field, which Access doesn't support for table relationships.

[aytee111]

I am trying to get you to separate the data, not combine it! Your joining will then become a simple matter.

e.g. "sum of packaged minus sum of ordered" - packaged quantity would be on packaged table, ordered would be on order items table, joined by product ID, optionally order (item) number, grouped by product ID with quantities summed.

[MaryJane]

So I can't just join on Product ID because Product IDs are not unique. Let me be more specific. I'm talking about weed here (legal recreational weed mind you). But my product IDs are half gram pre-rolls, whole gram pre-rolls, half pound flower, whole pound flower, but they are NOT all the same. They could be different strains (like Blueberry Cookies or Blue Dream) but not all Blueberry Cookies are the same, each batch is different. A batch harvested last month might have a THC of 17.56% and a batch harvested this month may have a THC of 15.89%. We don't sell those as the same product because high THC is more desirable to retail shops. So my sales manager needs to be able to see something like the following:

Batch ID | Product ID | THC | Available Units
BBC.4-20 | FLW-HP | 17.56 | 5
BBC.4-20 | PRE-HG | 17.56 | 102
BBC.5-1 | FLW-HP | 15.89 | 7
BBC.5-1 | PRE-HG | 15.89 | 90

I do have a batch table with batch info that is not getting repeated like what bloom room it was in, its THC, its CBD, etc. But that's easy to join in because there is one record for each batch ID.

[aytee111]

Type is Blueberry Cookie - TYPES table
Product is half gram pre-roll - PRODUCTS table
Batch is TypeID, ProductID, date harvested, THC, units harvested - BATCHES table

Type Name | Date Harvested | Product name | THC | Units
BBC | 4-20 | half gram pre-roll | 17.56 | 5

[MaryJane]

I appreciate your help but I don't know what to do with this. If I split up all the information in to multiple fields, I still have to include all that info on in the OrderDetail table AND the PackageDetail table because I need to know exactly what was packaged and exactly what was sold.

[aytee111]

Why don't you post your database with some data and we can help you make some changes? You really need to read about normalization!

[Homegrownandy]

don't worry at all how to display information at this point. if the relationships are correct you can show whatever you like.

[ssanfu]

PMFJI,

What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html

The Normal Forms
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form.
The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html


Also, you might take the time to actually work through these tutorials....
http://www.rogersaccesslibrary.com/forum/forum46.html




Good luck with your project.....