Relation or array

[TonC]

Hello,:hi:



Looking for solution.

A warehouse, In that house is
one scaffolding
with 8 shelfs,
and each shelf has 8 products.

Looking for a relationship between tables (and which tables) or a solution for an array. It cost me days to find something about this subject on the internet

Guidance will be welcome
TonC

[Aussiebear]

To effectively manage warehouse storage in an Access database, you'll want to create a relational database structure. Here's how you can set up the tables and relationships:
Tables:


Warehouses:


WarehouseID (Primary Key, AutoNumber): Unique identifier for each warehouse.

WarehouseName (Text): Name of the warehouse.

WarehouseLocation (Text): Address or location of the warehouse.

Other relevant fields (e.g., Capacity, ContactPerson).


StorageRacks:


RackID (Primary Key, AutoNumber): Unique identifier for each storage rack.

WarehouseID (Foreign Key): Links each rack to a specific warehouse.

RackName (Text): Name or identifier for the rack (e.g., "Rack A1").

Other relevant fields (e.g., RackType).


Shelves:


ShelfID (Primary Key, AutoNumber): Unique identifier for each shelf.

RackID (Foreign Key): Links each shelf to a specific storage rack.

ShelfNumber (Integer): Number or identifier for the shelf within the rack (e.g., 1, 2, 3...8).

Other relevant fields (e.g., Capacity).


Products:


ProductID (Primary Key, AutoNumber): Unique identifier for each product.

ProductName (Text): Name of the product.

ProductDescription (Text): Description of the product.

Other relevant fields (e.g., SKU, QuantityInStock, UnitPrice).


StockLocations


StockLocationID (Primary Key, AutoNumber): Unique identifier for each stock location.

ShelfID (Foreign Key): Links each stock location to a specific shelf.

ProductID (Foreign Key): Links each stock location to a specific product.

Quantity (Integer): The quantity of the product at that location.


Relationships:


Warehouse to StorageRacks: One-to-many relationship. One warehouse can have many storage racks.

StorageRacks to Shelves: One-to-many relationship. One storage rack can have many shelves.

Shelves to StockLocations: One-to-many relationship. One shelf can have many stock locations.

Products to StockLocations: One-to-many relationship. One product can be in many stock locations.

Explanation for Access:
In Microsoft Access, you would create these tables and then define relationships between them using the Relationships window. The key is to use Primary Keys (unique identifiers for each record in a table) and Foreign Keys (fields in one table that link to the Primary Key in another table).


The WarehouseID is the Primary Key in the Warehouses table. In the StorageRacks table, it's a Foreign Key, linking each rack to its warehouse.

Similarly, RackID is the Primary Key in StorageRacks and a Foreign Key in Shelves.

ShelfID is the Primary Key in Shelves and a Foreign Key in StockLocations.

ProductID is the Primary Key in Products and a Foreign Key in StockLocations.

This structure allows you to efficiently query and manage your data. For example, you can:


Find all storage racks in a specific warehouse.

List all shelves in a particular rack.

Determine which products are stored on a given shelf.

Track the quantity of each product at each specific location.

[xps350]

As there is only one warehouse and one scaffolding, just the shelves (storage locations) are relevant.
You need (at least) three tables:


StorageLocation
Product
StockMovement.


In the StockMovement table you will fields like:

StorageLocationID
ProductID
Quantity (+ or -)
Date/time.

[TonC]

Hello,:doh:

Sorry, I think i was not clear about my intentions

A warehouse, In that house is
one scaffolding or one rack.
with 8 shelfs,
and each shelf has 8 products, and each of those products, has 8 descri[ptions.

I want to choose with a combo box which shelf I want for example shelf1, shelf2, etc etc
until shelf8.

Sofort I want to use a combo box for the product in the chosen shelf.

And finally, make a selection from my 8 dercriptions in my combobox product.

So, Shelf combobox for Products
Product combobox for Description

Example
Shelf3, Product5, Description4
Or Shelf3, Product5, Description2
Or Shelf3, Product4, Description4
Or Shelf1, Product4, Description2


TonC

[xps350]

I do not understand why a product has 8 descriptions. Can you explain and give examples?

[TonC]

Hello P.

How about chocolate bars,
-milk
-pure
-white
-hazelnut etc etc

Is the relationship I described with comboxes not possible ?

TonC

[xps350]

I understand what you mean. But for me, a milk bar and a hazelnut bar are two different products (so not 1 product with two descriptions). They each have their own characteristics (price, stock, barcode) and are not interchangeable.You could possibly create (lookup) tables for type of product (bar) and taste (white).

[TonC]

Hello P.:dunno
I think we got a difference of opinion about the essens of a choclatbar, which description I could use. But it does not matter. the option you gave me with tables to lookup, Is there a lot of VBA code?

TonC

[Aussiebear]

@TonC, are you sure you are not needing a form of dependant dropdowns rather than a combobox?

[TonC]

Hello Aussiebear,:hi:

Yes, I need a form with possible subforms or, and with dropdown comboboxes. I See the solution is probably difficult te realize. I think the relationships, thats the bottleneck.

TonC

[Aussiebear]

Do a google search on dependant dropdowns and go from there, unless you want to provide a sample workbook with 8 Shelves with each having 8 main products and each product having 8 sub product types.

[Aussiebear]

Failing that then this workbook with multiple examples may give you some insight