I have a master database, permit. Permit carries many fields that are key to another database . Bld to builder, sb to sub etc. Permit needs to be able to link to job and model, but model also needs to be able to link to job. I know I am probably not using the proper terminology, this is all very new to me. I have drawn it out on paper and it seems that it will work (in my mind). And I have tried reading a few different posts here to try and understand how it works.
I have tables A, B & C. A (master) links to B with seq and C with seq. But then I need C to link back to B (I am not sure with what field yet). So A would be a single to many and B & C would be considered??
Your nomenclature is confused and confusing. You are perilously close to asking either of a couple of questions, but you will note the long hesitation in getting even one answer. I attacked this question yesterday but backed off because I decided I didn't know at the time what it was that I was missing.
You can have two possible sets of relationships here. You have not given us a couple of key pieces of data so I cannot tell whether you have screwed up your design or are spot-on target. The following cases can apply to the relationships you have described
Code:
A --> B --> C transitive case (or)
B <-- A --> C multi-directional JOIN
The problem here is that we don't know which of these (if any) are one//one and which are one//many. We ALSO don't know which, if any, are the PRIME keys and which are secondary keys. Lacking that information, we have a hard time knowing what is going on. Nevertheless, I'll take a shot in the dark here and just qualify my comments.
In transitive situations, this kind of relationship would work and make sense if A//B was one//many and if B//C was also one//many. This would be a classic grandparent/parent/child situation.
In multi-directional situations, you need A//B and A//C to both be one//many unless they are one//one.
The part about this layout that confuses the issue is you are talking as though the same key applies to all three tables. If all three tables are one//one using the same key and it is also the primary key then you have a normalization problem of sorts, or the damnedest data we've ever seen. The reason I say that is that there are fancy ways of expressing normalization rules, but here is the bottom line:
A record's elements must depend solely and entirely on the record's primary key. If that isn't true, something is wrong with the data layout.
So... if you have what I described as the transitive relationship and both steps are one//many, you are OK. But if both steps are one//one you are not so OK. For the multi-way case, if any of the relationships are one//one you are not OK.
Why? Because of the THEORETICALLY unlikely nature of the match-up. If two tables actually depend on the same key, why are they different tables? (Rhetorical question.) Things that are alike belong in the same table. In any case where you have the same exact key for two dfferent tables, either your key is incomplete or the tables should represent something that ISN'T different and so can be merged into one table.
Now, you are new to Access so some of this might be confusing. But the key to understanding the confusion is simple: Without more data as to the nature of these relationships we cannot see why you separated the tables in the first place. And this immediatly brings up the chance of a normalization error.
My Data Base's arethe following with Permit being the parent:
Permit – Builder – Sub – Job - Model
All data bases could have the fields bld,sb,jb (This is our way to connect to permit).
Permit fields ,sb,bld,jb, mdl and many more.
Sb would be Permit to Sub
Bld would be Permit to Builder
Jb would be Permit to Job NOW
Model could be connected to Permit or Job
Permit has the records that Job has to connect to. With out the relationship to Permit Job is Lost.
Since Permit and Job carry the same importance to our data the Model could work either way. It's kind of like Job being a child of Permit telling Model it wants to try and be it's uncle.
It almost seems like the chicken and the egg. But I don’t want to make a stupid mistake being as this is the start of a major project. Let me try and present this again since it was posted by Heather.
“A” is master. “B” “C” “D” lives off of “A”.
“D” could be just as happy dinning with “A” or “C”.
It’s the “D” that we want to be sure of.
This said I will go over your above response in the am.
From your description, you have a confusing situation. (Which probably is why you asked the question, no doubt.)
The problem you have described is complex enough that I think my first advice is to consider the Old Programmer's rule. If you cannot do the job on paper, you will not be able to do it in Access. "Why," you ask, again rhetorically...
In order for you to implement a representation or model of your business (which it sounds like you are trying to do), you must understand it well enough to put the design on paper before you have any chance of doing so in Access. Access, despite the lovely things it will do, is really pretty dumb.
First, you must define the business entities that are part of your model. From your previous comments, you have done much of this.
Second, you must be able to draw diagrams on paper that show data flow or business activity sequences or SOMETHING to tell you what data feeds what other business elements, and how that works. Because putting your business in Access is always a LATE step in process automation. The EARLY steps are all parts of the design phase.
Third, you must be able to put the design and notes on paper for another reason. Paper has a better memory than people. Computers remember pretty well but accidents happen. Besides, a computer's attention span is no longer than its electrical cord.
Your description above helps only a little. I still cannot tell which fields are keys and their cardinality with respect to each other.
Something you mentioned IS possible in Access but since I don't fully understand your explanation (and am not sure I want to), I won't go too deep here. You mentioned that one or the other of two entites could be the main drivers for your project. In fact, Access can in theory have multiple tables that drive some or all of your processing. For the U.S. Navy Reserve, we have three such drivers - Personnel, Billets, and Reserve Units. Each could be claimed as a driver for the other two. Which is the cart and which is the horse? Well, because it is a Personnel database, we got arbitrary and said the Personnel tables drive it. But that's just one of several possible views on that subject.
Like that better then the chicken and egg. Putting it that way Model is the child of Permit.
For Info:
1. Permits - "Wisconsin Uniform Building Permits" (Needed to build)
2. Builder - Building Contractor
3. Sub - Subdivision (Land Division)
4. Job - Job site address of Building Permit
5. Model - Models of homes
And thanks for the help. It appears that the Model is going no were with the cart, Permit.
Bob, just organize your thoughts first. That's the best general advice I have for you at this stage.
The way I see what you have told us, you have a JOB at the top of your data hierarchy. Without the JOB, nothing else makes sense. (I.e. why bother to get a permit if you have no place to build.)
I see this as
Supplemental data:
BUILDER - has BUILDERID (prime, could be autonumber), contact information about builder.
SUB - has SUBID (prime, could be autonumber), general information about the subdivision
MODEL - has MODELID (prime, could be autonumber), information about the model being built
PERMIT - has PERMITID (prime, number might be externally defined by whatever the local government uses for permit numbers), permit address
JOB - contains JOBID (prime, autonumber), address (which might be optional unless there is EVER a case where permit and job addresses can legally differ, since it sounds like this is zoning and local government stuff), PERMITID (foreign key to PERMIT), BUILDERID (foreign key to BUILDER), MODELID (foreign key to MODEL), SUBID (foreign key to SUB), and any other data you need for the specific job.
