Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Relatiships that undo themselves!

Relatiships that undo themselves!

Relatiships that undo themselves!

I've created an Access database with several tables, and when i have tried to create relationships between two tables (one-to-many) following the instructions in the Access manual, the relationship somehow reverses (becomes a many-to-one)


RE: Relatiships that undo themselves!

Hi Binksy,

seems more likely that the key from the tables makes access think that this is the correct relationship.

basically the one side of the relationship should have a single key field (it could be multiple field but not good design practice) and the many side will have a multiple key field.

Order Table        OrderItemsTable
OrderID 1 - M  OrderID
Otherdata          ProductID

here the table Order has a unique identity that relates to the table OrderItems which has a unique identity
"orderID AND ProductID" however niether OrderItems.OrderID OR OrderItems.Product uniquely identify any records in OrderItems. check your table structure (unless this is truely a strange fault,...)


Robert Dwyer

RE: Relatiships that undo themselves!

Thanks for the tip, but when i made sure that indexes and fields were correct and deleted relationships and started again, it did exactly the same.  I asked a lecturer at my college, and he does not have the fogiest!!! :(

My relationships:

Member --- Acc Holders --- Account --- Statement
(One)        (Many)         (One)       (Many)

RE: Relatiships that undo themselves!


does this mean you have something like this as the keys for the relationships?
Member     AccHolders    Account    Statement
MemberID   AcctID        AcctID     StatementID
                 MemberID                 AcctID

just a thought here i would change the account table and its relationship with the other tables

Member 1:M AccHolders (MemberID->MemberID,AcctID)
AccHolders 1:M Statement (AccID->StatementID,AccID)
Accounts 1:M AccHolders (AcctType->AcctType)

The relationship to account would be via something like
AccountType but not as a key in AccHolders.

Member     AccHolders    Statement    Accounts
MemberID   AcctID        StatementID    AcctType
                MemberID      AcctID

AcctType (not part of the key!)

This allows Members to have multiple Account Types and depending on how you define the index for AccHolders members could have multiple Accounts of the same Type. if AcctType were part of the Key then a member could also have several of the same type of account. with out knowing exactly what you wanted to acheive


Robert Dwyer

RE: Relatiships that undo themselves!

im gonna give it a go now!!!

RE: Relatiships that undo themselves!

A different thought.

If the db/app is a multi user and other users are logged on when you make the change, then when they log-out (after you), does the db retain their version of the relationships?

Try setting the relationships whne you have exclusive access to the db/app.

There is never time to do it right but there is always time to do it over

RE: Relatiships that undo themselves!

Got is sussed!!! it was the fact that i had to change the duplicates to allowed in one table and no duplicates in the other.  i had to get one of the top bods in who just walked in and realised the problem straight away.  MS bloody access makes me feel like a fool!!! :)

RE: Relatiships that undo themselves!

No not at all, it's all part of the learning process.

access has its draw backs but relational databases for the time are the best, wait for object databases

see ya

Robert Dwyer

RE: Relatiships that undo themselves!


That's why Ms. XXXX is the (set of) program(s) we love to hate.  If if were much worse, it couldn't be used by anyone but the 'real experts', if it were much better, anyone could use it without asking for the help (read employment).  Right where it is, everyone can get just far enough into the projects that they don't want to throw it away, but they need some help - EMPLOYMENT OPPOTUNITY!!  YEA!!!  Struggle to figure out arcane and mysterious issues - BOO! (YEA - More EMPLOYMENT OPPORTUNITIES!!!)

There is never time to do it right but there is always time to do it over

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close