Can I calculate fields in Access like in Excel

[Dave Nelson]

I have a database where I would like one field to be the result of another
field. I can do this easily in Excel, I have tried to find it in Access
help, to no avail.

For example in Excel:
=month(C1) would be a formula to return the value of the month where c1 was
a date value.

Thanks,
Dave

 

[Douglas J. Steele]

What you're trying to do is a violation of database normalization principles
(having one field strictly dependent on the value of another field in the
same row), so Access doesn't support it.

What you can do is create a query, and put your calculated field in the
query. Once you've done that, use the query wherever you would otherwise
have used the table.

 

[John Vinson]

I have a database where I would like one field to be the result of another
field. I can do this easily in Excel, I have tried to find it in Access
help, to no avail.

For example in Excel:
=month(C1) would be a formula to return the value of the month where c1 was
a date value.

Excel is a spreadsheet, a very good one.

Access is a relational database.

THEY ARE DIFFERENT. You can drive nails with a crescent wrench, but
that doesn't make it a hammer!

Stop, step back, and prepare to do a fairly significant mental
reorientation. Read up about "Database Design" and "Normalization",
and plan to use Access as it is designed.

The direct answer to your question is that you can't and shouldn't do
this in a Table, but it's perfectly easy in a Query; simply create a
Query based on your table and type

ShowMonth: Month([datefield])

in a vacant Field cell. When you open this query as a datasheet, or
(better) open a Form or Report based on the query, you'll see the
month number.

 

[MaWa]

Calculated fields as an option to data input

Hi

I am an Access user with basic knowledge and have a situation in an Access table where I forecast salary expense based on individuals' pay rates. Everyone gets the same percentage pay increase onthe same date, so I can calculate the new pay rate in a query. However, I also have the situation where new staff start after the pay increase has been implemented and want to be able to enter the person's salary rate directly in to their newly created table record.

I think this is one situation where it makes sense to have a calculated field for the new pay rate in the table and overtype it with the actual rate for those people who start after the pay increase occurs.

If you agree, how do you create a calculated field in the table. Is this through an update query?

Hope you can help

Thanks
MaWa