Extracting data from second table 2

[SMHSleepy]

Hello all, can someone please offer help with the following?

1. I have a tblMAIN table which contains, amongst others, the fields "StudyType" and "BillingCode". These are text fields which can be filled manually by data entry.
2. In order to facilitate data entry and reduce human error, I have created a second table called tblSTBC. This table only has two fields, "StudyType" and "BillingCode" and is filled with all current study types and corresponding billing codes. These study types and codes may change in the future.
3. I'd like to create a frmMAIN form for data entry which offers a drop down selection for "StudyType". The drop down will look up the values entered in my other table (tblSTBC). When I select the study type, it will copy the study type text into table tblMAIN:StudyType and also copy the corresponding billing code to table tblMAIN:BillingCode. If necessary, the user should have the ability to edit these fields afterwards.
4. In the future, if I make changes to table tblSTBC, this should not impact previous data on my tblMAIN table. The new values will just appear on the tblMAIN form drop down selection for StudyType and therefore all future records will have the new study types and billing codes.

Thank you in advance.

 

[misscrf]

see if this helps:

http://stackoverflow.com/questions/276737/normalizing-an-existing-ms-access-database

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[SMHSleepy]

Thank you but I need a little more direction. Also, it appears that the stackoverflow answer relates to linking tables, which is not what I want. I just want to use the second table to extract text to populate those 2 fields in my main table. I need a simple solution please, anyone?

 

[misscrf]

The article is explaining that normalizaton is how this is done. There is no easy way with Access dev. Only the right way and a big huge mess.

You need to make lookup tables and link the valus in. The values should not be in both tables. Anyone who tells you different is giving you a quick fix that will cost you in the long run.

Take my advice, clean it up now so you can have a good app going forward.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[SMHSleepy]

Thanks for the advice. In fact, it is exactly along the lines of what everyone has been telling me in every help forum I've tried. The thing is, by using this normalization method, if I make a change to the billing code and/or study type, it will change all previous entries to the new codes. The premise for creating this database is simply to replace a chronological patient log which is currently being done on a Word table. The Word table was created to replace a big notebook where we used to hand write the patient log. This line by line type of log is necessary. I can't have foreign keys in multiple tables linked to each other, etc. All the data has to be in a single MAIN table as described above. I understand this is not proper database design but could someone just humour me? Something like creating a combo box to select the StudyType, then add AfterUpdate code to place the corresponding BillingCode into the appropriate field in my MAIN table. I can almost "taste" it, but can't quite figure out how to do this.

 

[JoeAtWork]

Something like creating a combo box to select the StudyType, then add AfterUpdate code to place the corresponding BillingCode into the appropriate field in my MAIN table.

Actually, that's exactly how I would describe how to do it. I don't have Access on this machine so I don't want to try to write example code, but it would basically be:
1. In the AfterUpdate event of the ComboBox for StudyType, use a DLookup function to retrieve BillingCode.
2. Place the value you retrieved in the ComboBox for BillingCode.

As long as there's no other data that is dependent on StudyType or BillingCode, I don't see this design as breaking normalization rules (or at worst its a minor deviation from normalization that doesn't cause much harm).

 

[TheAceMan1]

How are ya SMHSleepy . . .

[blue] The thing is, by using this normalization method, if I make a change to the billing code and/or study type, [purple]it will change all previous entries to the new codes[/purple].[/blue]

Thats certainly real world reason, however ... just arbitrarily appending values to a table makes no sense. Appending during editing of a record does!

So on a form used to edit [blue]tblMain[/blue] we'll have an [purple]unbound[/purple] combobox based on [blue]tblSTBC[/blue], used to update the appropriate fields in the form.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Thanks Joe and Aceman, I forgot to update my own post. Finally figured it out using the combo box on exit code. I couldn't see the change on the form without restarting so I added a refresh command and all is just perfect now!