Crosstab

[0nyx175]

Hi guys. I've been wracking my brains all day with no luck.

I have the following data

Account number Forename Surname 1 2 3 4 5
1234567 bob jones F A F F F
2233111 Sid Roberts A F A F A

These are an individuals voting options.

I need the following summary showing a count of all votes in their categories. :

Vote F A X
1 1 0 0
2 8 2 9
3 1 3 5
4 2 6 7

Can this be done with a cross-tab because I've really become stuck as to how i would do that

 

[plog]

I don't see that you can.

Where do you expect the Vote column values to come from? I don't see them in your original data.

And that's overlooking the fact that your table seems to be improperly structured. You shouldn't have fields named with numbers. It should be in a seperate table.

 

[0nyx175]

I don't see that you can.

Where do you expect the Vote column values to come from? I don't see them in your original data.

And that's overlooking the fact that your table seems to be improperly structured. You shouldn't have fields named with numbers. It should be in a seperate table.

The vote column values are input by the user. Could you think of a way to restructure which would allow me to output in that format?

 

[plog]

The vote column values are input by the user

That doesn't make sense. User input into a query itself cannot dictate rows. To do that you would need to use VBA.

A properly normalized table structure would use the primary key of the table you posted as a foreign key for multiple records. Suppose FID=1 for the first row of data you posted (Account number =1234567) and FID=2 for the second row (Account number = 2233111). Then the table to hold the votes would look like this:

Voting
VoteID, FID, VoteNumber, VoteValue
1, 1, 1, F
2, 1, 2, A
3, 1, 3, F
4, 1, 4, F
5, 1, 5, F
6, 2, 1, A
7, 2, 2, F
8, 2, 3, A
9, 2, 4, F
10, 2, 5, A

That would be the proper structure for the voting. Again, though, you have an issue obtaining the Vote value for the crosstab query you proposed.