Relationship Trouble

[amygamet]

Having trouble with a relationship. Here's what I have:

Customer Table
1-M
Job Table
1-M
Equipment Table

Customers have multiple jobs, which have multiple pieces of equipment. The first relationship works fine--the ID (primary key) from the Customer Table is automatically entered into the "Corresponding Customer" field in my Job Table.

The second relationship is setup the same way--the ID (primary key) field from my Job Table should be automatically entered into the "Corresponding Job Number" field in my Equipment Table. BUT INSTEAD, the "Corresponding Job Number" isn't being filled in at all, and the ID-primary key for my EQUIPMENT TABLE is being automatically filled in with the ID-primary key from my CUSTOMER TABLE. Especially wacky because that field in the Equipment Table is set to autonumber.

Here's where I'm really baffled... Under relationships, only the two 1-M relationships are shown. Yes, I showed all. The only place I can find any join between the ID field on the Customer Table and the ID field on the Equipment table is when I show both tables in the query design view. I just tried this for kicks, and there is was.

Am I missing something really stupid? Why is Access joining those two fields? And help appreciated.

 

[VBslammer]

The reason relationships appear in the query builder is because it automatically tries to join similar fields if you have the option turned on in the Tools -> Options -> Tables/Queries -> Enable AutoJoin checkbox. If the relationship doesn't show up in the relationships window then it doesnt exist.

You can check for the existence of your relationships in the hidden system table 'MSysRelationships' too. You can view it using the Tools -> Options -> View -> System Objects checkbox. However, since this is where Access stores relationship information then you're not going to find anything there that doesn't show up in the window.

Are you using a subform to enter the data? Some developers don't even use referential integrity to join tables and just rely on subforms to 'pull' in the right fields.

Anyway, if this doesn't help try posting more details about your field names / types / sizes / PKs and FKs.

 

[amygamet]

Thanks for the reply. I figured out the autojoin thing a while after I posted, and felt a little stupid about that.
Yes, I am using subforms to enter the data, but I want to make sure there's a unique identifier for each record in my 1-M tables so that I can run different reports off of them and match them up with the customer.

Here's the extra info you asked for; I'm only including the fields that are involved in my relationships or that I'm having trouble with:

Customers Table
ID CUSTOMERS(primary key, autonumber)

Job Table
ID JOB (primary key for next relationship, autonumber)
CORRESPONDING CUSTOMER (foreign key for first relationship)

Equipment Table
ID EQUIPMENT(primary key, autonumber, no relationships)
Corresponding Job (foreign key for second relationship)

So, it's:
CUSTOMER ID 1-M Corresponding Customer
JOB ID 1-M Corresponding Job

The first of these relationships works fine, fills in the ID in the Corresponding Customer field.

The second has two problems: doesn't fill in the Corresponding Job field AT ALL, and for some mystery reason fills in the Equipment ID field with the Customer ID number! I find this last bit exceptionally odd, given that it's an autonumber field--how is it being filled in with the number from a different table?

Also, someone here at work asked someone else who programs Access, she was told you can't have autonumber fields as the primary key. I think she meant as the foreign key, right?

If you or anyone else has some advice for me, I'd really appreciate it!

 

[amygamet]

OH! I forgot to mention that both foreign key fields are set to number, long integer.

 

[amygamet]

I figured it out. I had to make multiple keys and relate everything back to each level of the heirarchy.

That was really fun. (Ugh.)

Thank you for the advice.