Can an Access Database do calculations?

[malewis48]

We need to create a large database with lots of calculations. I don't know
if Access will do calculations and if it does how to set it up.

 

[Jeff Boyce]

Access is not a spreadsheet. If you need to do lots of calculations, have
you looked at using a spreadsheet instead?

That said, you can create expressions in Access queries and code that will
do calculations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Wayne-I-M]

Hi

Yes access can do some extrelemly compex calculations.

Basically you will store the data in a table and then use querys, form and
reports to do the calculations in.

Of course there is asteep learning curve with access and it does take some
practice to get the hang of it but it's not too hard. You can always post a
question here if you a stuck on a specific question or topic. There are many
people with the letters MVP after thier name who will give very details
answers to most questions regarding access.

So Yes access can do calculations BUT it is NOT imple another version of
excel.

Here is a link (there are MANY) that lists a few of the main difference
between excel and access.

http://www.tek-tips.com/faqs.cfm?fid=4547

Hope this helps

 

[RayV]

We need to create a large database with lots of calculations. I don't know
if Access will do calculations and if it does how to set it up.

As already said it can do them but you need to be very careful.
Access can give unexpected results when Null values are involved and
doesn't do 'simple' calculations like Median without code.

 

[James A. Fortune]

As already said it can do them but you need to be very careful.
Access can give unexpected results when Null values are involved and
doesn't do 'simple' calculations like Median without code.

I agree with your point in general but not in particular:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/b24f513ff5abf10a

I had to get a little creative to accomplish that, but I only use SQL
for medians. The link in that post points to an example where a median
is calculated for a result set that is not grouped.

James A. Fortune
[email protected]

 

[Mark]

How come in some formulas in a query that uses fields from that query
(similiar to your example) when you run the query, it keeps asking prompting
for the parameter value, even though I've specified the name of the fields
being calculated by using []. When this happens, I just hit enter and it
calcs correctly, but I'm not sure why I'm being prompted this.

Thanks....

We need to create a large database with lots of calculations. I don't know
if Access will do calculations and if it does how to set it up.

Yes it can do calculations, but not in a table, nor as a rule, should
calculations be stored in a table.

In an unbound control on a form or report:
=[FieldA] * [FieldB] / [FieldC]

In a query...
[SomeAmount]:[FieldA] * [FieldB] / [FieldC]

If you need more help, you'll have to give more information.

 

[Bill Mosca]

Mark

When you get prompted for a field's value, 9 times out of 10 it means you
spelled it wrong or you thought it was in the query, but it's not.

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals

How come in some formulas in a query that uses fields from that query
(similiar to your example) when you run the query, it keeps asking
prompting
for the parameter value, even though I've specified the name of the fields
being calculated by using []. When this happens, I just hit enter and it
calcs correctly, but I'm not sure why I'm being prompted this.

Thanks....

We need to create a large database with lots of calculations. I don't
know
if Access will do calculations and if it does how to set it up.

Yes it can do calculations, but not in a table, nor as a rule, should
calculations be stored in a table.

In an unbound control on a form or report:
=[FieldA] * [FieldB] / [FieldC]

In a query...
[SomeAmount]:[FieldA] * [FieldB] / [FieldC]

If you need more help, you'll have to give more information.

 

[Mark]

Hmmmm....I even went to the expression builder and selected the fields to
avoid mis-spelling. I'll try again.

Mark

When you get prompted for a field's value, 9 times out of 10 it means you
spelled it wrong or you thought it was in the query, but it's not.

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals

How come in some formulas in a query that uses fields from that query
(similiar to your example) when you run the query, it keeps asking
prompting
for the parameter value, even though I've specified the name of the fields
being calculated by using []. When this happens, I just hit enter and it
calcs correctly, but I'm not sure why I'm being prompted this.

Thanks....

On Thu, 1 Feb 2007 11:31:00 -0800, malewis48 wrote:

We need to create a large database with lots of calculations. I don't
know
if Access will do calculations and if it does how to set it up.

Yes it can do calculations, but not in a table, nor as a rule, should
calculations be stored in a table.

In an unbound control on a form or report:
=[FieldA] * [FieldB] / [FieldC]

In a query...
[SomeAmount]:[FieldA] * [FieldB] / [FieldC]

If you need more help, you'll have to give more information.

 

[Clif McIrvin]

How come in some formulas in a query that uses fields from that query
(similiar to your example) when you run the query, it keeps asking
prompting
for the parameter value, even though I've specified the name of the
fields
being calculated by using []. When this happens, I just hit enter and
it
calcs correctly, but I'm not sure why I'm being prompted this.

I seem to remember having trouble if I refered to a calculated value
*BEFORE* the calculation was done. Seems like I solved that by either 1)
making certain that all my calculated values were before (to the left in
the Query design grid) any references to those calculations or 2)
Nesting queries. Write a simple select query (Name it something like:
"DataThatIwantSelect" )that defines all your calculated values, then use
that query as your recordsource for the query (name this one
"DataThatIwant") that generates the recordset you actually want.

I have processes that have queries nested three deep .... it sure has
simplified my query writing from trying to do everything in one complex
Query!

HTH
ymmv
--
Clif
Still learning Access 2003

Thanks....

We need to create a large database with lots of calculations. I
don't know
if Access will do calculations and if it does how to set it up.

Yes it can do calculations, but not in a table, nor as a rule, should
calculations be stored in a table.

In an unbound control on a form or report:
=[FieldA] * [FieldB] / [FieldC]

In a query...
[SomeAmount]:[FieldA] * [FieldB] / [FieldC]

If you need more help, you'll have to give more information.