Good day to all,

I hope the holiday season finds you all well. Perry here again - as you remember I am new to Access and this is my first database.

Attached I have a JPG with my current table structure and relationships.

I'm having trouble with 2 of the tables. Looking at the JPG you can see a field that reads QFMCNumID. This is a TXT field that represents the contract number for this submittors record.

Can anyone help me with trying to relate this field to the table that has been circled in the attached JPG?

If I created a One-to-Many then there is a problem when I open the Submittor table - it gets confused with the other relationships already in place.

If I make a Many-to-Many what would I link the Link table to? I seem to have used up everything I can relate.

OR - am I WAY out to lunch with the whole approach?

Many many many thanks!

I can't tell by the picture where QFMCNumID originates (in which table is it the primary key?) Your naming standard is obscuring the true relationships. The primary key name and the foreign key name should be identical unless multiple instances of the foreign key are needed in the same table. For example PersonID is the pk of the person table. MotherID and FatherID also occur in the person table and are both foreign keys and reference the same pk which is PersonID.

You don't set the cardinality of a relationship, Access determines it based on the key structure of the tables being joined. If tableA and tableB are joined on their primary key fields, the relationship is 1-1. If the primary key of tableA is joined to a non-key field in tableB or to one of the fields of a multi-field key, the relationship is 1-m. m-m relationships are simply two 1-m relationships that intersect in a single table.

Therefore to make a 1-m relationship on QFMCNumID in the two tables, that field would need to be the primary key of the submitter table which it doesn't look like it is.

The more a try the worse I seem to make it. I have removed the data and zipped my mess for you to look at.

The QFMCNum is a number that reflects an agreement to certify a Concrete lab to work under the conditions tested.

A Submittor will submit themselves many times therefore 100 QFMCNum's may only have 25 unique Submittor names. The addresses will be different - representing the many factories a Submittor will own.

Each QFMCNum will have contacts pertaining to that record. A new record will have new contacts - thus a 1 to many between the QFMCNum record and a list of contacts (tblLabsContactItem) Sometimes those contacts have an address that is different from the Submittor - so there are fields to support that.

Each QFMCNum will have a series of additional tests. There are 38 separate tests that may or may not apply - thus a separate table. Each QFMCNum will have a record to support the tests (if any) so here there is a 1 to 1. You would never have more tests records than QFMCNum records.

The labs will have persons within them whom also have to be tracked. One QFMCNum will have many persons related to that number - so another 1-to-many between the Submittor Item table and the FieldExamItem table.

Does this make any sense?

I can't do this - I think I bit off more than I can chew. Any help you can offer would be VER VERY appreciated. Think of it as a generous Christmas gift....ha ha.

MSN Messenger address is perrymcleod@hotmail.com is you want to YELL AT ME FOR BEING SO STUPID

I attached the tables less the data.

QFMCNum needs to be the primary key in some table for it to be a foreign key in another.

With help from Andrew I was able to move forward. It's the little things that get missed along the way that create problems.

Many thanks to everyone - happy holidays to all!