Normalisation

[Parkroyal]

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 in "tblHomeAddress" 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 the sake of clarity we will call this record "spoof&quot

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.

 

[BSman]

Be sure you don't have a relationship defined that demands a match between the addressID in Table1 and tbleHomeAddress. (I'm assuming there is an ID column called addressID that is the primary key in tbleHomeAddress to link the two tables.)

Just set addressID to null for all records in Table1 where addressID equals the value of the "spoof" record in tbleHomeAddress. You then will have to set up your data entry so a new address can be entered into tbleHomeAddress.

But I have a question about this split of data. Do multiple records in Table1 have the same home address? If not, I would seriously consider combining the table tables back together. The reason for creating a separate table for a set of values is to reduce duplication of data. So you might have a table for "City" with a CityID (primary key number) and save the primary key in Table1 (or, in your case, tbleHomeAddress), since there are probably multiple entries with the same city name. But I would be surprised if you have many (or even any) duplicate home addresses. Thus, combining them back into one table makes sense (and does not de-normalize the database).

 

[Parkroyal]

Thank you BSman for your reply.

This is what is writen in the lookup.
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];

Can't see owt wrong there.

Yes I have come to the conclusion that the fault may lie with the form that is used to input the data. I can add anew record but it messes up if I try to eddit an existing record.

Normalisation: Point taken. There are instances where some values from the table "tblHomeAddress" are used many times and that was one reason for splitting off this particular information into another table. But this is also happening with other tables and lookups that where created at the same time that are more justified ---- I just used this one as an example rather than repeating the same problem in a different description.
One of the requirements for 1NF is to remove all repeating groups -- this and others where repeating groups. Another reason for this particular table is to make the origonal table smaller for efficiency.

Thanks for your help. Maybe I should look at the form that is used to edit/add records