Apply something to every record

A

Alex

I need help with data entry... I have a table with about 8000 records, and I
have a field which has a number, and another field which has the name for a
person and a another field for the county they live in... Now the number
corresponds to the county, and since about 80% of the table doesnt have the
county entry, only the number, I wish to know if there is a way for me to
write some code which will permit me to put the name of the county into its
place, by having the code read the number, then compare it against a list
inside the code, then write the name of the county to the county field...

The only way I know how to do this is by using a form, then putting the
appropiate code into the On Current event, then just click on the arrow and
scroll through all of the records... however if there is an easier way (i
think it involves using recordset, except I dont know how to use it) could
someone please tell me....

I have acess XP
 
J

James Hahn

You might be approaching this the wrong way. If I had to do this I would
create a new table that contained the number and the county name. Then, in
any report or form where I was interested in this information, I would put a
lookup to that table and display the county name where the county number
would normally appear. I wouldn't have a county name field in the primary
table at all. And I would avoid any solution that had the names of the
counties embedded in code, as it is almost certain that at some stage in the
future they will change.
 
D

Dirk Goldgar

Alex said:
I need help with data entry... I have a table with about 8000
records, and I have a field which has a number, and another field
which has the name for a person and a another field for the county
they live in... Now the number corresponds to the county, and since
about 80% of the table doesnt have the county entry, only the number,
I wish to know if there is a way for me to write some code which will
permit me to put the name of the county into its place, by having the
code read the number, then compare it against a list inside the code,
then write the name of the county to the county field...

The only way I know how to do this is by using a form, then putting
the appropiate code into the On Current event, then just click on the
arrow and scroll through all of the records... however if there is an
easier way (i think it involves using recordset, except I dont know
how to use it) could someone please tell me....

I have acess XP
Click to expand...

There is a *much* easier way, but before doing it, consider this: do
you actually need to have both the county number and the county name in
this table? It's a principle of relational database design that you
don't store redundant information. Although I don't know any more about
your database than you've told me, it sounds like you ought to have two
tables: a table of counties containing the fields CountyNumber (or
CountyID) and CountyName, and your original table containing all its
original fields (including CountyNumber) except for CountyName.

The tables would be in a one-to-many relationship: for each record in
the Counties table, there would be some number (0 or more) records in
the original table with the matching CountyNumber. Any time you need to
see the CountyName corresponding to the CountyNumber, you can use a
query that joins the two tables on CountyNumber to look it up. On a
form, you would select the county for each record from a combo box based
on the Counties table, bound to the CountyNumber field but displaying
the CountyName.

Does that make any sense to you? You can certainly leave your table
design as it is, and run an update query to make the CountyName field
correspond to the CountyNumber field. But there will be nothing to keep
the two fields in sync, and no way to ensure that the table won't get
messed up again. If you change the structure as I described, it will be
impossible for the fields to get out of sync, because you will only be
storing the CountyNumber in the detail table.
 
A

Alex

I'd really rather do it this way... besides I'll get the added advantage of
learning how to use recordsets...
thanks though...
 
L

Larry Daugherty

Hi Alex,

If that number is unique to each country then you should have a table
tblCountry with the country name and the number associated in the same
record. Then you would only store the Number of the country in the other
table and simply look up the country name to which the number refers. Data
entry would go the same way, You'd choose the country name from a combobox
and store the number rather than the name in the underlying record.

HTH
 
A

Alex

for know I'd rather keep this design... I will probably update it the way you
suggested, but for now... for ease of use, and taking into account that by
doing this I will probably have to change quite a bit of stuff on the
database, if there is a simple way to do this, without changing db structure,
maybe what you suggested with the update query...

How do you use an update query to accomplish this??

And if not is there another way to do it by using VB??

Again, thanks for everything...
 
D

Dirk Goldgar

Alex said:
for know I'd rather keep this design... I will probably update it the
way you suggested, but for now... for ease of use, and taking into
account that by doing this I will probably have to change quite a bit
of stuff on the database, if there is a simple way to do this,
without changing db structure, maybe what you suggested with the
update query...

How do you use an update query to accomplish this??

And if not is there another way to do it by using VB??

Again, thanks for everything...
Click to expand...

Your first step either way is to create a table of counties (unless you
already have one), whether you later modify the structure of your
original table or not. I don't know the names of your table or its
fields, so you're going to have to look at the SQL I suggest and modify
it appropriately.

First define a table of Counties with field like this:

Counties
---------
CountyID (Type: Number, Size: Long Integer)
CountyName (Type: Text, Size: 255)

Make CountyID the primary key of this (currently empty) table.

Now execute a query with SQL like this

INSERT INTO Counties
SELECT DISTINCT
CountyNumber, CountyName
FROM OriginalTable
WHERE CountyNumber Is Not Null
AND CountyName Is Not Null;

You may find that some records are rejected due to key violations. That
would mean that the same CountyNumber exists in OriginalTable with
different CountyName values associated with it. In that case, you'd
need to figure out what should be the correct CountyName for that
CountyNumber.

Having created and loaded table Counties, now you can use it in an
update query to fix the CountyName field in OriginalTable. That update
query might look like this:

UPDATE
OriginalTable
INNER JOIN
Counties
ON OriginalTable.CountyNumber = Counties.CountyID
SET OriginalTable.CountyName = Counties.CountyName;

If you run that query, the CountyName in each record of OriginalTable
will be set equal to the CountyName in the matching record in Counties.
 
Top