Problem relationships

[sinbadly]

tblEmployees is the one side of a one-to-many relationship with tblEmpWorkers, and also with tblWorkRecords.

tblEmpWorkers has a subform on the main form whose data source is tblEmployers.

All three tables have enforced referential integrity etc.

The form and the subform work together as we'd expect it to, but the form for tblWorkRecords doesn't.

For instance, if the id for a record is 25 and I make the foreign key field for the works record form 25, it will appear for every other record too. So although they are related in the relationship, the two tables are not acting that way. And I don't know why. What am I doing wrong, please?

 

[BlueHorizon]

Hi sinbadly,

Can you explain better what this means?

For instance, if the id for a record is 25 and I make the foreign key field for the works record form 25, it will appear for every other record too.

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[sinbadly]

Thanks, BlueHorizon

What I mean:

Two tables appear in a form and sform. But I want other tables to be part of the relationship.

The two on the main form obviously are working together. How can I get other forms (from other tables) to be related and working together.

For instance, if I am making adjustments to id=25, how can I make sure that id=25 (as a foreign key) on other forms can be summoned too, at the same time, if needed, please?

 

[WildHare]

Is your Employee ID linked to BOTH of the child tables in TWO one to many relationships? e.g.

EmployeeID -------> tblEmpWorkers.EMPID (FK)
          \-------> tblWorkRecords.EMPID (FK)

The only reason I ask this is that many people don't realize that you can make one field in Table A the foreign key in more than one other table...



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[sinbadly]

Yes, I must say that I hadn't realised it either until recently, and then I wasn't sure that it would work. However, your say so confirms it must be.

And that is what I have. But it still doesn't want to be tied (as if were) to the others.

When the combined forms opens at, say, EmployeeID 25, shouldn't the other related form (FK), when called up, also be at 25?

But when it opens, it opens at the beginning and has to be clicked through to get to the appropriate record.

I would have thought that being related it would open at the same place as the others in the relationship. Or have I got that wrong, do you think, pls, WildHare?

 

[WildHare]

Relating tables, per se, has no effect on the positioning apparent when you open forms.

If you've embedded your subforms (which I read as being TWO subforms for the one main form), and LINKED them correctly via the Link..Master..Child process, they (the two subforms) should both open and position to records that match the current key on the main form, right?



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[sinbadly]

Perhaps I should embed the third table too. But there are to be other tables in this db. I'll have another hard look this afternoon. Many thanks, WildHare.

 

[sinbadly]

Hello,WildHare, you mentioned a sample. I expect you have been contacted too. I wonder if you could post it here?

 

[WildHare]

Hope there's no problem with posting outside links...

This is just a single form with two linked subforms showing the basics. Nothing much, but here's the link to the Access 2003 DB.

http://www.jmhare.com/databases/One_to_Many_Sample.mdb

--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[sinbadly]

Many thanks for that, WildHare. Will study it tonight, and return in the morning. Cheers