Warehouse Inventory headache

[Slap]

I am having a brain block on how to approach a problem I have with Inventory.

I need to be able to migrate an inventory snapshot built from 2 separate stock records which at their highest level (Item/Lot/Qty) will be the same.

My first step is to create a 'matching' inventory table based on grouped queries to find all matching records (in the knowledge not all records will match), that I have achieved and tested ok.

This is where my problem now surfaces.

Using data from Table 1 which has this information:

ITEM
LOT/SERIAL
QTY
Sub-Inventory

I need to now add the 'locator' (shelf position in the warehouse), which is a sub-set of Sub-Inventory.

Where the stock is a direct match and only 1 sub-inventory exists I have achieved this simply with queries but as soon as I have 2 values in sub-inventory and more than 1 shelf locator position then queries pull duplicate records.

I'm leaning towards VBA using DCount & recordsets to essentially build a new table of records with the data I need, does that sound correct?

Example:
My Stock
ITEM
LOT/SERIAL
QTY
SUB-INVENTORY

A1, ABC1, 10, MAIN
A1, ABC1, 10, DAMAGE

Warehouse Stock
ITEM
LOT/SERIAL
QTY
LOCATOR

A1, ABC1, 3, L10101
A1, ABC1, 7, L10102
A1, ABC1, 4, L10103
A1, ABC1, 6, L10104

Outcome expected
A1, ABC1, 3, MAIN, L10101
A1, ABC1, 7, MAIN, L10102
A1, ABC1, 4, DAMAGE, L10103
A1, ABC1, 6, DAMAGE, L10104

 

[jdraw]

I'm not sure what you have for tables and relationships, but it appears you are missing a Junction Table. You need a means to identify where a Sub_Inventory is Located. This table, which I have called SubInventory_Location contains 2 fields; Sub_Inventory and Locator.

It is structured as

SUB_INVENTORY, LOCATOR
"MAIN", "L10101"
"MAIN", "L10102"
"DAMAGE", "L10103"
"DAMAGE", "L10104"

I have made a composite, unique key on the combination
Sub_Inventory + Locator ( index Unique YES, Primary NO, Ignore Nulls NO) see attached subinventory_location_UniqueIndex.jpg

This will prevent duplicates in this Junction Table.

There is a jpg of the query set up in the query wizard.
And a jpg showing the result from running the query.

I have also added jpgs for just the Junction table and warehouse to show the query and result. Yopu don't need the MyStock table to show the result you asked about. I included it to show how the tables could be related.

 

[Slap]

jdraw, Thanks.

That works just how I'd expect it to What I hadn't really got my head around is that I needed all the sub-inventories and locators mapped in that table before I looked at the migration of the inventory.

I just need to expand the data set to all 30k lines of inventory and 400k locator combinations now

 

[jdraw]

Do you have a data model?
Can you show a jpg of your Tables and relationships?

My answer was based on your specific query and data. There was no "obvious means" of locating a subInventory. There may be other ways of achieving what you want. Others may propose some redesign options, once they know how your tables and relationships are set up.

You may wish to do a little research on Many to Many relationships, and Junction tables.

 

[gemma-the-husky]

slap

just to confirm - in dataset A - you have more than 1 stock record, because stock is held in 2 or more bins/bays

in dataset B - the same

so when you join them you get a cross-product join?


---
one answer is to use queries which accumulate totals for each record set. You lose the bay information, but you avoid the cross-product

ie tableA
500 units in one bay
300 units in another

table B
400 units in 1 bay

----------
instead you get

table A
total 800 units

table B
total 400 units

and no cross-product problem (although you do lose the bay information)