G
Guest
I Know how to get Excel to add the values of a set number of columes by using
the fx. I can't figure out how to do it on Access
the fx. I can't figure out how to do it on Access
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jeff Boyce
Use a query. Select the single column/field that holds the numbers. Change
the query to a Totals query, and use the Sum() group-by.
Or if you are asking how you can add the values in a number of columns, in
Access, you have to spell it out in your query. I.E., field1 + field2 +
field3 + ... fieldn. This sounds like a spreadsheet, though, not a
relational database table.
If you are doing that, your data structure may benefit from further
normalization.
the query to a Totals query, and use the Sum() group-by.
Or if you are asking how you can add the values in a number of columns, in
Access, you have to spell it out in your query. I.E., field1 + field2 +
field3 + ... fieldn. This sounds like a spreadsheet, though, not a
relational database table.
If you are doing that, your data structure may benefit from further
normalization.
G
Guest
Jeff Boyce said:Use a query. Select the single column/field that holds the numbers. Change
the query to a Totals query, and use the Sum() group-by.
Or if you are asking how you can add the values in a number of columns, in
Access, you have to spell it out in your query. I.E., field1 + field2 +
field3 + ... fieldn. This sounds like a spreadsheet, though, not a
relational database table.
If you are doing that, your data structure may benefit from further
normalization.
--
Good luck
Jeff Boyce
<Access MVP>
How do I change a query into a Totals query?
L
LeAnne
GWG said:How do I change a query into a Totals query?
Click the button with the Greek sigma.
PS. Ever heard of the Help file? A little effort on your part would
obviate the need to post in a newsgroup and then having to wait for
someone to read and answer your post. Search for the topic "Perform
calculations in a query," or just go to the Help Index and enter "total"
in the box.
LeAnne
G
Guest
LeAnne said:Click the button with the Greek sigma.
PS. Ever heard of the Help file? A little effort on your part would
obviate the need to post in a newsgroup and then having to wait for
someone to read and answer your post. Search for the topic "Perform
calculations in a query," or just go to the Help Index and enter "total"
in the box.
LeAnne
I did use the help file. I must be doing something wrong because I'm not
getting the sum. I'm not stupid, I'm just not good with all this code
writing.
J
John Vinson
I did use the help file. I must be doing something wrong because I'm not
getting the sum. I'm not stupid, I'm just not good with all this code
writing.
No code is needed to get a Totals query. You may be making the common
error of treating Access as "a big version of Excel". A table may look
like a spreadsheet but IT IS DIFFERENT! YOu cannot and should not have
totals in a Table.
Instead, store your basic data in the Table. Then create a Query based
on the table. If you want to add "across", you can type an expression
in a vacant Field cell in the query grid:
SumAcross: [Field1] + [Field2] + [Field3]
If you want to add "down", click the Greek Sigma icon; select which
fields you want to Group By and put Group By on the new Totals row;
change it to Sum for the fields that you want to sum.
John W. Vinson[MVP]
G
Guest
John Vinson said:I did use the help file. I must be doing something wrong because I'm not
getting the sum. I'm not stupid, I'm just not good with all this code
writing.
No code is needed to get a Totals query. You may be making the common
error of treating Access as "a big version of Excel". A table may look
like a spreadsheet but IT IS DIFFERENT! YOu cannot and should not have
totals in a Table.
Instead, store your basic data in the Table. Then create a Query based
on the table. If you want to add "across", you can type an expression
in a vacant Field cell in the query grid:
SumAcross: [Field1] + [Field2] + [Field3]
If you want to add "down", click the Greek Sigma icon; select which
fields you want to Group By and put Group By on the new Totals row;
change it to Sum for the fields that you want to sum.
John W. Vinson[MVP]
I don't think that I'm getting my problem across.
Child ID Date Paid Amount Paid Total
1 01-Jan-05 $125.00
1 15-Jan-05 $125.00
This is what I'm looking at. I'm trying to make a query that will add the
total from 1 jan and 15 jan then put it in total. I want to be able to add
the day and amount payed by the parent to a daycare provider and see the
total amount payed by the parent. I was able to get it to work in a subform
but it add the end total in all the blocks rather than
01 jan 05 $125 $125
15 jan 05 $125 $250
01 feb 05 $125 $375......
I don't know what I'm doing wrong. I have tried every code(=Sum(amount
paid) =Val() and so on) but none of them worked. If anyone can help I'd
really appreciate it.
GWG
J
John Vinson
I don't think that I'm getting my problem across.
Child ID Date Paid Amount Paid Total
1 01-Jan-05 $125.00
1 15-Jan-05 $125.00
This is what I'm looking at. I'm trying to make a query that will add the
total from 1 jan and 15 jan then put it in total. I want to be able to add
the day and amount payed by the parent to a daycare provider and see the
total amount payed by the parent. I was able to get it to work in a subform
but it add the end total in all the blocks rather than
01 jan 05 $125 $125
15 jan 05 $125 $250
01 feb 05 $125 $375......
I don't know what I'm doing wrong. I have tried every code(=Sum(amount
paid) =Val() and so on) but none of them worked. If anyone can help I'd
really appreciate it.
GWG
You're confusing data STORAGE with data PRESENTATION. I realize that
in Excel you use the same vehicle for storage, as for calculations, as
for printing - but Access is not Excel, and requires different logic.
The total should not be stored *in any table whatsoever*.
If you want to present the data like
Child ID Date Paid Amount Paid
1 01-Jan-05 $125.00
1 15-Jan-05 $125.00
Total: $250.00
then just store the ID, DatePaid, and AmountPaid in your Table; and
create a Form (for onscreen viewing) or a Report (for printing) based
on the table. Make the Form (or Report) footer visible (using the View
menu option) and put a textbox labeled Total on that footer, and set
its COntrol Source to
= Sum([Amount Paid])
John W. Vinson[MVP]
G
Guest
John Vinson said:I don't think that I'm getting my problem across.
Child ID Date Paid Amount Paid Total
1 01-Jan-05 $125.00
1 15-Jan-05 $125.00
This is what I'm looking at. I'm trying to make a query that will add the
total from 1 jan and 15 jan then put it in total. I want to be able to add
the day and amount payed by the parent to a daycare provider and see the
total amount payed by the parent. I was able to get it to work in a subform
but it add the end total in all the blocks rather than
01 jan 05 $125 $125
15 jan 05 $125 $250
01 feb 05 $125 $375......
I don't know what I'm doing wrong. I have tried every code(=Sum(amount
paid) =Val() and so on) but none of them worked. If anyone can help I'd
really appreciate it.
GWG
You're confusing data STORAGE with data PRESENTATION. I realize that
in Excel you use the same vehicle for storage, as for calculations, as
for printing - but Access is not Excel, and requires different logic.
The total should not be stored *in any table whatsoever*.
If you want to present the data like
Child ID Date Paid Amount Paid
1 01-Jan-05 $125.00
1 15-Jan-05 $125.00
Total: $250.00
then just store the ID, DatePaid, and AmountPaid in your Table; and
create a Form (for onscreen viewing) or a Report (for printing) based
on the table. Make the Form (or Report) footer visible (using the View
menu option) and put a textbox labeled Total on that footer, and set
its COntrol Source to
= Sum([Amount Paid])
John W. Vinson[MVP]
Thanks everyone. I got it working. I couldn't have done it with out ya'll
G
Guest
John,
Thanks for the reply to this post. After days of trying to find an answer,
finally a clear, suscint answer to a problem I had also. I am unable to add
a total column to the underlying data table - I am restricted to totalling
within queries.
As a long time database user (in many forms: DBase, Foxpro, SPSS, GIS
systems,...) I have always found Access a problem bc it refuses to allow my
to use it as a "larger version of Excel". Essentially Access has added
relational functionallity (the first version I used was Access 1.0) but is
still basically a front end for extracting data, not what I consider a full
database program.
I still need to export to full featured database to calc the total column
before reimporting into Access. Otherwise I have to construct the sum field
query everytime I want to pull the data. BTW the reason I want to calc a
total column once is because I have years worth of weekly data - entering sum
formulas for 52 weeks worth of data everytime is very time consuming and
prone to errors.
Your response was much appreciated.
Thanks for the reply to this post. After days of trying to find an answer,
finally a clear, suscint answer to a problem I had also. I am unable to add
a total column to the underlying data table - I am restricted to totalling
within queries.
As a long time database user (in many forms: DBase, Foxpro, SPSS, GIS
systems,...) I have always found Access a problem bc it refuses to allow my
to use it as a "larger version of Excel". Essentially Access has added
relational functionallity (the first version I used was Access 1.0) but is
still basically a front end for extracting data, not what I consider a full
database program.
I still need to export to full featured database to calc the total column
before reimporting into Access. Otherwise I have to construct the sum field
query everytime I want to pull the data. BTW the reason I want to calc a
total column once is because I have years worth of weekly data - entering sum
formulas for 52 weeks worth of data everytime is very time consuming and
prone to errors.
Your response was much appreciated.
John Vinson said:I did use the help file. I must be doing something wrong because I'm not
getting the sum. I'm not stupid, I'm just not good with all this code
writing.
No code is needed to get a Totals query. You may be making the common
error of treating Access as "a big version of Excel". A table may look
like a spreadsheet but IT IS DIFFERENT! YOu cannot and should not have
totals in a Table.
Instead, store your basic data in the Table. Then create a Query based
on the table. If you want to add "across", you can type an expression
in a vacant Field cell in the query grid:
SumAcross: [Field1] + [Field2] + [Field3]
If you want to add "down", click the Greek Sigma icon; select which
fields you want to Group By and put Group By on the new Totals row;
change it to Sum for the fields that you want to sum.
John W. Vinson[MVP]
J
John Vinson
John,
Thanks for the reply to this post. After days of trying to find an answer,
finally a clear, suscint answer to a problem I had also. I am unable to add
a total column to the underlying data table - I am restricted to totalling
within queries.
That is correct. If you want to perform *operations*, do so in
Queries; if you want to store *data*, do so in tables.
As a long time database user (in many forms: DBase, Foxpro, SPSS, GIS
systems,...) I have always found Access a problem bc it refuses to allow my
to use it as a "larger version of Excel". Essentially Access has added
relational functionallity (the first version I used was Access 1.0) but is
still basically a front end for extracting data, not what I consider a full
database program.
You can drive nails with a crescent wrench, but that doesn't make it a
hammer. If you look at SQL/Server, Oracle, DB/2, Visual FoxPro, MySQL,
or a raft of other "full database programs" you will find that... aha!
No, you cannot do calculations in table there either.
I still need to export to full featured database to calc the total column
before reimporting into Access. Otherwise I have to construct the sum field
query everytime I want to pull the data. BTW the reason I want to calc a
total column once is because I have years worth of weekly data - entering sum
formulas for 52 weeks worth of data everytime is very time consuming and
prone to errors.
You do not need to reconstruct any queries. You need ONE QUERY to do
this total. Just store the query, and have it apply to your table; if
you want parameters to select a particular week, just use a parameter
query.
Access is *perfectly* capable of accomplishing what you want to do.
However, you must bend a little bit and work with it, rather than
constructing a Procrustean bed of "this is the way this must always be
done" and forcing the program into it.
John W. Vinson[MVP]