Hi and thanks for your reply,
Just a bit of background to help explain what is going on here:
I started building this database with a few unbound text boxes on a form and this worked fine when it came to saving to the table. I set up an update query that referenced each unbound textbox on the form and the field to save to. So each field mapped directly to a textbox. I figured that I didn’t have to worry too much about normalisation as there were so few columns and not too many rows. However, over time the project grew and grew and before I knew it I had too many fields in the table/unbound textboxes and decided to normalise things a bit to speed up select queries underlying reports.
This did speed up my select queries considerably but meant a more complicated method of saving textbox values to the table as now the textboxes do not map to a single field but rather had to be identified by the combination of three fields, if you get my meaning.
For instance:
In the original scenario before normalisation let’s say I had a textbox named txt111RevenueAAA and I would save the value to a field named 111RevenueAAA. So each textbox had a corresponding field in the table based on textbox name, minus the prefix txt.
This was fine until I had more textboxes/fields than is practical.
I realised that I could reduce the number of columns in the table by adding FirstCategory and SecondCategory fields which would store the categories referenced in the textbox name, in the case of txt111RevenueAAA this would be 111 and AAA
FirstCategory could be any of: 111, 222, 333, 444, 555, 666
SecondCategory could be any of: AAA, BBB, CCC
So the total combinations of FirstCategory and SecondCategory would result in 18 rows. So for Revenue there were 18 textboxes defined by the two Categories. Hence, txt111RevenueAAA, txt111RevenueBBB, txt111RevenueCCC, txt222RevenueAAA etc etc. I deleted all 18 of the Revenue Fields in the table and ended up with just the one field named Revenue. When the user first saves a record in the parent table of this child table, my idea was to save all 18 rows in this child table and pre-populate the ForeignKey, FirstCategory and SecondCategory fields. Any future edits relating to that foreign key would just be an update of those 18 existing rows. So all 18 combinations are there in the table and the user will never get to update values in fields FirstCategory or SecondCategory, just the Revenue field and other similar fields.
The table does have an auto number primary key and a foreign key to relate it to it’s parent table.
There are also a couple of dozen fields in the table that are similar to Revenue in that they have both first and second categories.
Here is an example of what the table looks like with all 18 rows populated for one foreign key:
Code:
PrimKey|ForeignKey|FirstCategory|SecondCategory|Revenue|Cases
1 |265 |111 |AAA | |
2 |265 |111 |BBB | |
3 |265 |111 |CCC | |
4 |265 |222 |AAA | |
5 |265 |222 |BBB | |
6 |265 |222 |CCC | |
7 |265 |333 |AAA | |
8 |265 |333 |BBB | |
9 |265 |333 |CCC | |
10|265 |444 |AAA | |
11|265 |444 |BBB | |
12|265 |444 |CCC | |
13|265 |555 |AAA | |
14|265 |555 |BBB | |
15|265 |555 |CCC | |
16|265 |666 |AAA | |
17|265 |666 |BBB | |
18|265 |666 |CCC | |
So I was trying to find an easy way to update the correct column and row in the table for each textbox. Or should I be storing the data in a completely different way?
Thanks
Deutz