Fieldnames As Columns?

[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

 

[Andrew Smith]

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).

 

[GreySky]

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