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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> help with relationships    
 
   
gkar
post Jul 10 2005, 01:45 PM
Post#1



Posts: 58
Joined: 11-June 05



Hi UA,
eeeelllllppp!!
Im working on a databse for funded/charity based company that I work for
The company collect and recycle IT waste.
We need a system that will allow us to have one company/many address(collection address 1,2 and 3 etc billing, mail etc) each address needs many contacts, each contact has many phones and emails. I have this set up and working no prob. I have a form that allows me to enter and retrive data just as I have said. The problem is I need to intregrate all of the possible company/address combos in a collection form, based on a collection table. This table/form should store Company collected from, company address used (from the list of possible endless collection address each company may have) then contact name info.
I have attatched a screeny of my relationships so far. If u c any major probs please let me know.
many thanks UA.
si frown.gif
Attached File(s)
Attached File  access.JPG ( 145.12K )Number of downloads: 29
 
Go to the top of the page
 
strive4peace
post Jul 15 2005, 12:21 AM
Post#2


strive4peace
Posts: 20,385
Joined: 10-January 04



You need to enforce referential integrity on ALL your relationships -- you have done it on a few ...
Here is one post with information on normalizing* data. If you search the stored posts on Utter Access, I am sure you will find a wealth of information on this subject:
* storing data efficiently
A new guy figuring out how to search -- discussion on normalizing data, designing tables and forms
about halfway through the post, there is a lengthy discussion on data structure, naming, etc ... and about 1/3 from the end is instructions for documenting the relationships and table structures
Go to the top of the page
 
gkar
post Jul 17 2005, 04:04 AM
Post#3



Posts: 58
Joined: 11-June 05



thx crystal,
wealth of info indeed, and more of it is sinking in having read it a good few times!
Ive found a number of redudant fields, and have also changed some field names. I have also now turned RI on for all relationships! I am also going to add a price history table as due the nature of our business prices to customers change very often.
Once Ive got to a stage where I feel I am nearer my goal of normalization I shall Repost my relationship screen, hopefully you wont mind having another look then.
BTW I have read some books, but most are very lite on normalization can u recommend any?
thx again
si frown.gif
Go to the top of the page
 
tbowconn
post Jul 17 2005, 07:03 AM
Post#4


UtterAccess VIP
Posts: 973
Joined: 14-February 03
From: Swamps of Connecticut U.S.A.


Hi si
Crystal's treatise on normalization is a masterpiece. As far as books , I do not know of any that have been suggested here dedicated to the topic. GroverParkGeorge's book does dedicate more than most to the subject. However this link Normalization Links will lead you to an enormous collection of references. Granted some links are redundant, ironically,lol. If you want more or want to see what is in the broken links in my threads, let me know.
Enjoy
HTH
Charlie
Go to the top of the page
 
strive4peace
post Jul 17 2005, 08:34 AM
Post#5


strive4peace
Posts: 20,385
Joined: 10-January 04



Thank you, Charlie wink.gif
i, glad you got benefit.
One of the more important things you can do once you have a good structure is to lay out your relationship window well -- and print it out! In addition to the post, consider this
... data diagrams should FLOW, just as data does -- in the same direction so it is easier to visualize (very important to get the best ideas)

another trick if you have a fieldlist longer than your screen (to get all fields to show) is to put it on the relationship diagram twice

place the copy next to the first one (I usually place it to the right and line it up with the bottom)

adjust the height of the copy and the scrollbar position to show the fields at the end -- then take a screen shot
Go to the top of the page
 
gkar
post Jul 18 2005, 01:38 AM
Post#6



Posts: 58
Joined: 11-June 05



Thx Charlie, I have printed and read a lot of the things you have listed there allready. I shall be looking through the GP stuff in next day or two.
I have today printed out my relationships, and had it on desk all day, It is amazing how much more you can tak ein by doing this! I found some problems corrected them and shal go back tommorrow reprint and got though it all again.
A question RE referential Integrity. I have a company table that has links to collections then to collection items then to inventory. now if I turn on RI, and wish to delete a company I get errors, unless I turn on cascade delete and the whole thing drops like a deck of cards. Lovely just as you would wont in certain circumstances. But I wil probelly wont to delete a company but leave the collections and inventory info intact. The only way I can see of doing this is not use RI. Now is there something here Im missing on the 3 forms? OR is this just the way it is, and sometimes RI is not the answer?
Thx again
si frown.gif
Go to the top of the page
 
strive4peace
post Jul 18 2005, 03:14 AM
Post#7


strive4peace
Posts: 20,385
Joined: 10-January 04



if you delete a record -- you should delete the related child records -- or at least remove the reference!
You can do this with an Update query
Keep RI ON -- run the Update Query first on the child records, THEN DoEvents* , THEN delete the record you want
*
DoEvents is used to make VBA pay attention to what is currently happening and look to see if the OS (Operating System) has any requests.
ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK, put DoEvents into the loop
DoEvents will also update values written to a form by a general procedure
A DoEvents is done when you use MsgBox, or are stepping through code (since it has to pay attention to the keyboard)
It is a good way to say, "Wake Up!"
Go to the top of the page
 
gkar
post Jul 18 2005, 03:46 AM
Post#8



Posts: 58
Joined: 11-June 05



thx crystal, it makes sense not to have unrelated data flying about. If I need to remove Donors I could use an update to replace company (supplier) ref with some kind of "generic company" info. Allthough the more I think about the very principles I trying to get my headaround the more I know I should just leave any related records fully intact.
That about RI on basic lookup fields? e.g. a contacts title...Mr..Miss. etc etc, there is no need to delete refs here, and data entry can be kept to the lookup fields used. Is it still best practice to keep it on? Surley RI dosnt count here
O BTW Having read youre piece about normalization I had laid my Relationships out with the data flow in mind. It is just so much easier to see how things relate and flow like this. I also found access has a "print relationships" option which made the whole thing v easy. Allthough I did have to use a load of tape to join em all together!
many thx for youre time crystal you have been very helpfull and more and more of this BEAST of a program is making sense! lol
all the best
si frown.gif
Go to the top of the page
 
strive4peace
post Jul 18 2005, 03:56 AM
Post#9


strive4peace
Posts: 20,385
Joined: 10-January 04



you can replace with NULL instead of making a generic record to get ID for...
If field such as contact title, I do not make a lookup table -- I use a "self-lookup" -- lookup values from the field you are IN -- no need for RI -- in fact, no need for the extra table...
Odon't like the Print Relationships from the menu -- for the reason you said -- to much tape! Instead, I paste screen shot to Paint -- save as JPG, then Insert picture onto PowerPoint slide or Word Doc (change to Landscape first) to print
Thanks, si wink.gif happy to help
Go to the top of the page
 
gkar
post Jul 18 2005, 04:28 AM
Post#10



Posts: 58
Joined: 11-June 05



thats interetsing about the self lookup, you say lookig in the filed youre in you mean the table?
rnt you breaking norm rules by doing this? Or is this is one the those "realworld" situations that means for breaking norm' rules.
What criteria do u use to move away from lookups to self lookups? I have several lookup tables that maybe would be better off by bringing it into one table instead.
kind regards
si frown.gif
p.s. off to clear my head get some fresh air!!! Phew to much ac c c c c e s s s s s s s s arrrrrrr.... lol
Go to the top of the page
 
strive4peace
post Jul 18 2005, 05:42 AM
Post#11


strive4peace
Posts: 20,385
Joined: 10-January 04



not bring the loolkups into ONE table ...
se the table that you are filling!
Lets just take one of your comboboxes -- like contact title
Look at the design view of the form where you collect and display the information from that table
Look at the properties of the combobox for contact title
What is the ControlSource?
What is the RowSource?
What is the tablename it comes from (RecordSource property for the form)
since you don't need to have a list for it, it is best if you let the user use any title, even one not on the list -- then, that new title becomes part of the list for the next time (when you Requery the combo) -- without having to add it to another table
Go to the top of the page
 
gkar
post Jul 18 2005, 06:34 AM
Post#12



Posts: 58
Joined: 11-June 05



im not sure im following crystal. <img src="/forums/images/icons/frown.gif"> you talking about adding data thats not in list to the table in question ie tbltitle.
Go to the top of the page
 
strive4peace
post Jul 18 2005, 06:56 AM
Post#13


strive4peace
Posts: 20,385
Joined: 10-January 04



I was just trying to help you with an example ... if you would answer the questions I asked, I can do that ... obviously, if you don't answer them, I can't help you
o you know how to turn on the property sheet and look at settings?
There are the questions:
1.What is the ControlSource property of the combobox?
2. What is the RowSource property of the combobox?
3. What is the tablename it comes from -- RecordSource property for the form?
Go to the top of the page
 
gkar
post Jul 18 2005, 07:06 AM
Post#14



Posts: 58
Joined: 11-June 05



sos heres the answers ....
ombo controlsource is... chrTitle
rowsource is... SELECT tblTitle.idsTitleID, tblTitle.chrTitle FROM tblTitle ORDER BY [chrTitle];
table name is... tbltitle
Go to the top of the page
 
strive4peace
post Jul 18 2005, 07:27 AM
Post#15


strive4peace
Posts: 20,385
Joined: 10-January 04



What is the tablename that chrTitle is stored in?
Go to the top of the page
 
gkar
post Jul 18 2005, 07:33 AM
Post#16



Posts: 58
Joined: 11-June 05



the title info is stored in tblcontactscontact
Go to the top of the page
 
strive4peace
post Jul 18 2005, 07:54 AM
Post#17


strive4peace
Posts: 20,385
Joined: 10-January 04



make this the RowSource of the combobox
SELECT DISTINCT chrTitle FROM tblcontactscontact ORDER BY [chrTitle];
On the AfterUpdate event of the combobox ...
me.combobox_controlname.requery
so, if you add another item, it will appear on the list next time
Make sure the combobox is is for
LimitToList --> No
THIS is what I meant -- forget about putting titles in another table, just make the list from your tblcontactscontact (what a name...) table
Go to the top of the page
 
gkar
post Jul 18 2005, 08:20 AM
Post#18



Posts: 58
Joined: 11-June 05



aha !!!! all becomes clear
hx for that crystal will use this is a number of similar situations. Allthough is this not breaking norm rules? If so at what point would you consider this OK?
HAs for the tbl name yep im gonna change that tblcompanycontact !!!! lol
thx for youre patience on this one! It is appreciated. frown.gif
best regards
si frown.gif
Go to the top of the page
 
strive4peace
post Jul 18 2005, 08:56 AM
Post#19


strive4peace
Posts: 20,385
Joined: 10-January 04



you are welcome, si wink.gif
So, it is not breaking any rules ... is a Title a valid piece of information all by itself? Does it have ANY meaning if it is not associated with a human?
For example,
an address represents a dwelling -- it stands on its own and you can visualize it...
a phone can be important all my itself and you can visualize it... although you may choose not to have a seperate table for them
a birthday is not independent
a hire date is not independent -- even though several people may have the same one ...
get the picture?
Go to the top of the page
 
gkar
post Jul 18 2005, 04:41 PM
Post#20



Posts: 58
Joined: 11-June 05



I understand(I think lol) Title is indeed tied to the person, even though title can exsist on its own. It is after a description of the staus of a peron not the person itsself, it is mainly meaningless data without a person, but it is repeating data, a list and I thought that was not allowed in first normal form. Having said that a contacts name will repeat eventually, even street names and the possabilty of house numbers can repeat so how far should one take 1NF.

If I am misunderstanding I am sorry crystal, I have never been so overwelmed by a piece of software and its concepts as I have by Access!

thank you again for youre patience and guidance.

kind regards

si <img src="/forums/images/icons/smile.gif">
Edited by: gkar on Tue Jul 19 11:49:31 GMT-5 2005.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:33 PM