Access: "crosstab" query effect for non-numeric data

Question

I am using Access 2007 and need help creating a query. If this is crazy long, sorry! I figure the more details the better understanding. I am not a programmer--I hope someone will help.

I need to know how to collapse/summarize/roll up non-numeric data into a single row that shares a characteristic/value. What I want is like a crosstab query because I want unique cell values from different rows pivoted/displayed in columns along one row for that shared/common value. However, my data isn't numeric and it requires alpha column headings, which crosstabs prohibit.

THE DATA:

• Badge_code 24 is produced in 3 locations: 110, 210, and 320 (represents Chicago, Rockford, and St. Louis)
• Badge_code B9 in 2 locations: 110, and 280 (Chicago and Peoria)
• Badge_code C1 in 3 locations: 200 and 210 (Indianapolis and Rockford)

Each badge_code-location combination is in a separate row, 8 rows total. The number of badge codes can/will grow.

THE QUERY I'VE TRIED:

Count(tbl_BadgeType.Badge_type_number) AS CountOfBadge_type_number
SELECT tbl_BadgeType.Badge_code
FROM tbl_BadgeType
GROUP BY tbl_BadgeType.Badge_code
PIVOT tbl_BadgeType.Location_production;

Badge_type_number is the primary key used to id each badge code-location use combo.

RESULT:

Badge Code     110   200   210  280  320
24             1           1         1
B9             1                1    
C1                   1     1         

The column headings are numeric and can't be changed to location names in crosstabs and I want a "Yes" instead of a "1". (Obviously, they're 1s because it's a count function in the crosstab, which I don't really need...)

DESIRED RESULT:

Badge Code     Chicago  Indianapolis   Rockford  Peoria  St. Louis
24             Yes      No             Yes       No      Yes
B9             Yes      No             No        Yes     No
C1             No       Yes            Yes       No      No

PROBLEM/QUESTIONS:

4. A crosstab query in its strictest sense is not quite what I need, but I do not know how to simulate its "roll up" effect. It's the difference between 6 pages of obscurity and 1 page of useful clarity.
5. If a crosstab is the way to go, then I can't figure out how to get the "1" values to be displayed as a "Yes". I've used IIF statements in other queries and reports to "translate" 1/0 values to "Yes/No" but can't figure out where to put the IIFs in this query or its report.

Is this making sense? Am I asking too much? Is there hope for me? :^)

Answer 1

Wow! I wasn't expecting an answer so soon.

Here's the structure and sample data of pertinent fields of tbl_BadgeTypes :

Badge_code     Badge_description(both are Text fields)
01             Faculty/staff
04             College of Law
62             Student-Peoria
86             Visitor
B7             Police 

The locations where a badge code is actually USED is in tbl_TokenInstance (a row for each instance of a token--badge, ID, etc. A badge code may be used by different locations; they're not exclusive. Pertinent data is:

  Instance_number(autonumber)  Prod_site_num(Integer)   Badge_code(text)
   1                            110                      B7
   3                            110                      B7
   4                            110                      B7
   27                           310                      04
   15                           210                      62
   21                           110                      62
   16                           110                      01
   22                           210                      01
   25                           310                      01
   31                           110                      86
   32                           120                      86

Last, the source list of production site numbers and descriptions is in list_ProductionSite:

  Production_site_number        Production_site_description
  110                           Chicago IDC
  120                           Chicago CS
  210                           Springfield IDC
  310                           Champaign IDC
  320                           Champaign CS

Answer 2

OK, so pending responses to the comments I left above, I can suggest some partial solutions (cleaner solutions may result, depending on what your table looks like):

Problem #1: Numbers to Text

If you have a table that can match [Location_production] to strings like "Chicago," than an inner join is all you need to make '110' appear as "Chicago." The third line of the query you tried would turn into something like:

FROM tbl_BadgeType AS T INNER JOIN tbl_Locations AS L ON T.Location_production = L.ID 

You would then just crosstab on L.LocationName or whatever the actual text is.

Problem #2: "1" to "Yes"

Generally speaking, you could just swap a vb iif expression in to change the "1"s to "Yes"s

ProducesHere: iif([Badge_type_number]>0, "Yes","No")

You would need to change "Count()" to "Max()" or "Min()" in the top line of your query and possibly other changes, depending on what your data looks like.