troubleshooting Question

How do I dynamically create summary values by state using Access 2003 query or VBA?

Avatar of atljarman
atljarman asked on
DatabasesMicrosoft AccessSQL
4 Comments1 Solution353 ViewsLast Modified:

I have a table in an Access 2003 database where the records are organized with and id, a state indicator, and a type.

I would like to create a summary table by type and state, and then an overall summary by type.  Here is an example of what the data might look like.

ID    State     Type
1      MS        Car
2      KY         Car
3      KY         Car
4      MS        House
5      KY         House
6      KY         House
7      KY         House
8      MS        Pers
9      KY         Pers

I would like to make a union query so that the tables are stacked on top of each other in the end.  I would then create a calculation of how much of each state represents the total.  The end summary table would look like this:
                     KY                   MS               Total
Total              6    100%         3  100%       9    100%
Car                 2    % of KY      1    %           3   (% of total)
House            3     %               1    %          4  %
Pers                1    %              1     %          2  %
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros