atljarman
asked on
How do I dynamically create summary values by state using Access 2003 query or VBA?
Hi,
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 %
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 %
jerryb30
Where do you get the perceent values from?
The totals Row and Column are problematic, but here is a starter:
TRANSFORM First([CountOfID] & "(" & Format([CountOfID]/[StateT
SELECT Temp.Type
FROM (
SELECT tbl_State_Types.State,
tbl_State_Types.Type,
Count(tbl_State_Types.ID) AS CountOfID,
DCount("ID","tbl_State_Typ
FROM tbl_State_Types
GROUP BY tbl_State_Types.State, tbl_State_Types.Type) AS Temp
GROUP BY Temp.Type
PIVOT Temp.State;
TRANSFORM First([CountOfID] & "(" & Format([CountOfID]/[StateT
SELECT Temp.Type
FROM (
SELECT tbl_State_Types.State,
tbl_State_Types.Type,
Count(tbl_State_Types.ID) AS CountOfID,
DCount("ID","tbl_State_Typ
FROM tbl_State_Types
GROUP BY tbl_State_Types.State, tbl_State_Types.Type) AS Temp
GROUP BY Temp.Type
PIVOT Temp.State;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
This proposed solution is then a query that can be run at anytime against the original table