CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Angad Arora
Angad Arora

Reputation: 739

Sum Multiple Column MS Access Via Sql Query or VBA

Problem:I am trying to make a Inventory Management Database on Microsoft Access 2010, and since i needed records date wise my Table looks like follows

 SKU   2016-03-16   2016-03-17   2016-03-18   ...   Total
 AAA   10           -5           15           ...   20
 BBB   05           05           25           ...   35
 CCC   06           -5           24           ...   25

This way i wanted to add records for each day, but i am unable to make a "Total" column which will total all the columns (Sum(Columns*))

I am aware of sum and groupby but that works across multiple rows i am looking for something which can do the same for column

Expected Solution: A way either by Sql Query or VBA to total all the columns for SKU AAA,BBB,CCC, if not possible to total in the same table then i am open to total the columns in a new table.

Upvotes: 0

Views: 1519

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

Such a table is normally result of pivoting summary data. Real data would look like:

SKU, Date, Quantity ...

Then probably you would want to create a PIVOT table using SKU for rows, Date for Columns and Quantity for data (default operation is SUM). It would have the row totals by default. It may have been named crosstab, cross tab .. or so in Access 2010.

(you may want to do the pivoting in Excel)

Upvotes: 2

Related Questions