Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple query, but I seem to be having some trouble. My table
consists of over 100,000 records. Each record has a DL field, and I want to
update these records to have another field called DL4 which takes the DL
field truncates it down and inserts those last 4 into the DL4 field. I tried
to run an append query then update the field, but when I do that all it does
is double the number of records in my table. I'm thinking there is something
I'm leaving out. Any help would be great.

Thanks
 
Jed said:
I have a simple query, but I seem to be having some trouble. My table
consists of over 100,000 records. Each record has a DL field, and I want to
update these records to have another field called DL4 which takes the DL
field truncates it down and inserts those last 4 into the DL4 field. I tried
to run an append query then update the field, but when I do that all it does
is double the number of records in my table. I'm thinking there is something
I'm leaving out. Any help would be great.

Thanks
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... you don't update a row (record) to have "another field." You
update existing columns (fields). So, I'm assuming you have a table w/
2 columns: DL and DL4. You want to put the last 4 characters of the
value in column DL into column DL4. Correct? To update a table you run
an UPDATE query not an append (INSERT) query. Therefore,

UPDATE table_name
SET DL4 = Right(DL,4)

Substitute your table's name for "table_name."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4CDoechKqOuFEgEQI9LQCeK0HhyDEyoGQ0zNAH4wVahXp6SQ8AoP2g
apqG94bLmz5+OMcdVmj8R2rB
=6fcr
-----END PGP SIGNATURE-----
 
I have a simple query, but I seem to be having some trouble. My table
consists of over 100,000 records. Each record has a DL field, and I want to
update these records to have another field called DL4 which takes the DL
field truncates it down and inserts those last 4 into the DL4 field. I tried
to run an append query then update the field, but when I do that all it does
is double the number of records in my table. I'm thinking there is something
I'm leaving out. Any help would be great.

Thanks
Click to expand...

That's what an Append query is designed to do: to *append* new records
into an existing table. It's neither necessary nor appropriate in this
case.

For that matter, your DL4 field *SHOULD NOT EXIST*. Since it can be
calculated whenever you wish from the DL field, simply store the DL
field in your Table and calculate the DL4 field whenever it's needed,
in a Query with a calculated field:

DL4: Right([DL], 4)

This field can then be printed, searched, sorted, viewed, whatever you
wish - anything except edited, which you wouldn't want to be able to
do anyway.

John W. Vinson[MVP]
 
Back
Top