SIMPLEST dumbbum question excel vs access

[beach]

Someone PLEASE just confirm that you CANNOT have a column in an access table that simply just calculates the product (or sum, or whatever) of the previous two columns (or some other two columns). on a similar principle to excel (add a column, insert formula C = A*B), copy down etc etc)

I am a access DUMMY, and have convinced myself you have to do a separate query thingy and then link back to the table you want to see the anser in.

Am I correct ??

 

[Ian Mac]

Hello,

You would generally do a separte query, but you don't need to link it back to the original table, the data is already there, the query becomes your source for outputting/viewing etc.
That's the way to use Access,

And BTW you're not being dum, Access does work in a different way are learning can be difficult esp. coming from an Excel setting.

Cheers,

 

[mdmilner]

A little more clearly.

No, you can not have a field in a given table dynamically calculate a value based on the contents of other fields in the same table. What access does instead is have queries.

Queries are extremely versatile objects. They have most of the capabiliities (properties) of tables, plus more. What Ian was trying to say is this. Start with your table, then create a query based on the fields you need. And then create a calculated field for your query that calculates your value. You can then use that query everywhere you would otherwise think you *had* to use for a table.

You can export it (to excel?), or use it for a form/report recordsource. You can also base other queries off it. The latter is a favorite technique of mine since it's frequently mandatory to create multiple queries based on each other when there are multiple joins involved...in order to use Crosstab queries.

And the fun thing about Access & Queries is - it's easy once you learn how. You enter a very short SQL statement and whether you have 1 record or 1 Million records. The SQL statement is the same size. No more autofilling down the entire spreadsheet to copy a formula (and it's limited to ~65,000 rows anyways)

SELECT fld1, fld2, (fld1*fld2) as fld3 FROM tblname

Would show you the first two fields and the product of them as if it was the 3rd field visible.

Mike