Relationship issue

[DMBLuva]

Hi all,

I'm hoping that you can help ease my very confused mind. While I am good at Access, I'm not very good with relationships and understanding them (no this is not a dating question

I am building a database that tracks jobs given out. We have five types of jobs that can be assigned. I have separated each type into their very own table since each is unique (deliveries, pick ups, Clothing requests, supplies and copy jobs). The main table has the information that is needed for ALL jobs; who requested it, who is being assigned the job, and so forth.

My issue is this, for my clothing table, I want to have a table that is purely for inventory/listing of all the clothing we can give (they are numbered) and it to be selected from the main clothing job table. I have made yet another table that will allow for the quantity of such items that that to be fed into the main clothing job table. I hope I haven't lost you.

Currently I have it like this -> JobID (Main job table not primary key) connected with JobID (number/foreign key) in main Clothing job table.

Then the qtyID (foreign key from main clothing job table) connected to primary key (ID) of clothing quantity table.

Finally, a foreign key in the clothing quantity table is connected to primary key (ID) in Clothing inventory table.

WHEW. Do I need all of these tables? I am trying to ultimately show one main clothing job inside the main job table and inside the main clothing job table each subsequent article of clothing and quantity to that job.

any help would be appreciated. I hope I have not confused anyone.

 

[jedraw]

I think you need a clear picture of what you are trying to do. To answer the question as to whether you need a particular table or not, is something only you can answer.
To understand exactly what you have and what you need, I think you need a data model - does not have to be ultra sophisticated. But enough to let you see the things you are dealing with and the attributes (fields/properties) associated with each.

If you then break down some of your tracking requirements, you'll soon see if some things make sense or not. If they don't make sense, it will help you determine what is missing, or what is not needed.

Here is a reference to a site that may be helpful.

http://www.datamodel.org/NormalizationRules.html#one