Normalisation

[Mile-O]

Example:

 

[Parker]

Thanks Mile-O

Good example --- Hope Jim Jones dosn't apply for a job though

 

[Mile-O]

Good example

In a complete reverse, I've been using yours as a good example for the normalisation tool I've been working on in VB.

 

[Parker]

Cor.

So at least I'm a good example of a bad example?

I look forward to this wizard.

If there is no authority for classifying db's for normalisation are you going to start one?

The only experiance I have is here on the forum and of using other peoples attempts. £1200 5yrs ago and I never realised untill recently that (1) It never worked properly, (I thouight it was me). and (2) The guy is a complete ripoff

 

[Mile-O]

If there is no authority for classifying db's for normalisation

No. I've been wanting to build something with VB for ages but never had much inspiration until the weekend when I thought of a little gadget that goes through a database and identifies numerous mistakes such as repeating groups, tables without primary keys, reserved words as field names, and so on, etc..

I never realised untill recently that ...The guy is a complete ripoff

From what I've heard, most are. I've seen a few by people who have charged the companies I've worked for a "lot" of money for these databases and they are the most disastrous macro driven pieces of crap I've ever had the misfortune to look upon.

 

[Parker]

From what I've heard, most are.

Oh this guys goooood

He sold me a licence key to activate the app. This also gave access to the source code (I didn't even know what that was then) Due to circumstances beyond my control I lost the disk and the licence key a couple of years ago. I did have a copy of the disk on a backup drive.

6 months ago I decided that it may be nice to dig this program out again and use it to learn about Access and maybe knock it into shape.(it would be useful if it worked proper)

I aproached the guy for a licence Number and he tried to charge me again. DOUBLE RIPOFF. My words where to impolite to print here.

Ambition: Learn enough about Access to reverse engineer his usless program -- change the code so that it works and distribute it as Freeware --- I will, of course, send him a copy (and charge him for it-------£1200 + five yrs intrest soounds about right)

 

[Parker]

Last post a little off topic but ....

I still can't understand why I am getting the problem that I am having with my db. And I hate to give up so, I'm going to attempt to explain exactly what is happening in the post that folloes

I had originally thought that this was a lookup query problem but, Thanks to Mile-O, Pat and my own investigations I am now not so sure.

It dose not appear to be a problem with my system as I have tried this on a different "clean" computer with a fresh install of Access.

In my limited knowledge of Access, might it be a problem with the way my form is trying to update a record? This is a possibility that occurred to me this morning.

 

[Parker]

I know you don't like verbal ramblings but it may help me and others understand the situation.

I have a table, "Table1”, which has many lookups to other tables. These lookups all behave in the same way so I will concentrate on only 1 of them. "HomeAddress"

When "Table1" was split to produce the table "tblHomeAddress" it was populated.

In "Table1” some of the records that had fields that where used for the new table where empty.
When "tblHomeAddress" was created, it created a row for every record that had data in any of the fields used. Plus 2 empty records.

If I try to modify “Table1” and go into the lookup for “HomeAddress” I can only pick records on the list in “tblHomeAddress”, Nothing wrong with that.

If I call the form to edit or add a record I can add a new record. BUT, if I try to edit a record that originally had nothing in any of the fields used for the new table,I end up with all records that where empty being displayed with the new updated values.

 

[Pat Hartman]

Your problem is exactly what I said it was. I'll try to say it another way. You have a value in the AddressLookup field of table1 that matches a key value in the Address table. When you change the data in the Address table for the record with that key, all the records from table1 that "point" to that formerly empty address record now appear to be populated. This is exactly the desired behaviour and why you split the table to begin with. You want multiple records in table1 to point to the same address record so that when the address record is changed, the change is reflected in all related records in table1. An example of this might be a church membership db. The members table contains individual entries for each family member but to minimze maintenance, you want only one address record to maintain so all members of a given family have the same value in the AddressLookup field.

The solution, is to remove the "dummy" value in the Addresslookup field in table1 and replace it with null. That way the record in table1 will not point to any address record. You will probably need to change the join between table1 and the address table in any queries so that the join type is Left rather than Inner. If you don't do this, your queries will "loose" any table1 record that does not have a record in the addres table.

 

[Parker]

Sorted

The solution, is to remove the "dummy"

Problem was I didn’t understand the problem in the first place.

I was trying to make changes to a linked record instead of adding a new linked record.

By adding a new record to the tblHomeAddress and then linking it to the record that is being updated the problem no longer exists.
And it was finally, Pat, your truncheon that made me see it.

However, for the sake of others following this post. It was, maybe, not a wise move to split the information the way that I did --- A little more forethought would have made the job a lot easier.

The analyse wizard is prob. NOT the best tool for doing the job. It appears to be easier, in the long run, to carry out this operation manually.

normalisation tool I've been working on in VB.

Hopefully Mile-O, your method will be better

Thanks especially to Mile-O and Pat for your patience as well as your help.

Where's the stars ?