Relative Novice - Access Tracking

[ArmandKruger]

Hi

Relative novice (been working in access for ages but never really done any coding or actual complex databases). I need help in the design of a database which will be used to keep track of inventory.

I work for a manufacturer which has a structure which we call a BUS and is made up of smaller components (lets call them cages)

BUS
Cage 01


Cage 02
Cage 03

Each cage is also comprised of different components

Cage 01
Component A
Component B
Component D
Component E

Cage 02
Component A
Component C
Component F
Component G

Cage 03
Component B
Component C
Component D
Component F

This is with 3 Cages and 8 Components where I actually have 13 "Cages" and over 2200 unique "Components" and almost 100 BUS's on order (with the idea to just add more BUS's as the orders come in.

We receive shipments per 3 BUS's at a time.

I have to create a tracking system (in access) where a shipment of component quantities can be captured against ALL the current BUS's on order where over supply of a specific component will overflow onto the Next BUS's supply. Every individual BUS has to have a report that splits it up into cages and states the % received for every BUS as well as display the Components per cage with their quantities and % complete per cage (per BUS).

Please help?

[ItsMe]

It will take a while to build a database and application that will do all of this well. Start by defining your Entities and how these entities interact with your business rules. It sounds like you are attempting to do this now.

I would treat each component and the end result (BUS, Invoice, Quality Control, etc.) as an entity. In general, this means each will have their own table. Each entity will have fields that describe how it is unique. Then, when there will be a many to many relationship, use junction tables to show how one entity relates to another entity. The difference of a many to many vs. a one to many relationship is difficult to explain.

An example of a One to Many might be a Customer that is related to many Orders.

To start determining the definitions of your Many to Many relationships, take a look at this example.
https://www.accessforums.net/databas...tml#post277108

[ArmandKruger]

At this stage it has to only receive the components into the lists. All Invoicing, QC, etc. tracking is covered in an existing system but they all work on the assumption that all components have been received. I've been tasked on creating a 100% independent system that only deals in the receiving of parts and has reporting capability such that the user will be able to identify which Cage of which BUS can go into manufacturing once all the components for that Cage and so on. I have used Many to many relationships before but I struggle to fathom how to capture the receiving bit. If I get 478 Component A's, how will I allow a user to enter the data in a simplified way so that it cascades over the cages and Busses

[ItsMe]

...If I get 478 Component A's, how will I allow a user to enter the data in a simplified way so that it cascades over the cages and Busses

If the table design is correct, updating tables will be accomplished using a simple Action query to append or update records. You can also use code to accomplish append and update actions. The new or updated records will use the Primary Key value of the parent table and place the value in the Child table's Foreign Key field. In the case of a many to many, the Primary Keys of both tables are appended to the junction table.

Your first step is to define your ERD. If you do not need to include QC or Invoicing, that is part of the ERD process. Now you know not to include those within your DB and only include a way to "hand off" or export the data to those entities.