Transpose rows and columns

[Guest]

I know this has been asked and answered for some complicated tables but this
seems much simpler and I am stumped. No, I don't want to export to Excel and
import back.

SELECT Format([Date Of Audit],"ww") AS Week, Abs(Count(IIf([Goal Met] Is Not
Null,1,0))) AS Total, Abs(Sum(IIf([goal met]=Yes,1,0))) AS Pass,
Abs(Sum(IIf([goal met]=No,1,0))) AS Fail, Abs(Sum(IIf([goal
met]=Yes,1,0)))/Abs(Count(IIf([Goal Met] Is Not Null,1,0))) AS Percentage
FROM Audits
GROUP BY Format([Date Of Audit],"ww")
HAVING (((Format([Date Of Audit],"ww")) Is Not Null));

This gives me each weeks totals one row at a time but I want one line with
new columns for each week. I don't know how to get a single row heading to
group on without screwing it up. Any Advice Anyone??? Thank You

 

[tina]

have you tried using your query as the base to create a crosstab query?

hth

 

[Guest]

I moved things around in a crosstab query and used format([date of
audit],"yyyy") as the row heading to get one row.

This is all hit and miss, I don't understand the whys, but it works,

Thank you
--
Jeff C
Live Well .. Be Happy In All You Do

have you tried using your query as the base to create a crosstab query?

hth

I know this has been asked and answered for some complicated tables but this
seems much simpler and I am stumped. No, I don't want to export to Excel and
import back.

SELECT Format([Date Of Audit],"ww") AS Week, Abs(Count(IIf([Goal Met] Is Not
Null,1,0))) AS Total, Abs(Sum(IIf([goal met]=Yes,1,0))) AS Pass,
Abs(Sum(IIf([goal met]=No,1,0))) AS Fail, Abs(Sum(IIf([goal
met]=Yes,1,0)))/Abs(Count(IIf([Goal Met] Is Not Null,1,0))) AS Percentage
FROM Audits
GROUP BY Format([Date Of Audit],"ww")
HAVING (((Format([Date Of Audit],"ww")) Is Not Null));

This gives me each weeks totals one row at a time but I want one line with
new columns for each week. I don't know how to get a single row heading to
group on without screwing it up. Any Advice Anyone??? Thank You

 

[tina]

you're welcome

I moved things around in a crosstab query and used format([date of
audit],"yyyy") as the row heading to get one row.

This is all hit and miss, I don't understand the whys, but it works,

Thank you
--
Jeff C
Live Well .. Be Happy In All You Do

have you tried using your query as the base to create a crosstab query?

hth

I know this has been asked and answered for some complicated tables

but
this

seems much simpler and I am stumped. No, I don't want to export to

Excel
and

import back.

SELECT Format([Date Of Audit],"ww") AS Week, Abs(Count(IIf([Goal Met]

Is
Not

Null,1,0))) AS Total, Abs(Sum(IIf([goal met]=Yes,1,0))) AS Pass,
Abs(Sum(IIf([goal met]=No,1,0))) AS Fail, Abs(Sum(IIf([goal
met]=Yes,1,0)))/Abs(Count(IIf([Goal Met] Is Not Null,1,0))) AS Percentage
FROM Audits
GROUP BY Format([Date Of Audit],"ww")
HAVING (((Format([Date Of Audit],"ww")) Is Not Null));

This gives me each weeks totals one row at a time but I want one line with
new columns for each week. I don't know how to get a single row

heading
to

group on without screwing it up. Any Advice Anyone??? Thank You