Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count of multiple fields (2003)

    I couldn't think of a better subject for this post but I can't figure out how to make a query that gives me a count of businesses that fall w/in each of four tiers. The data that I'll use looks like this:
    <pre>Tier SIC CODE BUSINESS TYPE
    4 5411 GROCERY STORES
    3 5995 OPTICAL GOODS STORES
    2 5812 EATING PLACES
    1 5521 USED CAR DEALERS
    1 5961 CATALOG AND MAIL-ORDER HOUSES</pre>

    What I want is to count the total number of businesses in each of the four tiers (1,2,3,4). So in the above example it'd look like this:
    <pre>TIER1 TIER2 TIER3 TIER4
    2 1 1 1</pre>

    where the numbers in the TIER fields are the 'total number of businesses that are listed under tier'.
    What does the query look like this? I've tried all sorts of combos but haven't stumbled on the right one. I keep getting one that gives me a total for all Tiers as a sum not each tier individually.

    Thnx,
    Deb

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count of multiple fields (2003)

    Have you tried the crosstab query wizard? If you activate the queries tab in the database window, and then click on 'New', select crosstab. It should help you create your query.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count of multiple fields (2003)

    Yes, that worked well (after a few attempts <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> )

    Thank you very much. If it weren't for the fact that there are too records to fit in Excel (over 685K), I could of done this in Excel in 30 seconds but I don't know Access very well.

    Thnx,
    Deb

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count of multiple fields (2003)

    Depending on the data...perhaps it would be better off in Access? Anyway, glad I could help.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •