Relationships getting redefined?

I am trying to model our church records, which in my model result in multiple
many-to-many relationships. For example, Many Husbands can have Many Wives
(well, not at one time, but...) Many People may Witness many Marriages and
so on. My "tblPeople" table has an autonumber primary key field
("autPersonID"), My "joinMarriage" table has an autonumber primary key
"autMarriageID" and two long fields ("lngHusbandID" and "lngWifeID") which
reference the "autPersonID" field in the "tblPeople" table. This theme seems
to occur repeatedly in my current design. In Access 2003, using the
Graphical relationship I can set up this model by dragging and dropping in
the typical way, resulting in tblPeople, tblPeople_1, ..., tblPeople_4,
because I have placed the tblPeople table in the ERD four times to model
many-to-many relationships I am dealing with.

That seems to work ok, but when I save and close save the layout of my
relationships, and then reopen them, the relationships are redefined in ways
that invalidate my model (for example, insisting that a Witness is also the
Person Getting Married.)

I'm thinking of taking the leap to the SQL Server Personal Edition--any
suggestions or advice anyone?

Thank you very much!!

Ross Ylitalo

If Access is getting confused between the different instances of your table
in the Relationships window, the culprit may be Name AutoCorrect. The best
solution might be to delete the relationships that are giving a problem,
and get Access to rebuild the database for you.

1. In the Relationships window, right-click the line representing the
problem relation, and choose Delete. Repeat for other relations as needed.
(Not that this is not the same as deleting the table from the diagram, which
does not remove the relation.)

2. Compact the database.

3. Create a new database.

4. Turn off the Name AutoCorrect check boxes under:
Tools | Options | General.

5. Import everything:
File | Get External | Import.

6. Recreate the relations you deleted at step 1.


For more info on the Name AutoCorrect problems, see:
http://members.iinet.net.au/~allenbrowne/bug-03.html

For a sample database (A2000 or later, 40kb zipped) illustrating how to
organize people into groupings (such as households, companies, choirs) and
be able to choose the individual or the grouping in your mailings/receipts
etc, see:
http://members.iinet.net.au/~allenbrowne/human.zip

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"raylitalo" wrote in message
...
I am trying to model our church records, which in my model result in
multiple
many-to-many relationships. For example, Many Husbands can have Many
Wives
(well, not at one time, but...) Many People may Witness many Marriages
and
so on. My "tblPeople" table has an autonumber primary key field
("autPersonID"), My "joinMarriage" table has an autonumber primary key
"autMarriageID" and two long fields ("lngHusbandID" and "lngWifeID") which
reference the "autPersonID" field in the "tblPeople" table. This theme
seems
to occur repeatedly in my current design. In Access 2003, using the
Graphical relationship I can set up this model by dragging and dropping in
the typical way, resulting in tblPeople, tblPeople_1, ..., tblPeople_4,
because I have placed the tblPeople table in the ERD four times to model
many-to-many relationships I am dealing with.

That seems to work ok, but when I save and close save the layout of my
relationships, and then reopen them, the relationships are redefined in
ways
that invalidate my model (for example, insisting that a Witness is also
the
Person Getting Married.)

I'm thinking of taking the leap to the SQL Server Personal Edition--any
suggestions or advice anyone?

Thank you very much!!

Ross Ylitalo

Finally got the web page written to go with that sample database:
http://members.iinet.net.au/~allenbrowne/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" wrote in message
...
If Access is getting confused between the different instances of your
table in the Relationships window, the culprit may be Name AutoCorrect.
The best solution might be to delete the relationships that are giving a
problem, and get Access to rebuild the database for you.

1. In the Relationships window, right-click the line representing the
problem relation, and choose Delete. Repeat for other relations as needed.
(Not that this is not the same as deleting the table from the diagram,
which does not remove the relation.)

2. Compact the database.

3. Create a new database.

4. Turn off the Name AutoCorrect check boxes under:
Tools | Options | General.

5. Import everything:
File | Get External | Import.

6. Recreate the relations you deleted at step 1.


For more info on the Name AutoCorrect problems, see:
http://members.iinet.net.au/~allenbrowne/bug-03.html

For a sample database (A2000 or later, 40kb zipped) illustrating how to
organize people into groupings (such as households, companies, choirs) and
be able to choose the individual or the grouping in your mailings/receipts
etc, see:
http://members.iinet.net.au/~allenbrowne/human.zip


"raylitalo" wrote in message
...
I am trying to model our church records, which in my model result in
multiple
many-to-many relationships. For example, Many Husbands can have Many
Wives
(well, not at one time, but...) Many People may Witness many Marriages
and
so on. My "tblPeople" table has an autonumber primary key field
("autPersonID"), My "joinMarriage" table has an autonumber primary key
"autMarriageID" and two long fields ("lngHusbandID" and "lngWifeID")
which
reference the "autPersonID" field in the "tblPeople" table. This theme
seems
to occur repeatedly in my current design. In Access 2003, using the
Graphical relationship I can set up this model by dragging and dropping
in
the typical way, resulting in tblPeople, tblPeople_1, ..., tblPeople_4,
because I have placed the tblPeople table in the ERD four times to model
many-to-many relationships I am dealing with.

That seems to work ok, but when I save and close save the layout of my
relationships, and then reopen them, the relationships are redefined in
ways
that invalidate my model (for example, insisting that a Witness is also
the
Person Getting Married.)

I'm thinking of taking the leap to the SQL Server Personal Edition--any
suggestions or advice anyone?

Thank you very much!!

Ross Ylitalo

Thank you for your reply Allen! While I am looking into the possibility that
"Autocorrect" might be causing problems, I suspect it may not be the culprit
in this case as I haven't used the Autocorrect feature in this construction
of my database--that is, I haven't asked Access to cascade corrections
through to other objects. I am reading the links you gave me to see if there
is something I'm not understanding on this issue.

However, I am very glad that you took the time to share the link to the
"Human" sample database, as it seems to present some ideas I hadn't
considered, which seem to hold promise of simplifying my project.

Again, Thank You Very Much!

Ross

"Allen Browne" wrote:

Finally got the web page written to go with that sample database:
http://members.iinet.net.au/~allenbrowne/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" wrote in message
...
If Access is getting confused between the different instances of your
table in the Relationships window, the culprit may be Name AutoCorrect.
The best solution might be to delete the relationships that are giving a
problem, and get Access to rebuild the database for you.

1. In the Relationships window, right-click the line representing the
problem relation, and choose Delete. Repeat for other relations as needed.
(Not that this is not the same as deleting the table from the diagram,
which does not remove the relation.)

2. Compact the database.

3. Create a new database.

4. Turn off the Name AutoCorrect check boxes under:
Tools | Options | General.

5. Import everything:
File | Get External | Import.

6. Recreate the relations you deleted at step 1.


For more info on the Name AutoCorrect problems, see:
http://members.iinet.net.au/~allenbrowne/bug-03.html

For a sample database (A2000 or later, 40kb zipped) illustrating how to
organize people into groupings (such as households, companies, choirs) and
be able to choose the individual or the grouping in your mailings/receipts
etc, see:
http://members.iinet.net.au/~allenbrowne/human.zip


"raylitalo" wrote in message
...
I am trying to model our church records, which in my model result in
multiple
many-to-many relationships. For example, Many Husbands can have Many
Wives
(well, not at one time, but...) Many People may Witness many Marriages
and
so on. My "tblPeople" table has an autonumber primary key field
("autPersonID"), My "joinMarriage" table has an autonumber primary key
"autMarriageID" and two long fields ("lngHusbandID" and "lngWifeID")
which
reference the "autPersonID" field in the "tblPeople" table. This theme
seems
to occur repeatedly in my current design. In Access 2003, using the
Graphical relationship I can set up this model by dragging and dropping
in
the typical way, resulting in tblPeople, tblPeople_1, ..., tblPeople_4,
because I have placed the tblPeople table in the ERD four times to model
many-to-many relationships I am dealing with.

That seems to work ok, but when I save and close save the layout of my
relationships, and then reopen them, the relationships are redefined in
ways
that invalidate my model (for example, insisting that a Witness is also
the
Person Getting Married.)

I'm thinking of taking the leap to the SQL Server Personal Edition--any
suggestions or advice anyone?

Thank you very much!!

Ross Ylitalo