Urgent! Need CrossTab help...

[Bonnie A]

Hi everyone! Using A02 on XP. Need to give my boss some info and my brain
cannot figure out how in the 2 hours left before her meeting.

I have a table with [ContractNumber],[F1],[F2] - [F26]. Fields [F1] through
[F26] contain responses to a survey. They are A, B, C, D and E or blank
(Null). I need to know the total number of A's in [F1], total number of B's
in [F1], etc. including total number of blanks (Null) for [F1]. I need this
for all 26 response fields.

I keep trying a crosstab wizard but just don't get it.

Am I even doing it in the right place?

I've got to try to find a dirty work around for now but would love to be
able to do this right. I would be ever so grateful for any help.

Thanks for your time!

 

[KARL DEWEY]

Create a list of possible entries named F_List.
F_List ---
A
B
C
D

Use this query substituting your table name for BonnieA_3 and adding the
rest of the fields ---
SELECT BonnieA_3.ContractNumber, IIf([F_List] Is Null,"Null",[F_List]) AS
FF, Sum(IIf([F1]=[F_List] Or [F1] Is Null,1,0)) AS F_1, Sum(IIf([F2]=[F_List]
Or [F1] Is Null,1,0)) AS F_2, Sum(IIf([F3]=[F_List] Or [F1] Is Null,1,0)) AS
F_3, Sum(IIf([F4]=[F_List] Or [F1] Is Null,1,0)) AS F_4
FROM BonnieA_3, BonnieA_3List
GROUP BY BonnieA_3.ContractNumber, IIf([F_List] Is Null,"Null",[F_List]);

You should not be using Access as a spreadsheet.

 

[Dale Fye]

Bonnie,

What Karl means about not using Access like a spreadsheet is that the
responses to each question should be stored in rows, not columns. Your table
should look something like:

Respondant Question# Response
1 1 A
1 2 B
1 26 C
2 1 D
2 2 E

With this structure, you could write a simple query like the following to
get these values:

SELECT [Question#], [Response], Count([Respondant]) as Freq
FROM yourTable
GROUP BY [Question#], [Response]

To get your data in this format, I would create a normalization query that
would look something like the following. You will need to add additional
rows for each of the fields, but you should get the idea. Once you have
created this query, save it and use it as the data source in the query above:

SELECT [ContractNumber], "F1" as [Question#], [F1] as Response
FROM yourTable
UNION ALL
SELECT [ContractNumber], "F2" as [Question#], [F2] as Response
FROM yourTable
UNION ALL
SELECT [ContractNumber], "F3" as [Question#], [F3] as Response
FROM yourTable
UNION ALL
....
SELECT [ContractNumber], "F26" as [Question#], [F26] as Response
FROM yourTable

HTH
Dale