Autofill columns with like data & bringing in new data

[David J.]

2 Questions.

Question 1:

I have tblMain. In one of the columns, I have a reason code, which is pulled
from tblReasons. The next column is reason description, which is associated
to a reason code. When I select a reason code, how do I get the reason
description to autofill with the reason description associated with that
reason code from tblReasons?

Question 2:

This question is a little complex. Hopefully I can explain it correctly. I
will just show the part of the table necessary for this question. I have 2
columns in tblMain; Damage Code and Damage Description. These values are
pulled from a table called tblDamageReasons. The values already pulled to
populate tblMain and all of the other data are correct. However, I found out
the many of the other Damage Codes are not correct. I was given a new master
list with corrected codes and descriptions. I want to just delete the old
tblDamageReasons and fill it with the new tblDamageReasons. However, doing
this will null the fields in tblMain. I need the values to stay in tblMain as
is, but delete/replace all of the data in tblDamageReasons with the correct
values. How do I do this without corrupting tblMain's data?

tblMain:
Damage Code | Damage Description

 

[John W. Vinson]

2 Questions.

Question 1:

I have tblMain. In one of the columns, I have a reason code, which is pulled
from tblReasons. The next column is reason description, which is associated
to a reason code. When I select a reason code, how do I get the reason
description to autofill with the reason description associated with that
reason code from tblReasons?

You don't, at least you don't copy the description redundantly into your
tblMain. tblMain should contain ONLY the reason code. You're using a
relational database - use it relationally! If you want to *display* the
description on the form, you can use DLookUp, or simply include the
description in the combo's row source query and use a textbox with a control
source like

=comboboxname.Column(n)

where n is the zero based index of the description field.

Question 2:

This question is a little complex. Hopefully I can explain it correctly. I
will just show the part of the table necessary for this question. I have 2
columns in tblMain; Damage Code and Damage Description. These values are
pulled from a table called tblDamageReasons. The values already pulled to
populate tblMain and all of the other data are correct. However, I found out
the many of the other Damage Codes are not correct. I was given a new master
list with corrected codes and descriptions. I want to just delete the old
tblDamageReasons and fill it with the new tblDamageReasons. However, doing
this will null the fields in tblMain. I need the values to stay in tblMain as
is, but delete/replace all of the data in tblDamageReasons with the correct
values. How do I do this without corrupting tblMain's data?

The same would apply. Your tblMain should NOT contain the damage description
*AT ALL*, just the damage code. If you have the correct (after editing) damage
codes in tblMain, and the correct descriptions in tblDamageDescriptions, then
a Query joining the two tables will show the correct description.

Your very problem is an example of the reason why you should avoid storing the
same data (the damage description) redundantly in two tables! It makes
correcting errors vastly more difficult.

John W. Vinson [MVP]

 

[David J.]

Right, I understand about normalization and data redundancy. However, my only
function for this database is to maintain the data from a previous emloyee
who built it. I will eventually be redoing everything and putting it in SQL
Server. So, I guess I will just have to redesign an export macro for the
current reporting of the data. But that still leaves me with the issue of
Question 2.

Taking out the description part of that, I still need someway to transfer
over the codes, deleting all entries in the current tblDamageReasons that are
not currently used in tblMain, while importing and updating the damage
codes...all without compromising any data in tblMain. Sorry for the
questions, I don't really use Access much.

 

[John W. Vinson]

Right, I understand about normalization and data redundancy. However, my only
function for this database is to maintain the data from a previous emloyee
who built it. I will eventually be redoing everything and putting it in SQL
Server. So, I guess I will just have to redesign an export macro for the
current reporting of the data. But that still leaves me with the issue of
Question 2.

Taking out the description part of that, I still need someway to transfer
over the codes, deleting all entries in the current tblDamageReasons that are
not currently used in tblMain, while importing and updating the damage
codes...all without compromising any data in tblMain. Sorry for the
questions, I don't really use Access much.

Back up your database first just in case this doesn't work right, or I've
misunderstood.

You can delete all the unused records in tblDamageReasons with an unmatched
records:

DELETE tblDamageReasons.*
FROM tblDamageReasons LEFT JOIN tblMain
ON tblDamageReasons.[Damage Code] = tblMain.[Damage Code]
WHERE tblMain.[Damage Code] IS NULL;

Adding new damage codes would just be an Append query from the corrected
table.

Neither of these operations will affect tblMain in any respect.

How you're going to *update* the existing damage codes I have no idea - if you
have an incorrect damage code in tblMain, how will you be able to tell that
it's incorrect, and how will you be able to tell what it should be!? Or do you
want to keep the damage codes currently in tblMain, and just use new
descriptions?


John W. Vinson [MVP]

 

[David J.]

Thanks a bunch. That worked. Fortunately I have the green light to redesign
the database and will be moving it to SQL Server, so I can finally not have
to deal with problems like this. Thanks again.

Right, I understand about normalization and data redundancy. However, my only
function for this database is to maintain the data from a previous emloyee
who built it. I will eventually be redoing everything and putting it in SQL
Server. So, I guess I will just have to redesign an export macro for the
current reporting of the data. But that still leaves me with the issue of
Question 2.

Taking out the description part of that, I still need someway to transfer
over the codes, deleting all entries in the current tblDamageReasons that are
not currently used in tblMain, while importing and updating the damage
codes...all without compromising any data in tblMain. Sorry for the
questions, I don't really use Access much.

Back up your database first just in case this doesn't work right, or I've
misunderstood.

You can delete all the unused records in tblDamageReasons with an unmatched
records:

DELETE tblDamageReasons.*
FROM tblDamageReasons LEFT JOIN tblMain
ON tblDamageReasons.[Damage Code] = tblMain.[Damage Code]
WHERE tblMain.[Damage Code] IS NULL;

Adding new damage codes would just be an Append query from the corrected
table.

Neither of these operations will affect tblMain in any respect.

How you're going to *update* the existing damage codes I have no idea - if you
have an incorrect damage code in tblMain, how will you be able to tell that
it's incorrect, and how will you be able to tell what it should be!? Or do you
want to keep the damage codes currently in tblMain, and just use new
descriptions?


John W. Vinson [MVP]