Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Binksy (Programmer) (OP)
23 Nov 00 14:32
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
Helpful Member!  Robertd (TechnicalUser)
24 Nov 00 8:45
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 (Programmer) (OP)
26 Nov 00 14:07
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 (TechnicalUser)
28 Nov 00 8:02
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 (Programmer) (OP)
28 Nov 00 12:29
im gonna give it a go now!!!
MichaelRed (Programmer)
29 Nov 00 7:34
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 (Programmer) (OP)
30 Nov 00 3:46
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 (TechnicalUser)
30 Nov 00 5:21
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 (Programmer)
30 Nov 00 6:36
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

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