problems with relating tables (newbie questions)

[AlexUK]

I am very new to access and am trying to construct a related dbase for customer management. <br><br>what i would like to do is create a basic customer information table with related tables for specific customer information i.e. telecoms, utility details which will obviously be unique to each customer. i have cretaed the tables and have command buttons in place to open subforms for entering details, what i need to know is how to relate the subtables to an individual customer. what is the relationship structure i should use. Simple stuff i know but we all have to start somewhere;-)<br><br>thanks in advance<br><br>alex

 

[elizabeth]

I think you will get a nice visual answer to this and many other useful features if you look at the NorthWind.mdb (A sample db that comes with Access). They have a customer database. <img src=

http://www.Tek-Tips.com/images/happy.gif>

 

[WaltLukeIII]

I asume that You have one table that is the basic customer information and then other tables that give different information regarding the customers relationship with you. <br>if this is the case then I would think a one to many relationship from the first table to each of your other tables. you can do this with the relationship heading under the tools menu. Good luck.<br><br>Walter III

 

[Garridon]

My brother, who's an Oracle programmer, used this example to help me understand relationships:<br><br>You've just moved.&nbsp;&nbsp;You call your bill company and change your address.&nbsp;&nbsp;You get your bills at your new address.&nbsp;&nbsp;A month later, you get some junk mail advertising addressed to your old address from the same company.&nbsp;&nbsp;What's happened is the company has your addressed listed in two different tables; ergo, the bill one got updated, but the other one didn't.&nbsp;&nbsp;In a relational database, that information exists only in one table.&nbsp;&nbsp;Once it's updated, it's corrected everywhere.<br><br> <p>Linda Adams<br><a href=mailto:Garridon@aol.com>Garridon@aol.com</a><br><a href=

http://www.geocities.com/hallona_2000>Linda

Adams Online</a><br>I'm a professional writer, published internationally.

 

[Quehay]

Alex here's newbie advice to newbie question:<br><br>Sounds like you're getting &quot;formcentric.&quot;&nbsp;&nbsp;The relationships have to be made clear first.&nbsp;&nbsp;The subform shows the &quot;many&quot; side of a one to many relationship, e.g., a doctor has three practices/clinics where she is the director, so the Office table has one &quot;Director&quot; column where her name/ID would be entered as a foreign key.&nbsp;&nbsp;Because the number of practices is variable they don't appear in the Doctor table (crude example--also the doctor's name doesn't have to be entered multiple times, increasing work and potential for data entry error).&nbsp;&nbsp;The subforms in this case would show the sites where the doctor is the director when looking at frm_DoctorInfo.<br><br>But..if the info you're talking about is unique to the customer (not shared like an office ph.#) and there is only one per customer there's no great harm in just putting it in one table.&nbsp;&nbsp;It doesn't sound like you're creating something that has to be capable of migration into SQLserver or Oracle so the rules can be bent.&nbsp;&nbsp;(As always defer to what the tipmasters say though.)<br><br>An excellent introduction to db structure from an Access orientation is Steven Roman's Access Database Design & Programming, O'Reilly (1999).

 

[SandyBill]

Thanks to all of you for your help. I think that i have been approaching this from the wrong angle. Back to the drawing board and a large reference book<br><br>Alex