Uncertain about relationship

[amerifax]

I have a master database called permit. It has all the builders that built homes in Wisconsin in past years. It only has one number that has builder information “BLD”. That is the reference used in the builder database file. It’s called “Builder”. It has a reference called “BLD”.

I want to link the “Permit” file to “Builder” file to run lookups. My first job, as I see it, is to develop the “Relationship’s”. Builder has all the phone, address and more for every builder in the “Permit” database.

Please try to understand my use of database or files. Access seems to call it a bit different, I will get it down in the future. My terms are more Visual dBase.

Database or file or “Permit”, Reference is “fields ”bld”

This is my first atempt st the Access relationship. How do I look?

Relationship Permit-bld to Builder-bld.
Edit Relationship
Table/Quary BLD
Related Table/Quary BLD
Enforce Referential Integrity (Box Checked)

Join Type
2. (This was my thought)

When I went back in by clicking on the line between “Permit and “Builder” there was a change. The “Enforce Reference Integrity” box was blank.
The “Join Properties” was changed to “1.”

My objective is to be able to use a form with the “Permit” file and see difference values of the “Builder” file that relate to the “Permit-bld” builder that is current selection.

I tried hard to explain. I hope you got it. Must times the question is harder than the fix.

Bob Snow

 

[fneily]

Yeah, language is important. Do you mean you have a TABLE called Permit and a table called Builder? An Access database = one file. That's the beauty of Access.
It's confusing because you say you're using the Permit File. Do you mean table?
If they are tables can you post the table structures -
tblPermit
Bld Primary Key
Other permit info

tblBuilder
Bld Primary key
etc.

It looks like you'll build a basic one-to-many relationship. But first I need to get less confused about terminology.

Did you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[lespaul]

And you will probably find Understanding SQL Joins helpful too.

Leslie

Have you met Hardy Heron?

 

[JoeAtWork]

Relationship Permit-bld to Builder-bld.
Edit Relationship
Table/Quary BLD
Related Table/Quary BLD
Enforce Referential Integrity (Box Checked)

Join Type
2. (This was my thought)

It's hard to tell from how you have presented it, but you may have got the relationship backwards. As I understand your table relationships, you want Builder to be the "Table", and Permit to be the "Related Table". The "Table" is the thing that must exist before you can have something related to it. In other words, you can't have a permit with a BLD that does not exist in Builder.

For Join Type I would choose first option, for same reason as above, Builder must exist before Permit does.


Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886