A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Field relationships



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2004, 05:03 PM
Kris
external usenet poster
 
Posts: n/a
Default Field relationships

Is there a way to have a delete record command button on a subform only
delete the field(s) that are linked to the parent forms table? For example:
I have a table (misc_donations) with 3 fields: donation_type, donation_amnt,
donor; and another table (donations_types) that has just 1 filed :
donation_type. On the form to enter misc_donations I include a button to
open a subform to enter/edit donations_type. The donation_type on
misc_donation table is selected from a pull down list. I can get it to
update changes to donation_type but if I delete a donation type from the
donation_type subform it deletes all of the records in the misc_donations
which have that particular donation_type, I just want it to clear out or
change the donation_type field.

Thanks, Kris


  #2  
Old April 24th, 2004, 10:22 PM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default Field relationships

It sounds likely you have cascade deletes setup. It also sound like you are
enforced referential integrity here.

Really, all you have is a lookup. Lets say a table of favourite colors.

If you delete a few records of bob's favourite colors...you don't care, or
need any relation stuff to the "colors" table. It is not really a formal
relationship..but just a list you use for looking up colors to save typing.

So, go to your relationship window..and double click on the join link
between those two tables...you want to un-check the cascade deletes. You can
leave in the referential integrity (enforce)...but you don't really have to.

Fact is, in our color example...you are free to add new colors to the
table...and again many customers may not yet have chosen their favourite
color. So, you code of want to distinguish between real relations like a
customer who has many orders. If you delete that customer, then you most
certainly want all orders that belong to that customer removed also. And,
you also don't want to be able to add an order UNLESS the customer exists.

However, with our color example...we have a lot of freedom..and we can add
new colors to our color list, and we can delete customers..but we never
delete colors from that simple list of colors. So, it is rather more of a
lookup list..then a relation. In fact, you can get away not even drawing any
join lines in the relationship window in this case. However, it is handy to
leave them there...as it helps you see that some tables do have a lookup.

If you take look at the following screen shot..you can see that when tables
do NOT have the sideways 8 (omega), then we are talking only about a
lookup. And, if you look even closer...you will note that some lines have an
arrow head..but some don't These are enforced relations..but what we call
left joins.

for example...we might set the database up so that all customers MUST have a
order. But, likely that is too restrictive of a relation (so, we use a left
join...or what the prompt says:

Include all records from customers and only those records from table orders.

If you set the relationship to where joined fields are both equal..then in
effect you are saying that customers MUST have a order for the database to
function. As a result, looking at the following relations picture...90% of
all my relations are left joins (yours should be also).

http://www.attcanada.net/%7ekallal.m...Appendex2.html


So, for example, tblBgroup (booking Group), is linked to table
payments...note the right arrow. That means, as a developer I accept the
fact that no payments may not yet be made. However, look at how tblBgroup is
joined to tblBooking. So, I do NOT allow a "group of people" to be booked
until a booking record is made. Thus, all my code will assume when you
create a booking..you MUST ADD people (the booking group).

So, setting up your relationships diagram can result is HUGE amount of
documentation for the project. In fact, I have as consultant walked into
companies and had developers fired for lack for setting up
relationships..and using ER tools. It is the first thing I look for when
evalaution any projects..or skills of the develoeprs involved.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.