Please Help!!! Query Results not right formatting for report

T

TotallyConfused

I have a query that give me counts of certain items. I will need to use for
a subreport. However, the results of the query (ex. below) generate
vertically and I only have two fields to pull into the report. I need the
results of the query to generate a column for each item and its count. Can I
do this in a query? If so how. I need to pull fields for each color and the
count. Please help!!

qry Ques 2
Color CountOfQues 2
White 5
Blue 4
Red 8
Green 1
 
D

Duane Hookom

Consider a crosstab query as the record source for your report. You may need
to enter all possible "colors" into the Column Headings property of the
Crosstab query.
 
T

TotallyConfused

I have run a crosstab query and it will give me the Colors across but it will
not give me the totals under the heading. I need to reference this in a
report. Please help! What am I doing wrong?

qry Ques 2 _Crosstab
CountOfQues 2 Ques 2 White Blue Red Green
1 2 1
4 4 1
5 5 1
8 3 1
 
T

TotallyConfused

TRANSFORM Count([qry Ques 2 cnts].Expr1000) AS CountOfExpr1000
SELECT [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2],
Count([qry Ques 2 cnts].Expr1000) AS [Total Of Expr1000]
FROM [qry Ques 2 cnts]
GROUP BY [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2]
PIVOT [qry Ques 2 cnts].Satisfaction;
 
T

TotallyConfused

This is the correct one.

TRANSFORM Count([qry Ques 2 cnts].Expr1000) AS CountOfExpr1000
SELECT [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2],
Count([qry Ques 2 cnts].Expr1000) AS [Total Of Expr1000]
FROM [qry Ques 2 cnts]
GROUP BY [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2]
PIVOT [qry Ques 2 cnts].Color;
 
D

Duane Hookom

Do you have values in the Color field?
Do you have values in the Expr1000 field?
Why would you not change the column name of Expr1000 to something that makes
sense?
Are there any records returned in the crosstab?

--
Duane Hookom
MS Access MVP


TotallyConfused said:
This is the correct one.

TRANSFORM Count([qry Ques 2 cnts].Expr1000) AS CountOfExpr1000
SELECT [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2],
Count([qry Ques 2 cnts].Expr1000) AS [Total Of Expr1000]
FROM [qry Ques 2 cnts]
GROUP BY [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2]
PIVOT [qry Ques 2 cnts].Color;


Duane Hookom said:
Show us your SQL view of the crosstab.
Click to expand...
Click to expand...
 
T

TotallyConfused

Okay, I ran another crosstab query. This is what I got. Which is exactly
what I wanted the Colors as headings with the totals below it. This is how I
want to pull into my report but all on one row. However, when I pull this
in my report it doesn't look like this. It counts rows. (copy of report
outcome below) Please help.

qry Ques 2 Rev_Crosstab
Ques 2 Total Of Color White Blue Red Green
2 1 1
3 8 8
4 4 4
5 5 5


TRANSFORM Count([qry Ques 2 Rev].Satisfaction) AS CountOfColor
SELECT [qry Ques 2 Rev].[Ques 2], Count([qry Ques 2 Rev].Color) AS [Total Of
Color]
FROM [qry Ques 2 Rev]
GROUP BY [qry Ques 2 Rev].[Ques 2]
PIVOT [qry Ques 2 Rev].Color;

Report:
White Blue Red Green
1

Duane Hookom said:
Do you have values in the Color field?
Do you have values in the Expr1000 field?
Why would you not change the column name of Expr1000 to something that makes
sense?
Are there any records returned in the crosstab?

--
Duane Hookom
MS Access MVP


TotallyConfused said:
This is the correct one.

TRANSFORM Count([qry Ques 2 cnts].Expr1000) AS CountOfExpr1000
SELECT [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2],
Count([qry Ques 2 cnts].Expr1000) AS [Total Of Expr1000]
FROM [qry Ques 2 cnts]
GROUP BY [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2]
PIVOT [qry Ques 2 cnts].Color;


Duane Hookom said:
Show us your SQL view of the crosstab.

--
Duane Hookom
MS Access MVP
--

message I have run a crosstab query and it will give me the Colors across but it
will
not give me the totals under the heading. I need to reference this in
a
report. Please help! What am I doing wrong?

qry Ques 2 _Crosstab
CountOfQues 2 Ques 2 White Blue Red Green
1 2 1
4 4 1
5 5 1
8 3 1


:

I have a query that give me counts of certain items. I will need to
use
for
a subreport. However, the results of the query (ex. below) generate
vertically and I only have two fields to pull into the report. I need
the
results of the query to generate a column for each item and its count.
Can I
do this in a query? If so how. I need to pull fields for each color
and
the
count. Please help!!

qry Ques 2
Color CountOfQues 2
White 5
Blue 4
Red 8
Green 1
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You show a query that should return 5 columns and then only provide 3
values. I can't help you with that kind of information. We are not getting
at your root issues.

--
Duane Hookom
MS Access MVP
--

TotallyConfused said:
Okay, I ran another crosstab query. This is what I got. Which is exactly
what I wanted the Colors as headings with the totals below it. This is how
I
want to pull into my report but all on one row. However, when I pull
this
in my report it doesn't look like this. It counts rows. (copy of report
outcome below) Please help.

qry Ques 2 Rev_Crosstab
Ques 2 Total Of Color White Blue Red Green
2 1 1
3 8 8
4 4 4
5 5 5


TRANSFORM Count([qry Ques 2 Rev].Satisfaction) AS CountOfColor
SELECT [qry Ques 2 Rev].[Ques 2], Count([qry Ques 2 Rev].Color) AS [Total
Of
Color]
FROM [qry Ques 2 Rev]
GROUP BY [qry Ques 2 Rev].[Ques 2]
PIVOT [qry Ques 2 Rev].Color;

Report:
White Blue Red Green
1

Duane Hookom said:
Do you have values in the Color field?
Do you have values in the Expr1000 field?
Why would you not change the column name of Expr1000 to something that
makes
sense?
Are there any records returned in the crosstab?

--
Duane Hookom
MS Access MVP


TotallyConfused said:
This is the correct one.

TRANSFORM Count([qry Ques 2 cnts].Expr1000) AS CountOfExpr1000
SELECT [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2],
Count([qry Ques 2 cnts].Expr1000) AS [Total Of Expr1000]
FROM [qry Ques 2 cnts]
GROUP BY [qry Ques 2 cnts].[CountOfQues 2], [qry Ques 2 cnts].[Ques 2]
PIVOT [qry Ques 2 cnts].Color;


:

Show us your SQL view of the crosstab.

--
Duane Hookom
MS Access MVP
--

message I have run a crosstab query and it will give me the Colors across but
it
will
not give me the totals under the heading. I need to reference this
in
a
report. Please help! What am I doing wrong?

qry Ques 2 _Crosstab
CountOfQues 2 Ques 2 White Blue Red Green
1 2 1
4 4 1
5 5 1
8 3 1


:

I have a query that give me counts of certain items. I will need
to
use
for
a subreport. However, the results of the query (ex. below)
generate
vertically and I only have two fields to pull into the report. I
need
the
results of the query to generate a column for each item and its
count.
Can I
do this in a query? If so how. I need to pull fields for each
color
and
the
count. Please help!!

qry Ques 2
Color CountOfQues 2
White 5
Blue 4
Red 8
Green 1
Click to expand...
Click to expand...
Click to expand...
 
Top