Relatiships that undo themselves! 1

[Binksy]

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)

Binksy

 

[Robertd]

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
OtherData

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,...)

HTH


Robert Dwyer
rdwyer@orion-online.com.au

 

[Binksy]

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)

 

[Robertd]

Ok

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
AcctTypes
then

AccHolders
AcctID
MemberID
AcctType (not part of the key!)
OtherData

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 ;-)

HTH

Robert Dwyer
rdwyer@orion-online.com.au

 

[Binksy]

im gonna give it a go now!!!

 

[MichaelRed]

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.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Binksy]

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!!!

 

[Robertd]

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
rdwyer@orion-online.com.au

 

[MichaelRed]

Binsky,

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!!!)




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over