Fieldnames As Columns?

  • Thread starter Thread starter Jim Jawn
  • Start date Start date
J

Jim Jawn

I have a table with 13 yes/no fields. Right now the table looks like this:

ID A B C D E F G H I J K L M N
24 -1 0 0 -1 -1 0 0 0 0 0 0 -1 0 0
25 -1 0 0 0 -1 -1 0 0 -1 0 0 0 0 0

What I would like to return is this:

Field Count
A 2
B 0
C 0
D 1
E 2
F 1
etc...

Is there any sort of way that I could do this? Ultimately I'd like to
create a chart from the data, but I can't seem to get it to work right...
Could I use a make table query, does anyone have any suggestions? Thanks.

Jim Jawn
 
This may not be a very practical suggestion if you've already got data in
your database, but you could (should?) change the table design:

ID Letter Result
24 A -1
24 B 0
24 C 0
24 D -1
24 E -1
....
25 A -1
etc

(Combination of ID and Letter as primary key, with a link to other tables
via the ID field).

You would have no problems with the query from this design. (And also no
problems when you need results for O,P or Q).
 
First step is to union your 13 fields into one. Something
like (air query):

--quniAllData
Select ID, "A" As TheCode, A As TheValue From tblTable
UNION ALL
Select ID, "B" As TheCode, B As TheValue From tblTable
UNION ALL
Select ID, "C" As TheCode, C As TheValue From tblTable
....

Then take that union query and perform a group-by.

Select TheCode, Sum([TheValue]) As TotalValue
From quniAllData
Group By TheCode

David Atkins, MCP
 
Back
Top