Need Advice (1 Viewer)

[Bee]

Then you have a basic concept-level design issue that is a LOT more complex than you really described. And I stand by my statement that you do better by having a dummy phase. However, from your last comment, it sounds like time/date becomes an element of identifying phases. Now you are REALLY getting into normalization issues.

It sounds to me like you have phases and houses, but membership in a phase is time-variant. Which means you now need a junction table that includes time. (Otherwise you have elements in the table not dependent on all of the table's keys.)

House: HouseID, address info, etc.
Phase: PhaseID, phase info
HP_members: HouseID, PhaseID, PhaseStart (date/time), PhaseEnd (date/time)

Normalization isn't just a "good" idea - it is based on a set of proofs related to the concept of "computability" - i.e. the ability to compute a result for a given question. With a properly normalized table you can quickly determine whether the information you want is obtainable. If the table is not normalized, you have the very good chance of having inaccessible data because there is no well-defined set-theory-based path from point A to point B.

You see, the reason I am taking a tough line on this is that you complained about all sorts of outer joins (thereby introducing nulls in JOIN queries) and having multiple conditions because of houses in various - or NO - phases.

You were looking for a neat, clean way to eliminate the complexity. The EASIEST way is to eliminate the outer joins and have a dummy phase so that you have no excluded houses. If ALL houses are members of a phase, no matter what that phase happens to be, then you have no more outer joins. You have no more nulls. You have eliminated the nastiest aspects of the special cases that were vexing you.

I'll finish by saying this: You will NEVER get Access to do on the computer something you yourself could not have done on paper. And I'm not talking about the tedious nature of doing it on paper. If you can't do it on paper, how will you ever tell Access how to do it? So this is beginning to smell like a case of poor workflow design or poor understanding of the workflow. I can't tell which from my end. But it is a design-level issue that must addressed very thoroughly before you have any hope of making progress.

Bee, please take this as an attempt to be helpful. If it comes across as a bit of a lecture, just remember my title is not medical. I used to be a college-level instructor. And you can take the Doc out of college instructing, but you can't take the college instructing out of the Doc.

No worries, I appreciate your help.
I will have to go now, but will look at your answer more thoroughly when I come back.

 

[Bee]

The Doc Man,

What I think was wrong is the lack of explanation from my side.

Phases have nothing to do with time. I will try to explain it as much and simple as I possibly can:

This company have construction sites. Each site has many phases (a phase is pretty much like a street because it contains a portion of the houses in the whole site).

Now, when the company are building houses, they need to provide them with services like electricity. So, When they want to install electricity, they get a sub-contractor to install it for each phase at a time and that means the sub-contractor gets one off payment for installing electricity in all houses in that phase. This is good up to now, but there are exceptions.

Sometimes, they have standalone houses that are part of a phase, but they just don't get treated on the same way as explained above in regards to installing services. So, what happens here is as follows: A sub-contractor will come and install services in that house alone.

If we gave a dummy phase to stand alone houses, they will start to belong to the dummy phase and no longer to their own phase; or will belong to both dummy and original phase And that makes it impossible for the company to track what house they have in what phase at the first place ….etc.

I had tblServices linked to both house and phase by houseID and PhaseNo as FKs in the tblServices, so it caters for both situations. The problem was with null values. If I have default values houseID and PhaseNo that belong to dummy records in both tables and can be entered instead of Null, will that be Ok do you think?