Normalisation

Parker · Feb 8, 2004

I have a wierd problem

I think the route of the problem is in the design of a lookup query but I am unsure.

I have a tbl (Table1) that has a number of lookups to other tables. the lookup that appears to be causing the prob is a lookup to a tbl called "tbleHomeAddress"

I split Table1 using the analise wizard in Access (this may be the route of the problem)

When the table was split a lot of records had no value entered in a text control called "HomeAddress" One record had no value in HomeAddress but had an entry in other fields in the underlying rs. (for sake of clarity we will call this record "spoof")

Now, I have ended up with all records that have no value in the text control "HomeAddress" linked to record "spoof" and if I update one of these empty records then "spoof" gets updated and I end up with that information in all records.

Pat Hartman · Feb 8, 2004

Sounds like your foreign key field (the link to the address table) defaults to 0 or some other value rather than null. Change the default to null (just remove the value that currently sits in the default property) and then run an update query that updates all foreign key fields with a value = 0 to null.

Parker · Feb 8, 2004

Thanks Pat

I'll try it and report back

Parker · Feb 8, 2004

Now I'm lost

(just remove the value that currently sits in the default property)

Where do I find the default property? this is what is writen fro the lookup in the fk

SELECT [ID] AS xyz_ID_xyz, [Home Address] & ', ' & [Post Town] & ', ' & [County] AS xyz_DispExpr_xyz, [Home Address], [Post Town], [County] FROM tblHomeAddress ORDER BY [Home Address], [Post Town], [County];

Pat Hartman · Feb 9, 2004

The default value that I was referring to is the one in the table. The query you posted doesn't join to tblHomeAddress so I don't know what problem you might be having with it.

Parker · Feb 9, 2004

Now I'm totaly confused

All tables have nothing in the default value

The query that I posted is what is writen in the lookup for the fk in table1

The fk in Table1 is joined to the pk in tblHomeAddress.

Mile-O · Feb 9, 2004

Can you post your database - or at least a copy with the guilty tables?

Parker · Feb 9, 2004

Thanks Mile-O

I'll email it to you, if you send me your email address, because if I desensitise it for public display you may not get the full picture

Mile-O · Feb 9, 2004

Parker said:
I'll email it to you, shortly, because if I desensitise it for public display you may not get the full picture

Oh! And you know my email address, do you?

Parker · Feb 9, 2004

Sorry I did eddit my post when I realised your email is not published

Mile-O · Feb 9, 2004

Parker, I've looked at your database and other than the serious normalisation issues (and the largest repeating group I've ever seen) I can't see what's wrong with the lookups you refer to.

Parker · Feb 9, 2004

Thanks

Sorry it took me a while to reply but I had to go out.

Yes I know about the normalisation which is why I was attempting it.

Repeating group? Is this maybe where the prob. is? I don't know. Being quite new to this, I have to confess, I don't even comprehend what you mean. I had assumed it was a lookup problem because of the way it was behaving. But the symptom is not always the cause.

Please try and explain. I am a quick learner. Honest.

Thanks

Pete

Mile-O · Feb 9, 2004

Parker said:
Repeating group? Is this maybe where the prob. is?

No, that's not where the problem is. The repeating group is in the table Employees where you have numerous Yes/No fields. These would be best served as a new table and then joined to the employees table via a many-to-many relationship.

Parker · Feb 9, 2004

Ahh haa

Ok I can take that on board --- I've just searched and read a couple of other posts you have made that included this subject.

Now I at least know what you’re talking about.

But you would pick on the one table I haven't paid any attention too and is a new addition that I knew needed attention. But I take that on board and will sort it. Promise.

So what is my problem then?

If you look at table1 you will see a lot of records with "A" in the lookup to ""HomeAddress" If you change the value "A" then all records with that value will change. In other words, if you change the value in that field then lotsa records change instead of just the entry for that record. And this is not the only place this happens.

I'm sorry this is long winded but it's the only way I can explain -- there is prob. a techie saying that will describe it in no more than a few words but I don't know what that is.

Thanks again for your patience.

Pete

Mile-O · Feb 9, 2004

Parker said:
So what is my problem then?

I don't know as I don't see one.

If you look at table1 you will see a lot of records with "A" in the lookup to ""HomeAddress" If you change the value "A" then all records with that value will change.

That's not happening to me. It works as I would expect.

Have you tried Compacting and Repairing? And what are the two objects in the database as I have a couple of missing references to some ocx files I don't have.

Parker · Feb 9, 2004

Ok

The 2 ocx. controls are for one of your pet hates; an on screen clock and a calender. And there is a good excuse for them.

As for the problem I'm having with this db Duno know why you can't reproduce it??

I have tried Compacting and Repairing but it made no difference appart from to reduce the size so that I could email it -- it was getting very bloated.

All I know is that if I try to update a record already entered before this process then I get the updated records appearing for all records where that field was origonally empty. This is why I thought it was the lookup query in the first place. It appears all is ok for entering a new record.

Thanks

Pete

Parker · Feb 9, 2004

The repeating group is in the table Employees where you have numerous Yes/No fields. These would be best served as a new table and then joined to the employees table via a many-to-many relationship.

I have read a few articles on 1NF, 2NF, 3NF yours included.

In the employees table that you mentioned, most of the yes/no columns are direct response answers to direct questions or investigations that employees have to go through before they can work for us.

How would you best go about organising these? Even if I split them into groups in other tabvles I will still end up with repeating groups. Only other way I can see is to give each yes/no group its own table -- a lot of tables for just two options in each. I must be missing something??

This table was built it from the information that we collected on paper up until now. But, as you can see it is not populated yet.

Pat Hartman · Feb 10, 2004

I haven't seen the db but normalizing a repeating group entails making it vertical (many rows) rather than horizontal (many columns). So your normalized table will look something like:

tblResponses:
QuestionID (pk field1, foreign key to tblQuestions)
PersonID (pk field2, foreign key to tblPerson)
AnswerText

Mile-O · Feb 10, 2004

Yes, Parker:

Rather than:

Code:

EmployeeID Forename Surname Diabetes Epilepsy Angina HGV etc

it's:

Code:

EmployeeStatID EmployeeStat

With the group listed within the EmployeStat field.

Parker · Feb 10, 2004

Mornin

Thanks to Pat and Mile-O

tblResponses:

EmployeeStatID EmployeeStat

Ok I think I understand now and will work on it.

Still can't figure the other prob though. And I can't understand why you can't reproduce it????

Maybe I'll rip it down and try it again only this time without the MS wizard. But I hate to give up, I don't believe that it is doing anything wrong, just that it has been given the wrong information, or formula, to work with.

PAT.
I had to post the db with some data in it so that the prob. could be seen and even having stripped a lot of data it still couldn't be put up for public view. People on this board I would trust but it is open to the public.

Cheers

Pete

Normalisation

Registered User.

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Registered User.

Super Moderator

Back once again...

Registered User.

Similar threads

Users who are viewing this thread