Avatar of atljarman
 asked on

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


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  %
DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment
Richard Daneke

8/22/2022 - Mon

Where do you get the perceent values from?
Dale Fye

The totals Row and Column are problematic, but here is a starter:

TRANSFORM First([CountOfID] & "(" & Format([CountOfID]/[StateTotals],"Percent") & ")") AS Expr1
SELECT Temp.Type
SELECT tbl_State_Types.State,
             Count(tbl_State_Types.ID) AS CountOfID,
             DCount("ID","tbl_State_Types","[State] = '" & [tbl_State_Types].[State] & "'") AS StateTotals
FROM tbl_State_Types
GROUP BY tbl_State_Types.State, tbl_State_Types.Type)  AS Temp
GROUP BY Temp.Type
PIVOT Temp.State;
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Richard Daneke

Add a new Field like Count of ID and set the Field Setting to choose % of column

This proposed solution is then a query that can be run at anytime against the original table
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes