update all records in table

[APINDARA]

I have a table that contains 80,000 records and want to add a field that I
can use to note year and location of receipt of record.

Something like 2004AB or 2007APR

Kindof a neophite with Update queries and cant figure out how to make this
relatively simple thing happen.

thanks in advance!

 

[Klatuu]

It is a good thing you have not gotten it done yet.
Now, you can get it done correctly.
The problem is you should be using two fields. One for the year and one for
the location. They are separate pieces of data. When you combine them, you
seriously reduce your ability to find specific information. In addition,
your queries will become more complex with the two items combined.

The YearReceived field should be a Long Integer numberic field and the
RecordLocation field should be a text field.

As to your Update query question, how will you know what values to put in
what records?

 

[Michel Walsh]

You need a data definition language statement, or, much simpler, use the
table design and add a column to your table.

An update query can only modify values. It does not change the structure of
the table(s).


Vanderghast, Access MVP

 

[APINDARA]

Each record has a segment code that I can see what season the name came from.

It is a good thing you have not gotten it done yet.
Now, you can get it done correctly.
The problem is you should be using two fields. One for the year and one for
the location. They are separate pieces of data. When you combine them, you
seriously reduce your ability to find specific information. In addition,
your queries will become more complex with the two items combined.

The YearReceived field should be a Long Integer numberic field and the
RecordLocation field should be a text field.

As to your Update query question, how will you know what values to put in
what records?

I have a table that contains 80,000 records and want to add a field that I
can use to note year and location of receipt of record.

[quoted text clipped - 5 lines]

thanks in advance!

 

[Klatuu]

Describe what is in the segment code, please
--
Dave Hargis, Microsoft Access MVP

Each record has a segment code that I can see what season the name came from.

It is a good thing you have not gotten it done yet.
Now, you can get it done correctly.
The problem is you should be using two fields. One for the year and one for
the location. They are separate pieces of data. When you combine them, you
seriously reduce your ability to find specific information. In addition,
your queries will become more complex with the two items combined.

The YearReceived field should be a Long Integer numberic field and the
RecordLocation field should be a text field.

As to your Update query question, how will you know what values to put in
what records?

I have a table that contains 80,000 records and want to add a field that I
can use to note year and location of receipt of record.

[quoted text clipped - 5 lines]

thanks in advance!

 

[APINDARA via AccessMonster.com]

I like the idea of adding a column to the table. Sounds very simple!

How do I populate each record with the same value? Will it differ if I use a
numeric value or a text value?

You need a data definition language statement, or, much simpler, use the
table design and add a column to your table.

An update query can only modify values. It does not change the structure of
the table(s).

Vanderghast, Access MVP

I have a table that contains 80,000 records and want to add a field that I
can use to note year and location of receipt of record.

[quoted text clipped - 5 lines]

thanks in advance!

 

[Michel Walsh]

UPDATE tableName SET columnName = constant


The difference between the field being numerical, literal, or a date_time
will be about the constant itself:

3.5
"red"
and
#12-31-1999 15:35:22#


are example of the constants.



Note that since the UPDATE statement has NO where clause ALL records are so
updated. Adding a where clause will restrict the updated records to those
satisfying the criteria of the where clause.


Vanderghast, Access MVP

I like the idea of adding a column to the table. Sounds very simple!

How do I populate each record with the same value? Will it differ if I
use a
numeric value or a text value?

You need a data definition language statement, or, much simpler, use the
table design and add a column to your table.

An update query can only modify values. It does not change the structure
of
the table(s).

Vanderghast, Access MVP

I have a table that contains 80,000 records and want to add a field that
I
can use to note year and location of receipt of record.

[quoted text clipped - 5 lines]

thanks in advance!