Same Table used in Mutliple Fields in another Table - Issues

[JasonTruman]

Hey all

Still trying to get my head around this one, so easier if I explain what I'm trying to achieve

I am setting up a mailing fulfillment database, for which there will be a maxmium of 6 inserts in one mailing for which one table will be the actual jobs table itself called tblJobs. The inserts will all be stored in one table called tblInserts, yet if I create 6 individual fields eg. Insert1, Insert2 etc in the tblJobs table and link each of these fields to the single tblInserts table Access doesn't really like it

What is the best way around this? 6 tables of the same inserts data or another suggestion?

Any help would be appreciated

Thanks

Jason

 

[ChrisM]

If I'm understanding correctly, your tbjJobs shouldn't have ANY InsertX fields in it. Your tblInserts should have a JobID column to link the two tables together. Then you just start adding rows to the Inserts table, if a job has 5 inserts then you add 5 rows, all with the same JobID.

Then when it's time to query the data, link the two tables together by JobID.

 

[JasonTruman]

Hi Chris

The inserts is where we are supplying items/materials ie. the type of envelope (of which there will be many and could be more than one type), paper (again there will be many), plastic polywrap (again all sizes), so I wanted all of these in one table of "inserts", so when a job comes in I can add to this job that a C4 envelope is required, along with a C5 envelope, a letter designed using A4 paper etc. I know I could have an envelope field with an envelopes table, but more than one type of envelope is sometimes used

In my "Access" head of relationships, 1 job can have many inserts and it is the job that has the inserts attached to it not the other way round, therefore my tblInserts table doesn't have the job attached to it with a JobID field - the JobID record has up to 6 inserts where I want to specify what insert is required on that single job

Cheers

Jason

 

[ChrisM]

Ok, I was thinking tblInserts contained unique inserts for each job, but you're saying the table really just contains generic inserts that can be assigned to any job. In that case, you need a third table, with just two columns, InsertID and JobID. Any insert you do for a job gets put into this new third table. This gives you a one to many relationship. One job can have many inserts related to it.

 

[JasonTruman]

Thanks Chris

I thought that's what it was going to end up being and I have never, in all my 19 years of using Access, got my head around the concept of a 3rd table, especially when it comes to a query whereby I want to see the job and the "up to 6" inserts on that job. Would I not see 6 records

In all fairness, I should go and create a simple 3 table database and get my head around it and the example I will do is that there could be up to 6 employees attending a job interview. A table of interviews, a table of employees and a table of interview employee attendees and see it I can crack it

Thanks very much for help

Jason

 

[JasonTruman]

I have done that now and when running a query I do get 6 records even though it's the same job - I only really want one record for the job, so I am thinking it will have to 6 separate tables each containing the same information. I just know I will have more problems in the long run when it comes to queries that look for the same item that could be in any of the 6 fields, meaning multi OR queries

 

[ChrisM]

The best way to think it through is to use a business sales order. That sales order is composed of part numbers that are linked to an Item_Master part number table (just like your tblInserts). A sales order can have any number of part numbers attached to it. The sales order has some header information (date, customer, order type, etc). This is all stored in the Sales_Order_Header table. Then you have a Sales_Order_Detail table (the "3rd" table) that stores one row per part number in the order. This is the way ERP systems work. In fact, the sample table names I just listed come out of JDE. SAP works the same way. If you pull up a sales order in the SAP client, you will literally see the header information at the top of the screen, then you'll see a table-like grid that contains all the part numbers. This grid is the result of querying the 3rd table for a particular order number.

Your report queries will have to work the same way. One query to get the header information, then a second query to get the detail information. As you correctly noted, if you directly link the two tables you will get six rows back with a lot of duplicate information.

 

[JasonTruman]

I'll give it a good go - thanks again for the advice :D