Problems with setting up relationships - HELP!

[Guest]

Please please can someone help me! I have done a few Access databases before
and am fairly familiar with relationships. The databases I have set up in the
past have been quite simple though i.e. Customer/Products/Suppliers/Orders. I
have now got to set up a new database with 8 tables and have tried to set up
the relationships as I think is correct but when I am trying to run a query
with more than one table it is not running correctly (hence this is why I
think it is the relationship between the tables that is not correct).

At the moment I am only worried about setting up 5 main tables, these are:
Main Practice Details
Branch Details
PC's
Audit Results
Known Problems

- Some of the main practices have more than one branch therefore I have
linked these using a one-to-many with Practice Name in both tables as the Key
Field.
- Each Main Practice will have more than one PC record therefore I have
linked these using a one-to-many with Practice Name as the key field in both
again.
- Each Main Practice table will have one Audit Result therefore I have tried
to link these as a one-to-one again using Practice Name as the key field.
- Finally the Known Problems table - this will only have information in it
if a field from the PC table has the answer "No". As this is the case I have
know idea how to link them as the link is dependent on the data.

I am on a really tight deadline to get this database done but I cannot start
implementing queries until I know the relationships are correct. I would
really appreciate any help!

 

[Guest]

Please please can someone help me! I have done a few Access databases before
and am fairly familiar with relationships. The databases I have set up in the
past have been quite simple though i.e. Customer/Products/Suppliers/Orders. I
have now got to set up a new database with 8 tables and have tried to set up
the relationships as I think is correct but when I am trying to run a query
with more than one table it is not running correctly (hence this is why I
think it is the relationship between the tables that is not correct).

At the moment I am only worried about setting up 5 main tables, these are:
Main Practice Details
Branch Details
PC's
Audit Results
Known Problems

- Some of the main practices have more than one branch therefore I have
linked these using a one-to-many with Practice Name in both tables as the Key
Field.
- Each Main Practice will have more than one PC record therefore I have
linked these using a one-to-many with Practice Name as the key field in both
again.
- Each Main Practice table will have one Audit Result therefore I have tried
to link these as a one-to-one again using Practice Name as the key field.
- Finally the Known Problems table - this will only have information in it
if a field from the PC table has the answer "No". As this is the case I have
know idea how to link them as the link is dependent on the data.

I am on a really tight deadline to get this database done but I cannot start
implementing queries until I know the relationships are correct. I would
really appreciate any help!

I see your relationships as follows:

MainPractice
--------------
PracticeID (PK)
PracticeName

Branches
----------
BranchID (PK)
PracticeID (FK)

PCs
----
PCID (PK)
PracticeID (FK)

Audits
-------
AuditID (PK)
PracticeID (FK)

KnownProblems
------------------
ProblemID (PK)
PCID (FK)


This is how I see it as you have things described but I would question the
PC/Practice relationship - wouldn't the branches have their own PC's? Also,
are the KnownProblems related to the PC's? or are they rlated to something
else?

 

[Guest]

Firstly - thank you so much for replying to my message!

Yes the branches would also have their own PC's so I need a way of linking
the branch PC's to the Main Practice as well as the PC Table itself.

Yes, the Known Problems are to do with the PC's and they will only have
information when a field described as "compliant" in the PC table is "No".

I know how to set primary keys but how do you set something as a foreign
key? Or is it just described as a foreign key when you link another table's
primary key to that field?

 

[John Vinson]

I know how to set primary keys but how do you set something as a foreign
key? Or is it just described as a foreign key when you link another table's
primary key to that field?

Exactly. A foreign key will not be labeled or identified as such in
the table definition; it's just a term describing how the field is
being used (as the recipient of a relationship line in the
relationships window).

John W. Vinson[MVP]