Same Table used in Mutliple Fields in another Table - Issues

JasonTruman

Board Regular
Joined
Sep 30, 2003
Messages
81
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Sep 30, 2003
Messages
81
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Sep 30, 2003
Messages
81

ADVERTISEMENT

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

Board Regular
Joined
Sep 30, 2003
Messages
81
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,038
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top