Problems with relationships

[itbeginner]

1. It’s about a video system. I have a supplier table which contains records of all suppliers. I have a purchase table in which I keep record of any video I purchase and from which supplier. The supplier table is therefore linked to the purchase table in a 1-to-many relationship.

The problem is if a supplier no longer supplies videos to the shop, its record should be deleted from the supplier table. But I would not like its corresponding records in the purchase table to be deleted. How do I do that?

If I don’t set the cascade delete related records, Access does not let me delete the record in the supplier table. If I set it, it deletes records from both tables and I don’t want this to happen.

Can you please help?


2. In a table, if you put a lookup field which gets its values from another table, Access automatically the relationship between the 2 tables. When relationships have been set, it becomes very difficult to make changes to the tables, especially to the linking field. Access tells you to delete the relationship first but this does not work. I have to delete the table completely and create it again. This is of much of a problem. Can this be resolved by any means?

 

[nberryman]

The main problem is the Purchase data would be useless as the supplier id would not be able to look up names etc, that's why Access is making life difficult.

Try adding a Deleted Yes/No field and adjust you queries to only show no. That will in effect Delete the supplier without the hassle.

You could then run an append / delete query when ever you need to to append details and the delete suppliers.



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[LittleSmudge]

Point 1
Add an 'IsCurrent' field to the supplier table of type Yes/No Default = Yes

Then in all of your Supplier selection combos etc. set the Rowsource to include a 'WHERE IsCurrent' clause

Add an admin form somewhere that lists all suppliers and allows you to toggle the IsCurrent field.



Point 2
Well there's just one more reason why professional database developers NEVER use Look-up fields.

Lookup fields are an access 'add-in' that was added to make life easier for the general office 'Sally' who is throwing together a database after one day's training.

They have no place in professional databases.





'ope-that-'elps.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Glad2000]

I think you have a planning problem. I was wondering why you need to do many changes to a combo-box? And I think you need to understand the concepts of relationship and orphan records.

Nberryman and LittleSmudge gave you the answer for the first point. But I tell you if you want to delete Suppliers, many Purchase record become orphan.
However you can do that making a non-enforcing integrity relationship.

I agree with both answers from Nberryman and LittleSmudge. Add a Yes/No field (Active Supplier? Yes/No) and in the query put a filter that only show the Active Suppliers.

 

[willir]

itbeginner

I agree with LittleSmudge on adding a boolean yes/no field to flag current and inactive suppliers. (Graham, one of these days, you will have to tell us the meaning of your handle)

I would also suggest that deleting records should be avoided until after a couple of years -- you still have inventory from the defunct supplier right??? If you delete the supplier, will this cause problems with existing inventory??

Flagging inactive users will work. As indicated by LittleSmudge, tweak your combo or list box for selecting suppleirs for purchases.

Later, as data and inventory become really obsolete, then (I suspect) most developers would create a routine to purge inventory and then the supplier rather than using a cascade delete. Once click of a button and some one could blow away a ton of current info...

As per making changes to relationships.
My suggestion is to be sure of your design before gathering data and putting the database "into production". It is one thing to add a field to a table (such as "IsCurrent") than to play with primary / foreign keys.

You can delete a relationship, edit the fields and then recreate the relationship. BUT consider the following before celibrating your nirvana -- you delete the relationship, tweak the data or the fields, then try to recreate the relationship -- Access says it can not because of integrity rules. Now you are in a mess. You can no longer link your records properly, and you now probably have bad data which is a heck of a lot worse than defunct suppliers.

One last point. One last word. Backups ... before you start.

Good luck on this one. I sense your handle can no be changed because you seem to have just entered the "school of hard knocks". And you are not alone -- we have all been there.

Richard

 

[LittleSmudge]

And you are not alone -- we have all been there

and some of us still live our life on the edge !



I'd happily explain my handle to you willir :-

But then I've have to kill you ! ;-)




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[itbeginner]

Well thanks a lot for all your valuable suggestions. A few remarks and one more problem.

LittleSmudge. You say that “professional database developers NEVER use Look-up fields”. The reason why I use lookup fields is reduce data entry for the user. Let us say he has forgotten the supplier code, he has to type his name and that could be long. So I find putting a combo box there would make his life much easier.

Willir. You see that’s my problem and I think it’s a major problem for all systems analyst and programmers that we can never be sure of our design. We always have to come back to it to make changes. As far as my handle is concerned, I think I’ll leave it as it is, because I’m really new to Access and I think I’ve got a long way to go to become professionals like you all here. And it’s a good thing to know am not alone.

Ok now the next problem. This one concerns the Member and Rentals table.

I’ve got a Rental form in which the user needs to input the Member ID, Video ID he is renting and the date of course. At the bottom I have a subform based on the Rentals table. I’d like that when I enter a Member ID, the subform would give me details of any videos which the member has not yet returned. The Member ID is a number. The problem is if that member has already returned all videos he has rented, the subform is not supposed to show any record at all. But instead of that it gives me a record showing the Member ID with a Video ID of zero. I’ve tried to put a <>0 in the SELECT clause of the Video ID, but it still doesn’t work. Any suggestions?

 

[willir]

itbeginner

The rental form should have a check-out date / time ... and a return date / time. The subform would then search for all videos checked out by the user with no return date.

 

[itbeginner]

I do have this date willir. And I do have the no return date criteria too. Even if there is no return date, it gives me the membership number, with a video id of 0 and a blank return date. And i dont want this to show up.

 

[RoyVidar]

Back to issue #2. Agree fully with LittleSmudge! Here's a link describing The Evils of Lookup Fields in Tables.

One of the challenge here, as described in the article, is that when relationship is alredy implemented, creating one of those **** table level lookups creates another relationship...

Besides, you are not going to allow the users to manipulate the data thru the tables, but thru forms. Use combos on the form instead, the wizard usually does a good job. If not, check out the faq section of the forms forum (see the Related Forum box at right) having a whole section dedicated to combos. So the answer to your problem with Table Level Lookups is simply to forget them, and use form combos for your lookups.

For the form challenge, you might again take a look in the forms forum. This is default behaviour. You can probably get around it setting the forms allow additions property to No (then when/if you need additions, alter the property to Yes)

Roy-Vidar

 

[LittleSmudge]

Roy-Vidar ( any many other regulars around here ) speak with one voice.

Lookup-Fields are not Combo-Boxes

You can do all the desirable things that you want to achieve with combo boxes on the FORMS without screwing up the table ( that only developers will get to see anyway ) by adding Lookup-Fields.



Nice link Roy.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Glad2000]

I think itbeginner has a planning problem. He has to design in paper the whole process and think ahead about future needs.
But all of you have said some interesting things about look-up fields.
I always have used look-up fields in tables and never have had problems with them, in tables, queries, forms and reports.

I think there are two kinds of look-up fields:

1. To categorize something, i.e.: status= in progress, awaiting, stopped, done, etc.
Color: black, white, red, blue, yellow, etc.

2. To link records, i.e.: Customers and their Purchase Orders.

How do you handle with these two issues knowing the second one is the most important?
How do you make the link between Customers table and PO table with no orphan permitted?
This is very interesting.
Thanks

 

[LittleSmudge]

You're getting confused over terminology Glad2000

"LookupFields" serve TWO purposes
1) To Lie to the developer when looking at the data in a raw table.

2) To tell the Form and report wizard to use combo boxes instead of text boxes when automatically creating forms.


They have no other use or purpose and have no place at all in queries.

As to your points 1 and 2
Go back to your schema. The data for both 1 and 2 are held in tables.
There are VERY rare occasions where one can be ABSOLUTLY sure that the list of 'categories' with NEVER be added to or reduced. In those special cases a combo box with it's row source set to Value List MIGHT be worth considering. However, it is so rare.

In all other cases use Lookup TABLES.
( DO NOT confuse Lookup Fields with Lookup Tables. )

To ensure no 'orphans' are permitted you set the referential integrity in the Relationship window of the database.

When laying out Forms and Reports - if you do use the Wizard - you can quickly convert text boxes to combos by selecting the text box and clicking Format, Change To ..
Then just set the RowSource by hand.
Or if you use the same combo on many forms you can copy and paste fully formatted controls from one form in design view to another.


All in all :-
Lookup Tables are a vital part of any Relational Database
Lookup Fields have no place what-so-ever in any professional database application.




'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Glad2000]

Now, LittleSmudge, I am very confused.
Tell me please what a Lookup field is.

Microsoft says this in the Help:
DisplayControl: Specifies the type of control (combo box or list box) that Access creates by default when you add a Lookup field (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.) to a form. This property is available only on the Lookup tab in table Design view.

But now I do not understand. Can you create a lookup field in a table? Or lookup fields are only in forms and reports? When you create a field in a table using a lookup wizard, that field is a lookup field?
Thanks.

 

[LittleSmudge]

This property is available ONLY on the Lookup tab in Table design View

So it is a TABLE based thing.

Specifies the type of control (combo box or list box) that Access creates by default when you add a Lookup field (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.) to a form

So - as I said earlier - it affects the way that the Form or report creation wizard works. ( My point 2 )

But now I do not understand. Can you create a lookup field in a table?

Yes. quote 1 above says JUST that.

Or lookup fields are only in forms and reports?

No Only the COMBO box that they CAUSE are in the forms or reports. ( My point 2 again )

When you create a field in a table using a lookup wizard, that field is a lookup field?

Yes.
and what MSAccess help then fails to warn anyone is that forever thereafter - if you look at the data in the raw table ( table in datasheet view ) then the screen LIEs to you. Because of the lookup field you do NOT see the data that is actually in the table. you see the data that is refered to BY the data in the table. and there is no visual warning of this either.

so all in all for easy of development use,
Ease for other developers to take over after you've gone,
Ease of understanding what's going on 6-12 months down the line when you are doing maintenance

LookUp fields are to be avoided like the plague.


'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Glad2000]

I don’t think the lookup field lies you, LittleSmudge, because you know the column width is set to 0”.
But this is a minor issue.
My concern is if you don’t like lookup fields, how do you create the link between the main table with the lookup table. To do this, don’t you need a lookup field?
Thanks

 

[RoyVidar]

OK - this discussion doesn't lead anywhere, but perhaps you could tell us what the problem in thread703-889608 is?

Roy-Vidar

 

[Glad2000]

RoyVidar,
the problem is now I am so confused and I don't know how true developer make the relationship between tables. The relationship is not made throu lookup fields? Or there is another way?
Thanks

 

[RoyVidar]

LittleSmudge said it, Tools | Relationships... is a handy interface for it.

Another confusing thingie, some developers prefer not using relationships at all (or not between all "related" tables), using only joins in queries, programatic referential integrity checks etc, but all of that's a bit related to preferences and the requirements of the project;-)

Roy-Vidar

 

[willir]

Glad2000

Let's put a smile back on your face.

Rather than banter technical points and questions, to avoid some confusion, you may want to review some of the following documentation...

http://www.abcdataworks.com/developers.htm

or

http://r937.com/relational.html

And Microsoft's view of the world...
Normalization

http://support.microsoft.com/default.aspx?scid=kb;en-us;283878

Relationships

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467

And here is some words of wisdom...

http://www.mvps.org/access/tencommandments.htm

http://www.mvps.org/access/lookupfields.htm

The first article (first two links, one is to a Word document, the orther to an HTML document) is a great primer on relational databases, Fundamentals of Relational Database Design by Paul Litwin.

Richard