Summary Table query

[daisyoso]

Hi All,

I want to make a summary table like the below:

Service	Total Nos. of drawings	As Built Submitted	SSKB % submission	Approved	Approved with comment	Revised and resubmit	Under review
Pavement	80	74	93	52	22	0	0
Perimeter Road	19	19	100	0	14	5	0
M/E Duct	392	392	100	354	25	13	0
Drain	879	871	99	773	89	9	0
Fence	239	239	100	136	101	2	0
AFL Civil	918	918	100	392	88	386	52
FH Civil	92	76	83	63	0	13	0
Total Nos. of Asbuilt drawings	2619	2589	99	1770	339	428	52

but my knowledge can only create the below. I just only want to count how many "A, AC, R, R&R and so on in my "Status" column. Pls help.

Query1 Query1

Doc Type	Trade	Status	Total
As Built	AFL	A	385​
As Built	AFL	AC	88​
As Built	AFL	R	21​
As Built	AFL	R&R	402​
As Built	AFL	UR	11​
As Built	D&B	TR	37​
As Built	Drainage	A	762​
As Built	Drainage	AC	75​
As Built	Drainage	R&R	9​
As Built	Duct Manhole	A	354​
As Built	Duct Manhole	AC	25​
As Built	Duct Manhole	R&R	13​
As Built	Duct Manhole	TR	2​
As Built	Fence	A	133​
As Built	Fence	AC	103​
As Built	Fence	R&R	2​
As Built	Fire Hydrant	A	67​
As Built	Fire Hydrant	UR	13​
As Built	Pavement	A	49​
As Built	Pavement	AC	21​
As Built	Pavement	W	2​
As Built	Perimeter Road	AC	19​

 

[srehman]

use pivot table

 

[Micron]

There is no pivot table function in Access AFAIK.
I can't make any sense out of the relationships between those 2 data sets so the only suggestion I have is to research Crosstab queries.

 

[daisyoso]

I am using Microsoft 2016 and I cant find pivot table. I will try the crosstab. Thank you guys!