Calculating in Access 2003 like calculations in Excel 2003

[Guest]

Is there a magic switch/command to Calculate in Access 2003 like calculations
are done in Excel 2003 (or any normal desktop Calculator)? I am a novice, so
please don't get too techie. A simple example would also be helpful, if
answer is yes. I am doing these calculations in a query and then using a
report to consolidate the data. When I take the report and export the data to
Excel 2003, and then add calculations off the detail data, I can get down to
the penny. But the query fields doing the same math are high or higher if I
round in the query and then reported in Access. If the answer is going to be
no, and even no in Access 2007. Then I need to ask if there is a way to run a
query/update that would take data from Access and give it to Excel, and let
Excel do the calculations, and then pass the down to the penny answers back
to Access and update the proper fields in my table. I need to save my results
and the factors for historical purposes, as rates/factors change daily. These
are $ calculations that have factors such as .036295 (3.6295%).
Thanks for any help,
Go_Bears

 

[Joseph Meehan]

Is there a magic switch/command to Calculate in Access 2003 like
calculations are done in Excel 2003 (or any normal desktop
Calculator)? I am a novice, so please don't get too techie. A simple
example would also be helpful, if answer is yes. I am doing these
calculations in a query and then using a report to consolidate the
data. When I take the report and export the data to Excel 2003, and
then add calculations off the detail data, I can get down to the
penny. But the query fields doing the same math are high or higher if
I round in the query and then reported in Access. If the answer is
going to be no, and even no in Access 2007. Then I need to ask if
there is a way to run a query/update that would take data from Access
and give it to Excel, and let Excel do the calculations, and then
pass the down to the penny answers back to Access and update the
proper fields in my table. I need to save my results and the factors
for historical purposes, as rates/factors change daily. These are $
calculations that have factors such as .036295 (3.6295%).
Thanks for any help,
Go_Bears

If I understand that you want, you can do it in Access. Access has a
number of ways of rounding and factors involved. Sorry, it seems I have
lost my link to a rounding tutorial for Access. I am sure someone will
offer you some specific help or you can try Google.

 

[Guest]

Joseph, Thank you for your reply. I have tried to use the rounding
function. But that doesn't get things down to the penny, as Excel will do
without using the rounding function in Excel. It would seem like Microsoft
could give Access the option to do calculations just like Excel or give
Access the option of passing data fields to a subroutine, and running this
subroutine in Excel to do calculations and then return data fields to Access
for report writing and storage (maybe they could call them calculated data
fields?). Though I still feel process and answers of 1 + 1 or 1*1 or 1/1
should be the same in Access or Excel. If Excel has the better engine to do
Calculations, then give Access the means to call out to Excel and use the
Excel engine and return the results to Access.
There is nothing worse than a Information Systems report in Microsoft
Access showing different numbers than the accounting departments Excel
report. Management doesn't want to understand about rounding, they want the
numbers to match/balance.

So I will keep on praying that there is a answer or something in the new
release of access to resolve this.

Also praying for the Bears, hope we both can accomplish our challenges!

Thanks again.

 

[Joseph Meehan]

Joseph, Thank you for your reply. I have tried to use the rounding
function. But that doesn't get things down to the penny, as Excel
will do without using the rounding function in Excel. It would seem
like Microsoft could give Access the option to do calculations just
like Excel or give Access the option of passing data fields to a
subroutine, and running this subroutine in Excel to do calculations
and then return data fields to Access for report writing and storage
(maybe they could call them calculated data fields?). Though I still
feel process and answers of 1 + 1 or 1*1 or 1/1 should be the same
in Access or Excel. If Excel has the better engine to do
Calculations, then give Access the means to call out to Excel and use
the Excel engine and return the results to Access.
There is nothing worse than a Information Systems report in Microsoft
Access showing different numbers than the accounting departments Excel
report. Management doesn't want to understand about rounding, they
want the numbers to match/balance.

So I will keep on praying that there is a answer or something in the
new release of access to resolve this.

Also praying for the Bears, hope we both can accomplish our
challenges!

There are a large number of ways to round numbers. I doubt that Excel
has more than Access. The problem is when I was working, I decided what
rounding was "correct" so if the people were using Excel and their answer
was different, that was too bad. Sorry to say the point is, I never
really spent any time researching it at the time. I have seen some
references to the Access rounding options, but I can't recall where.

If you don't get an answer in 24 hours, you can try reposting it, maybe
asking about "rounding options in Access."

You also might do a search of existing subjects for "rounding."

Here is one:

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

There are quite a bit of information on the subject.

BTW, you are not using integer filed type are you?

 

[Guest]

Joseph, Thank you for your reply. I checked the link you included. I have
seen it before and agree with it, MS Products don't calculate the same way
due to historical resasons. But I am hoping that someone in the future will
correct the lack of standardization(the past historical mistakes), and get
all the MS Products to share/communicate together and give us the abliity to
calculate the same basic data the same basic way in all of the MS Products,
so we as I.T. people don't look stupid. A basic report with no extra rounding
features turned on, in Excel, should give the same numbers/results as the
same basic report that Access gives. A simple approach. Like the KISS
principle.

Too bad the Bears didn't keep it simple and remember they were there to earn
the right to win by sharing/communicating as a Team. Hope somebody incharge
of the Access/Excel development teams learns from the Bears' mistakes.

Thanks again for your help,
Go_Bears maybe next season?

 

[Joseph Meehan]

Joseph, Thank you for your reply. I checked the link you
included. I have seen it before and agree with it, MS Products don't
calculate the same way due to historical resasons. But I am hoping
that someone in the future will correct the lack of
standardization(the past historical mistakes), and get all the MS
Products to share/communicate together and give us the abliity to
calculate the same basic data the same basic way in all of the MS
Products, so we as I.T. people don't look stupid. A basic report with
no extra rounding features turned on, in Excel, should give the same
numbers/results as the same basic report that Access gives. A simple
approach. Like the KISS principle.

Too bad the Bears didn't keep it simple and remember they were there
to earn the right to win by sharing/communicating as a Team. Hope
somebody incharge of the Access/Excel development teams learns from
the Bears' mistakes.

Thanks again for your help,
Go_Bears maybe next season?

Can you provide some actual examples of the numbers and the rounded
numbers as you want them and as they are being displayed by Access. Maybe
we can come up with a solution.