jalexm Posted May 26, 2016 Share Posted May 26, 2016 Hello, I have a table similar to this:Table: SectorsSector Type-------------- A X B X A Y C Z A X B ZAnd I need to build a query so that I get these results:Sector Nr of X Nr of Y Nr of Z-------------------------------------------- A 2 1 0 B 1 0 1 C 0 0 1Does anyone know how to get this?Note: using an old MS-Access version (2003).Thanks. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 26, 2016 Share Posted May 26, 2016 You could do a count query where you group by both columns, but it's not going to return the data formatted like that. It would return a record set like this, but you could use another language to loop through it and format the table: A X 2 A Y 1 B X 1 B Z 1 C Z 1You can get that result with a count query where you group by both columns. Link to comment Share on other sites More sharing options...
ddwildnd Posted June 21, 2016 Share Posted June 21, 2016 Try something like this... select Sector, (select count(type) from sectors b where a.sector = b.sector and type = 'X') AS "Nr of X", (select count(type) from sectors b where a.sector = b.sector and type = 'Y') AS "Nr of Y", (select count(type) from sectors b where a.sector = b.sector and type = 'Z') AS "Nr of X" from Sectors a group by sector Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now