You tried to assign the Null value to a variable that is not a Variant data type. (Er

[The_Doc_Man]

The idea of "what's wrong with it" is as I stated. You have phases with so many dependencies that it almost can't be created.

Let's try this again. You have

tblSite - with a SiteID as PK, plus other geographic data.

tblPhase - linked to Site, depends on site.

tbl RCC - this CANNOT BE DIRECTLY LINKED TO ANYTHING in the Site or Phase tables because they don't depend on RCC. (Believe it or not... read on to see what I mean by this.)

You CAN make a junction table between phases and RCCs. As you stated, RCC can apply to more than one phase. The JUNCTION between Phase and RCC is a child of two tables - AND solves the problem that a single RCC might apply to many phases.

You would make one entry in the RCC/Phase table (containing an RCC ID and a phase ID as a minimum, other fields possible) for each phase that a given RCC covers. So that is how you implement that relationship. While I cannot be sure, I suspect that the other tables you showed earlier in your relationship diagram will be the same way.

The technical reason that your originally diagrammed relationship is at the very least unlikely is that it says - in order to have a phase, you must have every one of those other things - a site, an RCC, and the two other tables - all at once.

But that's not true. You start from a site and you lay out your phases whether you have an RCC or not. You just have to wait for the RCC to start work on that phase - but it can exist (on paper and in a legal ownership sense) without an RCC. Your relationship diagram says it could not.

The problem with this is that the RCC will only appear for one phase when a query is run even though it cover phase1 and phase2.
[\QUOTE]

This way, you have a separate pointer to the common RCC - but structurally you can now do a lookup from the pointers. Trying to directly point to the phase from the RCC, as you have noted, gets you nowhere because one RCC record can only really point to one phase. Your "start phase" and "end phase" values vanish now, to be replaced by a list of the applicable phases for that RCC.

Let me try another analogy to give you the mental picture of why your structure was so troublesome. You had four independent trees of different species with a single root system. So of course, Access the arborist didn't know WHAT kind of tree you really had. You were WAY beyond apples and oranges. You had a four-fruit ambrosia mix going on in that diagram.

 

[The_Doc_Man]

I'll add another item in the "attempt to explain what is wrong" category.

As an ideal, you would want a properly designed database to assist you in its own care and sanity-checking. Read up on "relational integrity" (we call it RI) which is the technical term for what I'm about to describe. When a database is set up right, you can turn on RI for every relationship and Access will take care of assuring that all relations are properly observed. You would be advised if something you were about to do would create orphans in parent-child relationships.

What does this have to do with your DB? In the diagram you showed us where four tables were one/many with your phase table, if you turned on RI you would not have been able to create ANY phase entry unless corresponding entries already existed in the four "feeder" tables. Not that you would have had blanks in some fields of the phase table ... instead, Access would have refused to even CREATE the new phase record. From your verbal description, you want to create the phase entry and define the RCC and other entities later because phase creation is a design and engineering thing where as your permits and other entities you described must come later.

There are times when you don't want RI turned on, but if you do try to turn it on, you would have an acid test of database self-consistency. And in your case, I suspect you would have been denied the ability to turn on RI.

Now, I'm not saying that every database needs RI turned on. But choosing to not enable RI for a given relationship should be a conscious decision for a valid data-related reason, not a forced decision because Access balks when you try it. In your case, it would be a forced decision.

I hope this explains "what is wrong" in a way that you can understand, since (pardon me for saying it) you seem to be relatively inexperienced in database issues in general and Access in particular. Sometimes it is hard to remember how we were when we first stepped into a quagmire of a database in our early days. It is hard to cast our answers without using Access terms. (In my case that difficulty would be greater because I've been around so long. Some folks would say, "Gee, Doc, I didn't know databases could run on an abacus." But it wasn't THAT long ago, trust me. )

 

[gemma-the-husky]

Bee

what are you actually trying to model with this project

i used to be FD of a housebuilding company, so i know where i am coming from.

ie - Is this to manage costing and production, or to manage sales or a bit of both.

for example - one immediate thought is why are you so bothered about including the RCC data within the database - surely the technical dept would not try to build or release anything that they knew hadn't got planning permission.

HOWEVER, if your database really has to be able to deal with the ability to identify whether a particular plot build or general construction task is permitted, because of the status of planning for the phase, then you need to get your hierarchy of the site battened down

eg, if you have a simple hierarchy

site ---- phase ---- plots then its relatively easy to include appropriate tables and links

however, if you have some items that are not plot based, but are site general, or even worse multi-phase, as you seem to be implying, eg site roads/sewers/land cost?, then the whole picture becomes confusing, as some costs will need to be allocated to plots, others to site or phase specific. To go a step further even, are you trying to analyse plot costs between lifts or trades.

eg again, you are talking about roads consents - does this include planning for drainage, sewers, electric gas, cable tv connections etc etc, or are these tasks covered by separate planning consents that also need to be modelled within the database.

do you need to be able to replan phases/site layouts during the life of the site

i think you should really think about what you are trying to get out of this, and come up with a data structure that lets the information flow out in a natural way - assuming you are dealing with financials, you dont (and probably cant) allocate every cost to a plot, and your data structure must allow for that, but dealing with planning details and financials in the same database is likely to make this very complex.

Hope this helps, but it sounds like a major major project, and quite expensive.

 

[Bee]

Is this to manage costing and production, or to manage sales or a bit of both.

It's both. They need all this information for different reports they have. Each report goes to a different dept.

 

[The_Doc_Man]

OK, time to pull out the trump card.

Bee, you cannot ask Access to do anything you can't first do on paper. You are having problems (or so it seems) because there is a tangled web of steps to perform here. If you cannot commit those steps to paper first, you should never even open up Access. I'm serious. For a project of the magnitude you are describing, you MUST have a document that programming veterans would call a "design bible." Otherwise, you are in a hopeless mess.

Search this forum for articles on database design and layout. I've authored a few. Many others have thrown in their share. Without a strict, methodical approach, you are going to have stress-related migraine headaches in no time. I am not trying to balk you or cast my views on your project. I'm trying to tell you what Access needs to make this work. And every time I turn around, you add something else.

It is time to go back to first principles. I would SERIOUSLY scrap the design you have, go back to the drawing board, and lay this out on paper first. What you have described so far is just not viable because the relationships are not well-defined. Without that definition, your reports just ain't gonna work. Access will continue to get confused in ways similar to the title of the post that started this thread every time you try something new if you can't get the schema to match both your business flow and the constraints of Access layout rules.

I'm not saying "Give up." I'm saying "Step back to take in the forest because right now I think you're lost in the trees."

 

[Bee]

I am not lost as you think, I have read and searched on the subject before posting the question. But I did not find exactly what I was looking for...there are no headaches....as you mentioned . I appreciate your advice and I am going to add junction tables, but do I need to add three junction tables in this case since tblGrounds, tblRoads have the same principles as table RCC? Do I need to keep the relationship between tblSite and tblPhase the same and only do the junctions between Phase/RCC, Phase/Grounds, and Phase/Roads?

Thanks,
B

 

[The_Doc_Man]

do I need to add three junction tables in this case since tblGrounds, tblRoads have the same principles as table RCC? Do I need to keep the relationship between tblSite and tblPhase the same and only do the junctions between Phase/RCC, Phase/Grounds, and Phase/Roads?

I think I would agree with that.

The relationship of phase to site is pretty clearly a parent/child sort of thing - or a one/many sort, if you prefer. The other three tables, if they have the same properties, almost certainly would require similar treatment. The good old JUNCTION table is the best way to do the "backwards" relationship that appears to apply for phases/RCC.

 

[Bee]

I have created a junction table between tblPhase and tblRCC and tried it before going ahead with the rest. It does not seem to have stopped the error messages mentioned at the beginning of this thread!

Also, tblPhase is not updatable from the query. I can create site and RCC, but not phase. If I create RCC first, then it does not allow me to neither create site nor phase.

I have attached an image with the new junction table.