Sum w/in query

S

Sr Accountant

I have an Excel spreadsheet that I import into Access. I am trying within
Access to have it sum/net information for me. For example, I would like all
my 925-0 to be subtracted from my TRVU's for a particular doctor. Within
writing formulas to do this in Excel prior to importating to Access, how can
I do it within Access? Any input you can provide would be greatly
appreciated.
Thanks
 
G

Golfinray

It is easier to take your field that you want to sum, put it in a separate
totals query, and then it back into your main query. In you try to sum in
your regular query, it gets messy.
 
S

Sr Accountant

I have tried that, but I can't get it to work. Can you elaborate a little
more for me? Would I need to rename field names so they aren't the same in
each query? This seems to be part of my problem.
Thanks
 
J

John W. Vinson

I have an Excel spreadsheet that I import into Access. I am trying within
Access to have it sum/net information for me. For example, I would like all
my 925-0 to be subtracted from my TRVU's for a particular doctor. Within
writing formulas to do this in Excel prior to importating to Access, how can
I do it within Access? Any input you can provide would be greatly
appreciated.
Thanks
Click to expand...

First off... Excel is a spreadsheet, best of breed. Access is a relational
database programming environment. THEY ARE DIFFERENT! Access is *not* Excel on
steroids, and a table datasheet is *not* a spreadsheet and doesn't work like
one.

Access Tables store data, not expressions or calculations. Store your (real)
data in your Tables, and then - separately - use a Query to do calculations;
or do your calculations in a Form or a Report.

For some help getting into the Access mindset check out:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
S

Sr Accountant

I understand that Access is NOT Excel, however, I thought you could write
some calculations. I have all my data in a main table, and have created sum
queries for those I need to separate out. In my main table, I have all my
data (for example, Total RVU's and also individual sites RVU's broken out for
each by the Dr #). I need to take the total RVU's and subtract any
individual sites RVU's from that particular Dr.'s total RVU's. I thought
this could be performed in Access, but it sounds to me that you are saying
no.

Please confirm or advise.
Thanks,
 
J

John W. Vinson

I understand that Access is NOT Excel, however, I thought you could write
some calculations. I have all my data in a main table, and have created sum
queries for those I need to separate out. In my main table, I have all my
data (for example, Total RVU's and also individual sites RVU's broken out for
each by the Dr #). I need to take the total RVU's and subtract any
individual sites RVU's from that particular Dr.'s total RVU's. I thought
this could be performed in Access, but it sounds to me that you are saying
no.
Click to expand...

Oh, sorry! Of course Access can perform calculations. It's just a matter of
WHERE it performs calculations.

You cannot (and need not) perform calculations *in a Table*. Just store the
data in a table; you'll never need to look at the table datasheet once you
have the database set up.

Instead, do the calculations in a Query, and base a form or report on that
query; or, do the calculations directly in your Form or Report. For totals
it's convenient to show the detail data in the Detail section of a form or
report, and put controls in the Footer to do the sums and averages.
 
Top