Database not completely normalized

[cuddles]

I have a database that is somewhat normalized and I tried to normalize it, however when doing so it corrupted the data entry forms. This is because my database has 1 main table and then other tables with ID's like state table has st_id and st_cd. If I leave my main table linking to the tables by all the ID's and then pull like:

maintable

MstrID St_ID YR_ID

statetable
st_id
st_cd

year_table
yr_id
yr

Link these so that it links like IDs should and say ok now on my form for data entry update, I want to update only the maintable with information. I can create a query to link everything together just fine and then select in my unbound table that opens the data entry form what I want and the end user selects and edits and all looks fine..................however..................if they select say item 4 of the maintable, and click edit and change the record to YR = 2013 and ST_CD = NY, then it updates the statetable and the yeartable to that information. So, this is why I created the table with the main containing YR, STCD because that is the only way it update only the main table and not the tables that fed the main table. If there is another way please let me know and I can normalize, however for my purposes what I have works and have spent 2 months on it database, forms and all and when attempting to normalize it screwed the whole forms system up. And the editing and the data entry. Thankfully I had a backup and went back to it. Can someone please advise as to why the feeding tables would update when main is edited or new entry put in?

Example:

When edited for NY, NY used to be in my table distinct ST_ID number 13, now it is number 13 and number 2, but number 2 used to be CT.

[June7]

I am thinking you have not bound textboxes to St_ID and YR_ID fields from maintable.

Personally, I would not use the ID fields. I would just save the st_cd and yr values.

[cuddles]

I tried that but then the DB had errors when attempting to publish. My data entry form has information where they can search first and if they find the record they click on it and then they can edit it in a subform. The subform is where they select from comboboxes what they want to update. They need to see the actual items. Not an ID or something like that. The data entry has this:

control source=St_CD
row source=select st_cd, st_id from state;

Then the column count is 2 and width 1;0; there is a textbox hidden to house the st_id that is the 0 so I know that the ID is updating to the correct ID from state table. The problem is if I change all this row source etc, then they cannot select from a list of states already housed. They need to be able to do that. select from this drop-down but also free form text in and have it find what they are looking for. So, this is why I stored some values in a main table becaase this is the only way I know how to do it. I hate data entry crap. All the other stuff would run perfect if normalized but the data entry does not. Nor the update unless I do it this way because like I said I know no other way and time is a factor.

[June7]

What do you mean by 'publish'?

Select from a list of states 'already housed' where?

Data entry is simply selecting item from combobox list and value is saved to field, regardless if it is st_id or st_cd.

If you want to allow users to enter a st_cd value to use for search/filter criteria, that must be with an UNBOUND control. Has nothing to do with normalization.

[cuddles]

My problem is when I normalize the database, meaning only primary keys in the main table, and create a data entry form and update form, when the end-user selects from the long list of combo options, the linked tables/relationships change information. Main is the main table joined to many other tables. They are joined by primary key. Because I have a query on this data entry from so I can pull in description related data because they are not going to know what st_id, prog_id, prod_id, contact_id etc etc necessarily are, they need to see contact_nm, st_cd, prog_nm so they select from the lengthy list of those and when it updates the info to the main table it will change what is in the connected or linked or relationship tables. For instance state table is below and after testing a data entry, NY was not only listed after I updated my entry in the data entry to NY, but then it changed CT to NY as well.

State table:

ST_ID	ST_CD
1	CA
2	CO
3	CT
4	FL
5	GA
6	IN
7	KY
8	MD
9	ME
10	MO
11	NH
12	NJ
13	NM
14	NV
15	NY
16	OH
17	TN
18	TX
19	VA
20	WA
21	WI

[June7]

Normalization does not require use of numeric ID. If you already have working structure why do you want to change it? For such short data values, I would not bother with ID fields.

The year can still be in a number field. As for indexing with the state code vs. state ID, I don't think there would be much performance difference.