How do i create an expresion to sum up totals for each Record line

  • Thread starter Thread starter Nyeri Rach
  • Start date Start date
N

Nyeri Rach

I created a table that has values for different category collums, e.g sales
for V1, V2, V3, V4 etc but I want to have a report that sums up these values
for each entry having a total for that line of transaction. then to sum up
the totol of the resulting collum.
 
Could you share your table structure and some sample records? If your tables
are properly normalized your report's record source would be a simple totals
query like:

SELECT Category, Sum([Sales]) as CatSales
FROM tblSales
GROUP BY Category;
 
It sounds like you are describing a spreadsheet, where, if you add a new
'category', you have to add a new column.

Access is a relational database. You won't get the best use of Access'
relationally-oriented features/functions if you feed it 'sheet data.

If you are using one column per 'category', what happens to your design when
the number of categories changes? You'll have to modify your table, related
queries, related forms, related functions, related reports, related macros
.... a maintenance nightmare!

A relational design would store categories in a table (one category per
row), and have a table that resolves the many-to-many relationship it sounds
like you have between something (I didn't spot that) and category.

With this design, if you add a category, you simply add a new row to the
category table. Everything else keeps working!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top