Transpose a Table to be used in a report

[ritchie_h]

Hi

I have search very hard and tried almost every transpose sql queries but i not getting it so i am asking for some help here.

This is my Table

AdioDTlID    MMID    DOAudioData           Frequency    RightEar    LeftEar
1                 20      04-Apr-10             0.50        10           12
2                 20      04-Apr-10             1.00        10           10
3                 20      04-Apr-10             2.00          0          10
4                 20      04-Apr-10             3.00        10          10
5                 20      04-Apr-10             4.00        20          15
6                 20      04-Apr-10             6.00          5          50
7                 20      04-Apr-10             8.00        15           60
8                 20      05-Apr-10             0.50        12           12
9                 20      05-Apr-10             1.00        10           12
10               20      05-Apr-10             2.00        10           12
11               20      05-Apr-10             3.00        10           12
12               20      04-Apr-10             0.50        10           12
13               20      04-Apr-10             1.00        10           10
14               20      04-Apr-10             2.00          0          10
15               20      04-Apr-10             3.00        10          10
16               20      04-Apr-10             4.00        20          15
17               20      04-Apr-10             6.00          5          50
18               20      04-Apr-10             8.00        15           60

I want to be able to extract the following in the following format meaning in the tranpose form so i can used it in the reports so that it will not occupy too much space.

Frequency        0.50    1.00    2.00    3.00    4.00    6.00    8.00
RightEar        10        10       0       10      20         5      15
LeftEar         12        10      10       10      15       50      60

Group By MMID, DOAudioData

Any help would be much apprciated i hope i explain it if you need any more details please ask i will be here all night

Ritchie

 

[JamesMcS]

You're talking about a crosstab query right? But your data isn't quite set up right... You need at least one row heading (this is the missing one), one column heading (Frequency) and a maximum of one value (leftear or rightear).

I think what you need to do is have your table set up differently - rather than having leftear and rightear as column headings, have a column "ear", in which you'll put "left" or "right", the corresponding frequency, and what I presume is the score for each ear:

Ear Frequency Score
Left 0.50 10
Right 0.50 10
Left 1.00 5
Right 1.00 6

And so on. You'd be able to create a crossdtab query really easily with this, using ear as a row heading, freq as a column heading, and score as the value.

 

[ritchie_h]

thanks for the information...but is there any other way to go about it using union queries and then cross tab for instance

 

[JamesMcS]

I've not used union queries before... but the problem with crosstabbing the data you've got at the moment is that you'd basically need 2 values, when you can only have one.

Let me know how you get on with a union query! You might be able to do 2 crosstabs and join them up union style...

 

[ritchie_h]

ok i used your first suggestion and added the two fields ear and score...but now i am having the problem of mapping my data for the chart as i will need to get the following information to plot

Frequency Against series one Right Ear Score and seires 2 Left Ear Score

but i am trying a few things if you can help point me in a direction would be great.

Thanks for all the help

Ritchie

 

[JamesMcS]

Hmm would this be in a report? I'd have thought Access would take care of that automatically... If it's in excel, it's easy to create series - the easiest way would be to sort by ear, then you can just select that range as the series data.

 

[ritchie_h]

I got through by using cross tab to get the information in the order to plot it....my problem was the master and child links to get the data to be dynamic...but i think it got through....it working so far still testing.

thanks for the help.

 

[JamesMcS]

No worries, sounds like you cracked it yourself!