Cross-tab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I have a cross-tab query. In this query, I used City as row heading and
Month as column heading. However the resule is something like this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
 
Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total Submitted]
PIVOT Query_Rpt2.Month;
Thanks!
 
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Vincdc said:
Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

Duane Hookom said:
Yes. First, show us your SQL View.
Click to expand...
Click to expand...
 
Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

Duane Hookom said:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Vincdc said:
Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

Duane Hookom said:
Yes. First, show us your SQL View.

--
Duane Hookom
MS Access MVP
--

Hello:
I have a cross-tab query. In this query, I used City as row heading and
Month as column heading. However the resule is something like this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Why do you need them in the group by? I believe including them will create
results like you wanted to avoid.

--
Duane Hookom
MS Access MVP
--

Vincdc said:
Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

Duane Hookom said:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Vincdc said:
Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

:

Yes. First, show us your SQL View.
Click to expand...
heading
and
Month as column heading. However the resule is something like this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
If I do not include these two under Group By, the query will not calculate
the ratio for me. I also tried to add the ratio under Group By; it still does
not show it correctly.

Duane Hookom said:
Why do you need them in the group by? I believe including them will create
results like you wanted to avoid.

--
Duane Hookom
MS Access MVP
--

Vincdc said:
Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

Duane Hookom said:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

:

Yes. First, show us your SQL View.

--
Duane Hookom
MS Access MVP
--

Hello:
I have a cross-tab query. In this query, I used City as row heading
and
Month as column heading. However the resule is something like this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Try:
TRANSFORM Sum([Yes])/Sum([Total Submitted]) AS Ratio
SELECT City
FROM Query_Rpt2
GROUP BY City
PIVOT [Month];


--
Duane Hookom
MS Access MVP


Vincdc said:
If I do not include these two under Group By, the query will not calculate
the ratio for me. I also tried to add the ratio under Group By; it still does
not show it correctly.

Duane Hookom said:
Why do you need them in the group by? I believe including them will create
results like you wanted to avoid.

--
Duane Hookom
MS Access MVP
--

Vincdc said:
Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

:

TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

:

Yes. First, show us your SQL View.

--
Duane Hookom
MS Access MVP
--

Hello:
I have a cross-tab query. In this query, I used City as row heading
and
Month as column heading. However the resule is something like this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
This one works. Thanks a lot for your help!!!
I am just wondering how it works? Why use SUM function?

Duane Hookom said:
Try:
TRANSFORM Sum([Yes])/Sum([Total Submitted]) AS Ratio
SELECT City
FROM Query_Rpt2
GROUP BY City
PIVOT [Month];


--
Duane Hookom
MS Access MVP


Vincdc said:
If I do not include these two under Group By, the query will not calculate
the ratio for me. I also tried to add the ratio under Group By; it still does
not show it correctly.

Duane Hookom said:
Why do you need them in the group by? I believe including them will create
results like you wanted to avoid.

--
Duane Hookom
MS Access MVP
--

Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

:

TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total
Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

:

Yes. First, show us your SQL View.

--
Duane Hookom
MS Access MVP
--

Hello:
I have a cross-tab query. In this query, I used City as row
heading
and
Month as column heading. However the resule is something like this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Isn't the sum what you wanted?

--
Duane Hookom
MS Access MVP


Vincdc said:
This one works. Thanks a lot for your help!!!
I am just wondering how it works? Why use SUM function?

Duane Hookom said:
Try:
TRANSFORM Sum([Yes])/Sum([Total Submitted]) AS Ratio
SELECT City
FROM Query_Rpt2
GROUP BY City
PIVOT [Month];


--
Duane Hookom
MS Access MVP


Vincdc said:
If I do not include these two under Group By, the query will not calculate
the ratio for me. I also tried to add the ratio under Group By; it
Click to expand...
still
does
not show it correctly.

:

Why do you need them in the group by? I believe including them will create
results like you wanted to avoid.

--
Duane Hookom
MS Access MVP
--

Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

:

TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN ("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total
Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

:

Yes. First, show us your SQL View.

--
Duane Hookom
MS Access MVP
--

Hello:
I have a cross-tab query. In this query, I used City as row
heading
and
Month as column heading. However the resule is something
Click to expand...
like
this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Thanks again! I figured it out why use SUM function here.

Duane Hookom said:
Isn't the sum what you wanted?

--
Duane Hookom
MS Access MVP


Vincdc said:
This one works. Thanks a lot for your help!!!
I am just wondering how it works? Why use SUM function?

Duane Hookom said:
Try:
TRANSFORM Sum([Yes])/Sum([Total Submitted]) AS Ratio
SELECT City
FROM Query_Rpt2
GROUP BY City
PIVOT [Month];


--
Duane Hookom
MS Access MVP


If I do not include these two under Group By, the query will not calculate
the ratio for me. I also tried to add the ratio under Group By; it still
does
not show it correctly.

:

Why do you need them in the group by? I believe including them will
create
results like you wanted to avoid.

--
Duane Hookom
MS Access MVP
--

Hello:
This one does not work as Query_Rpt2.[Yes] and QueryML_Rpt2.[Total
Submitted] are not included in the Group BY. Any suggestion?
Thanks!

:

TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month;
or
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City
PIVOT Query_Rpt2.Month IN
("January","February","March",..."December");


--
Duane Hookom
MS Access MVP


Here is the SQL:
TRANSFORM (Query_Rpt2.[Yes]/QueryML_Rpt2.[Total Submitted]) AS
Ratio
SELECT Query_Rpt2.City
FROM Query_Rpt2
GROUP BY Query_Rpt2.City, Query_Rpt2.[Yes], Query_Rpt2.[Total
Submitted]
PIVOT Query_Rpt2.Month;
Thanks!

:

Yes. First, show us your SQL View.

--
Duane Hookom
MS Access MVP
--

Hello:
I have a cross-tab query. In this query, I used City as row
heading
and
Month as column heading. However the resule is something like
this
May June July Aug Sep
LA 1
LA 1
LA 3
LA 2
LA 1
Is there anyway that I can show data in one row for LA?
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top