Help with Database Structure

30 pts.
Tags:
Database administration
Database design
Microsoft Access 2007
I need to create a databse that keep track of a Item # and associated with that Item# is 2 type of people. DonorID and OwnerID. Both these type of people i want listed in the same Table. So far my structure looks like so.

[strong]tblItems  [/strong]                  [strong]   tblPerson[/strong]       

Item#                           PersonID(want linked to DonorID & OwnerID)

Description                    LastName

DonorID([em]dropdownList[/em])    FirstName

OwnerID([em]dropdownList)[/em]

I can succefully link 1 of them (OwnerID or DonorID) but when i try to link another one access says"relationship already exist" and makes a third table called "tblPerson_1" and then links it to that one.

Why can't I link the 2 foreign keys to the 1 primary ?

Am I going about this the right way ?

How can I use 1 table to identify all my people and just link it to the DonorID/OwnerID fields in tblItems properly ?



Software/Hardware used:
Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

Access doesn’t create a new table, it just shows you the same table 2 times. It needs to do that to be able to display the second relationship.

You can confirm that by querying the MSysObjects table, looking for an object called ‘tblPerson_1′. It will not exist.

You can also query the MSysRelationships table, and you will see that the szReferencedObject field is the same for both relationships.

So, don’t be afraid, a new table is not being created.

-CarlosDl

————————————-

You’ll probably need to edit the code which Access is using to create the second relationship and give it a different name.

–Msi77–
Add another one copy of the table in the diagram and make link with it.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Tdizzle342
    How do I go about editing this code ? I am fairly new to access sorry. REDO: tblItems tblPerson Item# PersonID(want linked to DonorID & OwnerID) Description LastName DonorIDdropdownList) FirstName OwnerID(dropdownList)
    30 pointsBadges:
    report
  • SbElectric
    Well, my first inclination is to create only one table for the Donor and the Owner. This can be easily done by adding a field "TYPE" with "D" for Donor and "O" for owner. Or you could create the Foreign key by starting with D or O as the first character of the key. Then you need to link only one table. May be I am not following your design architecture/structure.
    2,540 pointsBadges:
    report
  • carlosdl
    I think you could create the 2 foreign keys without any problems, with a script (SQL).
    69,990 pointsBadges:
    report
  • tlsanders1
    Maybe create two separate queries with different names, but both pull info out of the person table. Then link your drop down boxes to the queries.
    1,340 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following