UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Referential Ingegrity (required?), Office 2000    
 
   
bburn
post Sep 21 2011, 05:14 PM
Post#1



Posts: 197
Joined: 2-July 09



Is there any circumstance in which one would not enforce RI in a one to many relationship? In the application on which I'm working, I can't get the results I need using it, but can without. If the only method I use to input records in the many side table in through forms/subs and it's only for my own use, so the chances of records being entered in the child without a corresponding record in the parent is nil.
Go to the top of the page
 
Squire4Hire
post Sep 21 2011, 05:23 PM
Post#2



Posts: 921
Joined: 1-October 10
From: North of the 49th Parallel


Funny... I had a similar question not too long ago. http://www.utteraccess.com/forum/Pk-fk-Rel...t-t1969852.html
My thoughts now are that not using referential integrity can cause a lot of problems down the road, regardless of the current use/results. Perhaps if you explain a bit about how you're attempting to find the data you're looking for with the referential integrity in place and how your table structure is, someone here might be able to help you make the link between the data work in the way you want it to.
Go to the top of the page
 
cpetermann
post Sep 21 2011, 05:25 PM
Post#3


Auntie Spam the UA moderator
Posts: 5,472
Joined: 17-February 08
From: Omaha, NE USA


Please explain a bit more, perhaps with an example of what you want to accomplish
and why RI interferes.
Go to the top of the page
 
bburn
post Sep 21 2011, 06:20 PM
Post#4



Posts: 197
Joined: 2-July 09



Crystal and Squire,
I'm almost embarrassed to explain this AGAIN. Tina was helping me and provided what I thought was a very nice scheme (as I said, it was a total paradigm shift for me) but it was so complicated that I couldn't modify it for my needs. But if you're willing to try to answer this simple question...
Obuild custom wooden windows, and offer a number different styles that are pretty defined. The purpose of the db is to store and calculate values for reports that I use for ordering materials and generating cut lists for machining the elements of each window or door I make.
Usually a customer wants 20 of the same style, for example 20 double hung (hung) windows and they will all have the same specifications (style, bottom rail, top rail and meeting rails are uniform in the whole job but there are about a dozen of others specs for each style of unit) except the height and width. Therefore it makes sense to me to have the tblUnitSpec with among other things the location of the unit in the home and the width and height. Then several tables of general specifications established with the standard values I use for that type of window set as the default. For example the bottom rail of a storm window is typically 3.5", for a Hung its 2.9375, for a casement window it's 2.75 and for a door it's 9.5". So I'd have tblGenSpecHung, tblGenSpecCase, tblGenSpecDoor and tblGenSpecStorm all with the field B_Rail with its respective value in each table. Again there are at least a dozen fields in each GenSpec table that are specific to that style of unit.
To complicate matters further, I frequently need to make "muntins" or grids. While all the windows may all be identical with respect to the general specifications varying only in width and height, they may also have varying muntin configurations or meeting rail offset. One particular project I now have has double-hung windows in which the upper sash has six lites of glass while the bottom sash has nine and the upper is 1/3 the overall height and the lower is 2/3. So I'd like to have a tblMuntins and a tblMR_Offset table to contain the fields that quantitatively describe the situation.
I’d like to open a form/sub in which to select the contract and/or complete the info, then just start adding units. Ideally, I’d tab to the first window/door unit record (unit), select the style (all the defaultspecs would then belong to that unit), input the location, width and height. If the unit had muntins or an offset meeting rail I could popup the appropriate form and input that info for the unit...Then move to the next unit record.
It seems that the GenSpec tables should have the FK contractID and the tblUnit should have the FK of the appropriate GenSpec table. So I gave tblUnit an FK of all of the gen spec tables, like this...
tblUnit
UnitID PK
GenSpecHungID FK
GenSpecCaseID FK
GenSpecDoorID FK
Location
Width
Height
Of course with RI enabled, when I try to enter the first Unit and specify Hung, it pukes and tells me I need a related record in Case. If there was one there I’m sure it would tell me I need one in Door. If I disable RI I can enter any record just fine. When I go back to look at the table without RI, I see the correct Hung ID and 0’s for Case and Door and that’s fine with me.
Once I have all the units entered for the contract I need to run my calculation queried to generate the reports I need. Again, that's the whole point.
Sorry to be so long winded but I’ve found if I don’t completely explain things the answers I get don’t jive with my mission.
tx
Bill
Go to the top of the page
 
Squire4Hire
post Sep 21 2011, 06:29 PM
Post#5



Posts: 921
Joined: 1-October 10
From: North of the 49th Parallel


bburn,
Have a look at your indexing, as well as your default values for the FK Fields in the tblUnit. If the default is 0, it may throw an error because 0 won't be in the PK table.
I've run into this problem a couple of times - in fact, I think it was the exact solution to the problem I was having when I was asking the same question.
Let me know what you find.
Edit: If you find a 0 - change it to have nothing.
Go to the top of the page
 
cpetermann
post Sep 21 2011, 06:30 PM
Post#6


Auntie Spam the UA moderator
Posts: 5,472
Joined: 17-February 08
From: Omaha, NE USA


Bill,
onna take a look at what Tina and you put together.
May take a bit.
Might be tomorrow before I can get back to you.
So, take a deep breath, enjoy your favorite beverage wine.gif guiness.gif
and I'll get back to you as soon as I can.
If anyone else would care to jump in sooner, please do.
Go to the top of the page
 
bburn
post Sep 21 2011, 08:07 PM
Post#7



Posts: 197
Joined: 2-July 09



Squire, Cynthia,
That did the trick. I set the default values for all the genspecs in tblUnit to null and it allowed me to enter multiple records just as I had hoped.
Also, could you tell me how you guys take advantage of the Quote button at the bottom. It looks cool to use but I didn't have any success previously. Do either of you have the time to look at my design and tell me how terribly far from normalized it is and what are the repercussions. I recognize that all the genspecs should be in the same table along with the location, height and width and that would be called a unit (characteristics that describe a specific item). However, it gets very cumbersome when I'm trying to do my calculations in a query and looking at a table with over 100 records. Trying to find the right field them and pull them into the query grid is tedious. It's much easier to look through a list of 12 - 14 spec for the "hung" unit for what I'm seeking and bring down the Width or Height from the unit table.
Bill
Go to the top of the page
 
cpetermann
post Sep 22 2011, 04:06 AM
Post#8


Auntie Spam the UA moderator
Posts: 5,472
Joined: 17-February 08
From: Omaha, NE USA


Bill,
blUnit
UnitID PK
GenSpecHungID FK
GenSpecCaseID FK
GenSpecDoorID FK
Location
Width
Height
the issue you are having is because you are trying to use tblUnit as a spreadsheet
A Unit is Either a Window OR a Door
it can not be all three.
A Window can be a Hung or Casement
A Door can be Interior Or Exterior Or Storm
Does this make sense? Perhaps this is where your paradigm shift vertigo is originating <
The way you and Tina designed the db:
tblContract
tblContractUnitis
tblUnitSpecs
is the correct approach.
A Contract is entered for Units
1 Contract can have Many Units
Each Unit is ONE of the following:
1 Double Hung window unit OR
1 Casement window unit OR
1 Door
Each of those Individual Units have specifications that would be entered in tblUnitSpecs
Height Width Muntins
That is how the data should be entered using your Form & subforms
The tables that you have now created tblGenSpecHung, tblGenSpecCase, tblGenSpecDoor and tblGenSpecStorm
should not be tables-- these would be queries created using the original tables.
These qrys would be the RecordSource for your forms.
Your form/subs can be designed to enter data for each of these specifications
If it were me, I would try adding tab forms to my main form with a Separate tab
for Casement Hung/Double Hung Door with updateable queries as the RecordSource for each.
That way the form would be ready to enter any and all types of units for a Contract.
The forms can be designed to do what you need--IF the tables are designed correctly. I promise!
Go to the top of the page
 
Squire4Hire
post Sep 22 2011, 11:15 AM
Post#9



Posts: 921
Joined: 1-October 10
From: North of the 49th Parallel


Bill,
trip down your db and post us a zip. I'm sure one of us can give you some more pointers on your table structures. As Cynthia has stated, it looks like some rearranging might be in order.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd May 2019 - 09:04 AM