Help with Calculating

[beeemergyrl]

Hi- I created a table in order to keep a log of all of our marketing
expenses. For each expense, I have a "Total" field where we enter how much
was spent. I also have a "Date" field to enter the date the expense occurred.

Is there a way that I can create another field that will calculate each
"Total"? Like in Excel where we are able to use the Sum function to sum up a
column.

Also, I want to be able to track how much was spent on marketing for each
month. Hopefully this is not too complicated.

Can someone help? Thanks!!

 

[John W. Vinson]

Hi- I created a table in order to keep a log of all of our marketing
expenses. For each expense, I have a "Total" field where we enter how much
was spent. I also have a "Date" field to enter the date the expense occurred.

Is there a way that I can create another field that will calculate each
"Total"? Like in Excel where we are able to use the Sum function to sum up a
column.

Also, I want to be able to track how much was spent on marketing for each
month. Hopefully this is not too complicated.

Can someone help? Thanks!!

You can't - and shouldn't! - store any calculated fields in a table. That's
not what tables are for. Do the calculations in a Query, or on a Form or
Report instead. There are Totals queries that will let you sum, average,
count, etc. records; and on a Report you can have many layers of sorting and
grouping, with subtotals and grand totals at each level.

A table datasheet may look like a spreadsheet but it *isn't*, and you'll have
nothing but frustration trying.

Also... don't use the reserved words Date or Total for fieldnames! There'll be
odd problems when Access gets confused about whether you mean your fieldname
or the builtin keyword.

 

[beeemergyrl]

How can I calculate in in a query? Can you guide me through it? Thanks John.

 

[John W. Vinson]

How can I calculate in in a query? Can you guide me through it? Thanks John.

Well... it depends on the structure of your table and what you want to
calculate, which I don't know except in the most general terms.

A Totals query can be created by creating a new query and clicking the
summation (Greek Sigma, looks like a sideways M) icon. This will let you group
by any field or fields in the query and Sum, Average, etc. any numeric field,
count records, etc. You can use this totals query as the recordsource for a
form or report.

Or you can create a Form based on a table, and put textboxes in the form's
Header or Footer with control sources like

=Sum([fieldname])

You can do the same in a Report, with the addition that you can use the
report's Sorting and Grouping feature to get multiple layers of groupings and
subtotals and grand totals.

In short your questioni has no short answer - there's lots of ways to do it.

If you'ld like to post some relevant fieldnames and datatypes (and meanings if
they aren't obvious) from your table and indicate what kind of calculation you
want to do, I or another volunteer can give you an example.