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?

 

[Glad2000]

I was not arguing I was asking.
Thanks to everybody for the interest to answer my questions.
Thanks to Richard for the interesting links and Roy-Vidar.
But LittleSmudge confused me.
I have built many applications for many small businesses all them very different and all them are working good. I never have had any problems with the relationship at all. I understand relationship, normalization, etc. And always have used “lookup fields”(?) in them without problems.
But she said true programmer do not use this way. They use other ways. My question was: Could you please tell me what the other ways are?

 

[willir]

Glad2000

Thanks for more info on yourself. Sorry about the "beginner" links. Here is a couple of nifty sites...

http://members.rogers.com/douglas.j.steele/AccessIndex.html

http://www.fmsinc.com/tpapers/vbacode/index.html

http://members.iinet.net.au/~allenbrowne/bug-01.html

And of course, worth repeating Royès link...

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

I formalize my relationships with Relationship tool. From the menu, "Tools" -> "Relationships"

I know others do not.

Some will rely only on their forms and reports and code.

You can also use Data Definition Language (such as DAO) and / or SQL statements. Using a script with SQL is probably a more portable approach.

Richard

 

[LittleSmudge]

Glad2000 You seem to be under a very dangerous illusion that using Lookup Fields in some way guarantees that a one-to-many relationship cannot have any orphan records etc.
Ie A Record at the many end having a value in the Foreign Key that does not have a corresponding value in the Primary Key at the One end.

Lookup Fields DO NOT guarantee this.

If you want to prevent orphans than you need Referential Integrity as I said on 28th July.




'ope-that'elps.


Mr. LittleSmudge

 

[RDWilson2]

Re: " Even if there is no return date, it gives me the membership number, with a video id of 0 and a blank return date."

You might want to add a constraint to the query so that it counts the "no return date records" by member and if the "no return date records" count is 0, it doesn't return a anything for that member.


Re; "Well there's just one more reason why professional database developers NEVER use Look-up fields."

Actually, professional database developers SELDOM use look-up fields and ALWAYS TRY to make sure that the design is either sufficiently complete or sufficiently robust with respect to any look-up fields. (As the old saying goes, "Never say 'Never', and always avoid 'Always'." ;-) )

 

[LittleSmudge]

professional database developers SELDOM use look-up fields and ALWAYS TRY to make sure that the design is either sufficiently complete or sufficiently robust with respect to any look-up fields.

Whilst the sentiment is fair enough I disagree with you on the topic.

If there was ever a case that the word "NEVER" was invented for then this is it.

There are NO good reasons for ever using Lookup fields


and the sufficiently robust with respect to any look-up fields is an oxymoron.
No design will be 'robust' if it uses Lookup Fields

Granted it will 'work' fine in everyday use, but when someone comes to upgrade/ maintain/ enhance the thing in a years time then the fact that the table views LIE to you ( because they cause the 'one' end table to display data from the 'many' end table rather than displaying the actual foreign Key held in the table itself ) causes all sorts of problems for the poor soul that has to pick up the job and work with it.

Lookup fields add NOTHING to the design or the day-to-day functionality of any database - yet they cause so many potential problems.




G.



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

 

[Glad2000]

I want to thank Roy-Vidar for his comments and suggestions.
Many thanks to Richard. I appreciate your great links and explanation. Now I am reading all these excellent articles and saving all of them. Thanks. Next application I build I am going to try relationship directly without lookup fields. Let me try it.
Thanks to RDWilson2 for your comprehension.
And thanks to Mr.LittleSmudge. He began this discussion.
Mr.LittleSmudge:
I always have used lookup fields in all my jobs. Very different jobs from simple to complex, using SQL and VB. If you use the word NEVER because it was invented, I use lookup fields for the same reason. It makes me the life easier.
The lookup fields NEVER have lied to me. When I want, I unhide the ID column to see the numbers, I can add other columns to see more info, even, sometimes I make running calculation within the lookup fields. For me they are a marvelous. I consider this programming, but I am not a TRUE developer, but it is programming. This is an oxymoron; I am not a programmer but I built applications. How can you understand this?
Now I am wondering if I am that general office "Sally" you said. I think I am not because I solve management problems.
You are worry about the poor soul trying to upgrade/maintain applications with lookup fields. That poor soul has three ways: 1. To study more to learn about Access and lookup fields; 2. To charge more $$$$ to the customer; 3. To come here, at Tek-Tips.com where there are many great people like all of you guys.
And in the future, SQL, Oracle and other languages will be able to understand lookup fields.

By the way, how is itbeginner? How is your application, guy? Let us know about it.