« Why >= or <= doesnt work on my MS ACCESS queries? How to make it work?
Microsoft Access 2007 Database Tutorial – Trust Center Locations »

How to count two values in the same column and in one table with a query in Access?

I guess it’s easy but i just can’t make it…

Here is the deal. I have a table with 3 columns similar to this:
City | Item ID | Status
NY | 001 | Open
MI | 002 | Closed
NY | 003 | Closed
NY | 004 | Open
CH | 005 | Open

I want to count how many Open and Closed item status there are for each City. The query should return the result in 3 columns like:
City | Open | Closed
NY | 2 | 1
CH | 1 | 0
MI | 0 | 1

I only seem to get one column result right… When I add the closed column in the query, it doubles every result.

Thanks

The way you are showing the desired layout of your results is achieved with a Crosstab Query. You can make it two ways: 1.)Make a new query and use the wizard. 2.)Go to your existing query. Go to Query (on the toolbar) and select Crosstab Query. A new column will appear in your design grid labeled "Crosstab". In your City column select Row Heading. In your Item ID column select Value. In your Status column select Column Heading. Run it. Hope this works for you!

This entry was posted on Thursday, February 25th, 2010 at 7:26 am and is filed under access count. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “How to count two values in the same column and in one table with a query in Access?”

  1. colanth Says:
    February 25th, 2010 at 1:11 pm

    You don’t want to add them, you want a count "where Status = ‘Open’" or "where Open = 1" or whatever other logic you want.
    References :

  2. Sandy Says:
    February 25th, 2010 at 1:39 pm

    The way you are showing the desired layout of your results is achieved with a Crosstab Query. You can make it two ways: 1.)Make a new query and use the wizard. 2.)Go to your existing query. Go to Query (on the toolbar) and select Crosstab Query. A new column will appear in your design grid labeled "Crosstab". In your City column select Row Heading. In your Item ID column select Value. In your Status column select Column Heading. Run it. Hope this works for you!
    References :

Leave a Reply