• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 938
  • Last Modified:

Access:Create a Table Sum of Categories

I have Table1. I need a second table to be generated from this table that will list each distinct category in table1 followed by the total cost in that category. How would I write VB to do this? (See tables below  )

Table1: THIS IS WHAT I HAVE
Field:  Book Categories     Book Purchases
               Fiction                      $30.02
               Fiction                   $10.05
               Romance            $5.00
               Sci-Fi            $15.00
         Technical            $20.00
         Romance            $19.99
         Sci-Fi            $10.99


Table2 : END RESULT I NEED
Field:    Book Categories     Book Purchases
               Fiction                      $40.02
               Sci-Fi            $25.99
               Romance            $24.99
               Technical            $20.00
        
0
ouestque
Asked:
ouestque
  • 2
  • 2
  • 2
1 Solution
 
ImoutwestCommented:
Just curious here, why?

1. I would recommend not creating your table, but the values change.
2. What you want is already available through grouping and summary on a Report.
3. Also, you could get these totals from a query.

Imoutwest
0
 
ImoutwestCommented:
Sorry, should be:
1. I would recommend not creating your table, BECAUSE the values change.
0
 
ldunscombeCommented:
You could use a make table query such as

SELECT Table1.[Book Categories], Sum(Table1.[Book Purchases]) AS [SumOfBook Purchases] INTO Table2
FROM Table1
GROUP BY Table1.[Book Categories];

which will create a table called Table2 in your database  with  the data as at the time you run it but as Imoutwest suggests the data in this table is static and is not automatically updated as new sales are made.

A select query however such as below will show the "Current Data" without the need for an additional table and can be used as the basis of a report, form or whatever.

SELECT Table1.[Book Categories], Sum(Table1.[Book Purchases]) AS [SumOfBook Purchases]
FROM Table1
GROUP BY Table1.[Book Categories];

If for some reason you need the static data then a maketable query would be the easiest way.

Cheers
Leigh




0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
ouestqueAuthor Commented:
Thanks guys! I got how to make the table, but how do I get the total cost to appear in the second column (As described above) Doing it in query form would be fine if you can do it.
0
 
ldunscombeCommented:
SELECT Table1.[Book Categories], Sum(Table1.[Book Purchases]) AS [Total Cost] INTO Table2
FROM Table1
GROUP BY Table1.[Book Categories];

Cheers
0
 
ouestqueAuthor Commented:
Oh yeah sorry idun! you explained that in your earlier post.

Thanks again!!!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now