Filling a Field

  • Thread starter Thread starter JCJewels
  • Start date Start date
J

JCJewels

Hello,

I'm trying to find out how to fill a field/column in access after a record has
been created. I have over 10,000 records and I need a couple of fields added
to the database and I need the same data these fields for every
record--similarly called the fill function in spreadsheet programs.

Any help is appreciated. Thanks.
 
The general answer is use an Update query (start like making a plain select
query, and change it to an Update query from the menu: Query > Update
Query).

The unavoidable question, though, is, if it's going to be the same value for
each and every record in the table, then why store it in every record? There
may be a better way, if you care to explain what you are trying to achieve.

HTH,
Nikos
 
Thanks so much. The Update Query is exactly what I needed. I need to export
my database to csv format for upload to a mainframe system.

Maybe you can help me out with another problem. I have a field with data in
it, it's different for every record, i need to edit each record and put the
SAME wording at the beginning of the field insert the EXISTING field contents
and then add the SAME wording at the end of the field.

Thanks again for any help.
 
You can also do this in an Update query. Assuming that the field name is FieldName and you want to put "SAME" at the beginning of each record, update the field to:

"SAME" & [FieldName]

You should have a look at the results in a SELECT query first, because if you get it a bit wrong the first time, your next update will have to take that into account.

For example, say you ran the above query and it gave you values in different records like "SAMEArthur", "SAMEBob" and "SAMECharles", and you actually wanted a space between them eg "SAME Arthur". If you just changed your update query to:

"SAME " & [FieldName]

You'd get "SAME SAMEArthur", "SAME SAMEBob" and "SAME SAMECharles".

So you'd need to get rid of your erroneous text. You have a whole bunch of options here, all related to string handling functions::

"SAME " & Right([FieldName], Len([FieldName])-4)
"SAME " & Mid([FieldName], 5, Len([FieldName]))
Left([FieldName], 4) & " " & Right([FieldName], Len([FieldName])-4)

etc etc

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/7098
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=1ef11053a39740e885835c52699efb2d
*****************************************
 
On your new question, I believe David's answer is all you need.

On the first question: if all you need is to export a flat table to .csv
there is absolutely no reason why you should store a constant in the table.
Make a plain select query on the table, add all the fields you need to
export to the grid, and just add a calculated field like:

FieldName: "ConstantValue"

Then export this query instead of the original table and the job is done
nicely, without storing redundant data. The exact same technique can be used
for your second question. Again, no need to store, just a calculated field
in the export query like:

FieldName: "Leading Text " & [FieldName] & "Trailing Text"

HTH,
Nikos
 
Thanks for all the help. It was very helpful. I couldn't use the last idea
though. I'm exporting a memo field that is larger than 255 characters and I
can't find a spreadsheet program that will read fields bigger than 255.
 
Back
Top