Transposing query Output

[ramez75]

Hi,

I am trying to create the attached chart using access. Below is the query I have so far

 [SIZE=3][FONT=Calibri]SELECT Abs(Sum(IIf([DurationOpen]<=30,"1","0"))) AS [0 - 30 Days], Abs(Sum(IIf([DurationOpen]>31,"1","0") And IIf([DurationOpen]<=60,"1","0"))) AS [31 - 60 Days], Abs(Sum(IIf([DurationOpen]>61,"1","0") And IIf([DurationOpen]<=90,"1","0"))) AS [61 - 90 Days], Abs(Sum(IIf([DurationOpen]>91,"1","0") And IIf([DurationOpen]<=180,"1","0"))) AS [91 - 180 Days], Abs(Sum(IIf([DurationOpen]>181,"1","0") And IIf([DurationOpen]<=365,"1","0"))) AS [181 - 365 Days], Abs(Sum(IIf([DurationOpen]>366,"1","0") And IIf([DurationOpen]<=730,"1","0"))) AS [366 - 730 Days], Abs(Sum(IIf([DurationOpen]>731,"1","0") And IIf([DurationOpen]<=1095,"1","0"))) AS [731 - 1095 Days][/FONT][/SIZE]
 [FONT=Calibri][SIZE=3]FROM qryCAPADurationOpen;[/SIZE][/FONT]

I need to find a way to transpose the out put of the query to be able to use the chart function in the report. Is that possible.

I need the headers in the query to be the x-axis values and the sum values to be the y-axis values

Am I looking at it in a wrong way. Any recommendation is greatly appreciated

Thanks

RB

 

[plog]

Yes, I think you are going about it the wrong way. What you have as column names ([0 - 30 Days], [91 - 180 Days], etc. ) should be values in a column. To generate the chart you posted, you need a query to give you data like this:

AxisY, AxisX
0, "0-30 days"
2, "31-60 days"
1, "61-90 days"
...

That's how the query needs to generate the data to get the chart you want. And you have some weird coding going on elsewhere:

Abs(Sum(IIf([DurationOpen]<=30,"1","0")))

There's no way that's going to be a negative numbers so Abs() does nothing. Also, you shouldn't be summing text. "1" should be 1 and "0" should be 0, both without quotes.

 

[ramez75]

Thanks, I took the quotation out and the ABS function. So how do I create the query to give me the data as you showed

Yes, I think you are going about it the wrong way. What you have as column names ([0 - 30 Days], [91 - 180 Days], etc. ) should be values in a column. To generate the chart you posted, you need a query to give you data like this:

AxisY, AxisX
0, "0-30 days"
2, "31-60 days"
1, "61-90 days"
...

That's how the query needs to generate the data to get the chart you want. And you have some weird coding going on elsewhere:

Abs(Sum(IIf([DurationOpen]<=30,"1","0")))

There's no way that's going to be a negative numbers so Abs() does nothing. Also, you shouldn't be summing text. "1" should be 1 and "0" should be 0, both without quotes.

 

[plog]

I would build a table to define your duration stratifications:

Durations
dur_Name, dur_Min, dur_Max
"0 - 30 Days", 0, 30
"31 - 60 Days", 31, 60
"61 - 90 Days", 61, 90
...
etc.


Then you can build a query using Durations and qryCAPADurationOpen to put each record in qryCAPADurationOpen into the right bucket. This is that SQL:

SELECT Durations.dur_Name
FROM qryCAPADurationOpen INNER JOIN Durations ON (qryCAPADurationOpen.DurationOpen<=Durations.dur_Max) AND (qryCAPADurationOpen.DurationOpen>=Durations.dur_Min);

Save that and name it 'Charts_sub1'. Finally, you can build your query to get the data like you need it for the chart:

SELECT Durations.dur_Name AS AxisX, Count(Chart_sub1.dur_Name) AS AxisY
FROM Durations LEFT JOIN Chart_sub1 ON Durations.dur_Name = Chart_sub1.dur_Name
GROUP BY Durations.dur_Name, Durations.dur_Min
ORDER BY Durations.dur_Min;

That query totals everything and ensures that any durations not in your data still show on the chart.

 

[ramez75]

Thank you Plog,

I created a table and Chart_Sub1. When I run 'Chart_sub1' I am asked to enter "Duration.dur_Name, is that suppose to happen

I would build a table to define your duration stratifications:

Durations
dur_Name, dur_Min, dur_Max
"0 - 30 Days", 0, 30
"31 - 60 Days", 31, 60
"61 - 90 Days", 61, 90
...
etc.


Then you can build a query using Durations and qryCAPADurationOpen to put each record in qryCAPADurationOpen into the right bucket. This is that SQL:

SELECT Durations.dur_Name
FROM qryCAPADurationOpen INNER JOIN Durations ON (qryCAPADurationOpen.DurationOpen<=Durations.dur_Max) AND (qryCAPADurationOpen.DurationOpen>=Durations.dur_Min);

Save that and name it 'Charts_sub1'. Finally, you can build your query to get the data like you need it for the chart:

SELECT Durations.dur_Name AS AxisX, Count(Chart_sub1.dur_Name) AS AxisY
FROM Durations LEFT JOIN Chart_sub1 ON Durations.dur_Name = Chart_sub1.dur_Name
GROUP BY Durations.dur_Name, Durations.dur_Min
ORDER BY Durations.dur_Min;

That query totals everything and ensures that any durations not in your data still show on the chart.

 

[plog]

No where in my code do I use 'Duration.dur_Name', its always 'Durations.dur_Name', so I don't know how the query is asking for that.

What it means is that it is looking for a field called 'dur_Name' in the table called 'Duration'. I used 'Durations' throughout my code, so it shouldn't pop that box up.

Did you use the same table/field names as I did for that new table?

 

[ramez75]

OOPs I know what I did wrong I fat fingered
I typed "du_Name" instead of "dur_Name", I never noticed it was asking for "du_Name" until I paid a close attention. Fixed it and it works like a charm

Thank you so much for the help

No where in my code do I use 'Duration.dur_Name', its always 'Durations.dur_Name', so I don't know how the query is asking for that.

What it means is that it is looking for a field called 'dur_Name' in the table called 'Duration'. I used 'Durations' throughout my code, so it shouldn't pop that box up.

Did you use the same table/field names as I did for that new table?