How to create data in one field based on another field?

[don911no]

How do I automatically create data in one field based on another field? For example, in field 1, I have entered a value in kilos. I now want field 2 to automatically show the value of field 1 but in pounds.

I'm using Access 2002 on Windows XP.

[John_G]

Where are you wanting to do this? A table? A form?

Please provide more details.

[don911no]

Hi John_G, thanks for your quick reply!

I'm using a table. I'm editing in Design View and viewing and working with the data in Datasheet View.

[John_G]

For a table, I suggest that you keep only one weight value in the table; when you need the other value(s), use queries. In A2002, you can't automatically populate one table field based on values in other fields.

A2010 (maybe A2007, too) does have calculated fields, but IMO they should be avoided - as I said, that's what queries are for.

You really should be using forms for data entry, instead of entering data directly into tables. Forms provide a lot more features in the way of data checking and validation than tables do.

[don911no]

OK. So how would I go about if I wanted to use a query to populate values in field 2 using the value in field 1 x 2.2 (for kilo to pounds conversion)? I have 7000 records in my table. And please answer like I'm 5 years old. :-) My knowledge of Access, programming and SQL etc. is very limited.

[John_G]

To just display the conversions without actually storing the values in a table, use a Select query. Include all the fields you need from your table, plus a calculated query field -

Pounds: [Kilos] * 2.2 , where Pounds is the column header when the query is run.

If you really do want to keep the Pounds value in the table, and you have a table field set aside for it, then you use an Update query. In query design view, add the table to the design. You only need the two fields, assume they are Kilos and Pounds. Change the Query type to Update, and in the "Update To" box in the Pounds column, put [Kilos] * 2.2 .

When you run the query, and the Pounds fields will be populated.

[don911no]

Hi John, I successfully followed your instructions to add a new field with Pounds to my table with an update query. Thank you very much!

Also, perhaps you can help me with another issue that has puzzled me for a long time. I want a field in my Table (table view) to display a numeric value with one decimal, regardless of how many decimals is put in. However, I can't get it to work. When the field is set to 'Number' and filed size to 'Single' with 1 decimal, the value is still shown with as many decimals as written in, i.e. if I write 155.565, then that's what is shown. I want it to show 155.6 when I type in 155.565. And if I choose 'Integer' with 1 decimal, then 155.565 becomes 156. Any hep would be greatly appreciated.

[John_G]

The internal storage format of a number is always the same for any specific data type, regardless of how many decimal places you want to show. You use the Format property of a field to specify how you want to to be displayed.

However, as a general rule, you don't set formats directly on table fields, because you shouldn't enter or display data by opening tables directly. Use forms to enter or update data, and queries or reports to display it. Queries, reports and forms all have format properties on controls and fields.