Using Output from Multiple Queries in a Report

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

I've mostly worked with queries where it's very easy to to include the
output from many tables and/or queries in a new query.
If this is possible to do in reports it's sure not as obvious as it is in
queries. Can it be done and if so how?
 
In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
 
I can't get it to work. I'm trying to pull output from several crosstab
queries.
When I change the source it tries to change it for all. if I right click on
a particular field and change it. It gives me does not recognize it as a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work. I've
seen Access try to use [queryname]![field]. But that doesn't work either.
It's temperature data. I have crosstab queries like Max_T(the max_T has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and display
like this:
Jan Feb Mar Apr May Jun Jul Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a great job
of crunching this climate data. I want to now put it all together into a
rather lengthy report. This temp data portion would only be one small part
of it but I can't even get it working. Once I figure out what I'm doing
wrong I could build on it and get it all done.
 
I have had no experience using crosstab querries as source
data for other queries. Maybe someone else can help???
Fons
-----Original Message-----
I can't get it to work. I'm trying to pull output from several crosstab
queries.
When I change the source it tries to change it for all. if I right click on
a particular field and change it. It gives me does not recognize it as a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work. I've
seen Access try to use [queryname]![field]. But that doesn't work either.
It's temperature data. I have crosstab queries like Max_T (the max_T has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and display
like this:
Jan Feb Mar Apr May Jun Jul Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a great job
of crunching this climate data. I want to now put it all together into a
rather lengthy report. This temp data portion would only be one small part
of it but I can't even get it working. Once I figure out what I'm doing
wrong I could build on it and get it all done.


In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
Click to expand...


.
Click to expand...
 
It looks like your crosstabs only include 1 record each. You can merge them
together using a UNION query.
SELECT Jan, Feb, Mar,...
FROM Max_T
UNION ALL
SELECT Jan, Feb, Mar,...
FROM MeanMaxT
UNION ALL
....

--
Duane Hookom
MS Access MVP


mcl said:
I can't get it to work. I'm trying to pull output from several crosstab
queries.
When I change the source it tries to change it for all. if I right click on
a particular field and change it. It gives me does not recognize it as a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work. I've
seen Access try to use [queryname]![field]. But that doesn't work either.
It's temperature data. I have crosstab queries like Max_T(the max_T has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and display
like this:
Jan Feb Mar Apr May Jun Jul Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a great job
of crunching this climate data. I want to now put it all together into a
rather lengthy report. This temp data portion would only be one small part
of it but I can't even get it working. Once I figure out what I'm doing
wrong I could build on it and get it all done.


Fons Ponsioen said:
In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
Click to expand...
Click to expand...
 
I have never done a union query before. Using the MS Help I was actually
able to do it. Haven't tried to use it in the report yet but I have a
question. Since the union query can only be done in SQL view, how to you do
things like fix decimal places? In this case I want it fixed to "0" decimal
places (should this discussion be shifted to the queries newsgroup(;-)).

Duane Hookom said:
It looks like your crosstabs only include 1 record each. You can merge them
together using a UNION query.
SELECT Jan, Feb, Mar,...
FROM Max_T
UNION ALL
SELECT Jan, Feb, Mar,...
FROM MeanMaxT
UNION ALL
...

--
Duane Hookom
MS Access MVP


mcl said:
I can't get it to work. I'm trying to pull output from several crosstab
queries.
When I change the source it tries to change it for all. if I right click on
a particular field and change it. It gives me does not recognize it as a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work. I've
seen Access try to use [queryname]![field]. But that doesn't work either.
It's temperature data. I have crosstab queries like Max_T(the max_T has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and display
like this:
Jan Feb Mar Apr May Jun Jul Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a great job
of crunching this climate data. I want to now put it all together into a
rather lengthy report. This temp data portion would only be one small part
of it but I can't even get it working. Once I figure out what I'm doing
wrong I could build on it and get it all done.


Fons Ponsioen said:
In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
-----Original Message-----
I've mostly worked with queries where it's very easy to
to include the
output from many tables and/or queries in a new query.
If this is possible to do in reports it's sure not as
obvious as it is in
queries. Can it be done and if so how?


.
Click to expand...
Click to expand...
Click to expand...
 
Actually, I never format my data in queries. All formatting is done in the
report or form controls.

--
Duane Hookom
Microsoft Access MVP


mcl said:
I have never done a union query before. Using the MS Help I was actually
able to do it. Haven't tried to use it in the report yet but I have a
question. Since the union query can only be done in SQL view, how to you do
things like fix decimal places? In this case I want it fixed to "0" decimal
places (should this discussion be shifted to the queries newsgroup(;-)).

Duane Hookom said:
It looks like your crosstabs only include 1 record each. You can merge them
together using a UNION query.
SELECT Jan, Feb, Mar,...
FROM Max_T
UNION ALL
SELECT Jan, Feb, Mar,...
FROM MeanMaxT
UNION ALL
...

--
Duane Hookom
MS Access MVP


mcl said:
I can't get it to work. I'm trying to pull output from several crosstab
queries.
When I change the source it tries to change it for all. if I right
Click to expand...
click
on
a particular field and change it. It gives me does not recognize it as a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work. I've
seen Access try to use [queryname]![field]. But that doesn't work either.
It's temperature data. I have crosstab queries like Max_T(the max_T has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and display
like this:
Jan Feb Mar Apr May Jun Jul Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a
Click to expand...
great
job
of crunching this climate data. I want to now put it all together into a
rather lengthy report. This temp data portion would only be one small part
of it but I can't even get it working. Once I figure out what I'm doing
wrong I could build on it and get it all done.


In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
-----Original Message-----
I've mostly worked with queries where it's very easy to
to include the
output from many tables and/or queries in a new query.
If this is possible to do in reports it's sure not as
obvious as it is in
queries. Can it be done and if so how?


.
Click to expand...
Click to expand...
Click to expand...
 
So no one knows how to do it in this case or is there just no way to do it
in a union query?

Duane Hookom said:
Actually, I never format my data in queries. All formatting is done in the
report or form controls.

--
Duane Hookom
Microsoft Access MVP


mcl said:
I have never done a union query before. Using the MS Help I was actually
able to do it. Haven't tried to use it in the report yet but I have a
question. Since the union query can only be done in SQL view, how to you do
things like fix decimal places? In this case I want it fixed to "0" decimal
places (should this discussion be shifted to the queries newsgroup(;-)).
Click to expand...
as
a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to
Click to expand...
work.
I've
seen Access try to use [queryname]![field]. But that doesn't work either.
It's temperature data. I have crosstab queries like Max_T(the max_T has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and display
like this:
Jan Feb Mar Apr May Jun Jul Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a great
job
of crunching this climate data. I want to now put it all together
Click to expand...
Click to expand...
into
a small
part
Click to expand...
Click to expand...
 
Wellll, I was able to do it with "Format" as in Format([Jan],"##0), etc.
However, that set the column heading to exp1001. Doing a Format([Jan],"##0)
as Jan didn't work because of the muliple Month names. So I had to do this
type of stuff (a lot of typing although I did a lot of copying and pasting):
Format([Mean_T].[Jan],"##0) as Jan. Let's see this type of stuff for each
month + Annual (13) for all 5 Queries (65 total). It works but it was a
pain.

mcl said:
So no one knows how to do it in this case or is there just no way to do it
in a union query?

Duane Hookom said:
Actually, I never format my data in queries. All formatting is done in the
report or form controls.

--
Duane Hookom
Microsoft Access MVP


you
do
Click to expand...
it
as
a
valid filed name or expression. What am I doing wrong? What is the exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work.
I've
seen Access try to use [queryname]![field]. But that doesn't work
either.
It's temperature data. I have crosstab queries like Max_T(the
Click to expand...
max_T
has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and
display
like this:
Jan Feb Mar Apr May Jun Jul
Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a great
job
of crunching this climate data. I want to now put it all together
Click to expand...
into
a
rather lengthy report. This temp data portion would only be one small
part
of it but I can't even get it working. Once I figure out what I'm doing
wrong I could build on it and get it all done.


In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
-----Original Message-----
I've mostly worked with queries where it's very easy to
to include the
output from many tables and/or queries in a new query.
If this is possible to do in reports it's sure not as
obvious as it is in
queries. Can it be done and if so how?


.
Click to expand...
Click to expand...
Click to expand...
 
Well,
Round also works.

mcl said:
Wellll, I was able to do it with "Format" as in Format([Jan],"##0), etc.
However, that set the column heading to exp1001. Doing a Format([Jan],"##0)
as Jan didn't work because of the muliple Month names. So I had to do this
type of stuff (a lot of typing although I did a lot of copying and pasting):
Format([Mean_T].[Jan],"##0) as Jan. Let's see this type of stuff for each
month + Annual (13) for all 5 Queries (65 total). It works but it was a
pain.

mcl said:
So no one knows how to do it in this case or is there just no way to do it
in a union query?

Duane Hookom said:
Actually, I never format my data in queries. All formatting is done in the
report or form controls.

--
Duane Hookom
Microsoft Access MVP


I have never done a union query before. Using the MS Help I was actually
able to do it. Haven't tried to use it in the report yet but I have a
question. Since the union query can only be done in SQL view, how to you
do
things like fix decimal places? In this case I want it fixed to "0"
decimal
places (should this discussion be shifted to the queries newsgroup(;-)).

It looks like your crosstabs only include 1 record each. You can merge
them
together using a UNION query.
SELECT Jan, Feb, Mar,...
FROM Max_T
UNION ALL
SELECT Jan, Feb, Mar,...
FROM MeanMaxT
UNION ALL
...

--
Duane Hookom
MS Access MVP


I can't get it to work. I'm trying to pull output from several
crosstab
queries.
When I change the source it tries to change it for all. if I right
click
on
a particular field and change it. It gives me does not recognize
Click to expand...
it
as
a
valid filed name or expression. What am I doing wrong? What is the
exact
format I need to use to specify a query & field. In queries I use
[queryname].[field] (ie: [Max_T].[Jan]). That doesn't seem to work.
I've
seen Access try to use [queryname]![field]. But that doesn't work
either.
It's temperature data. I have crosstab queries like Max_T(the max_T
has
columns for Jan through Dec with also an annual.
There are also: Mean_Max_T crosstabs, Mean_T, Mean_Min_T, & Min_T.

So I want the report to pull the data from 5 crosstab queries and
display
like this:
Jan Feb Mar Apr May Jun Jul
Aug
Sep Oct Nov Dec Annual
Max T 55 etc
Mean Max T 40 etc
Mean T 30
Mean Min T 20
Min T -2

I think you can get the drift from this. My crosstab queries do a
great
job
of crunching this climate data. I want to now put it all
Click to expand...
together
into
a
rather lengthy report. This temp data portion would only be one small
part
of it but I can't even get it working. Once I figure out what I'm
doing
wrong I could build on it and get it all done.


In the report design view, go to the source data, here you
can either select an existing table or query, or you can
click on the little box with dots to the right of the
source box (once you click in it)here you can create your
own query unique to this reportwhere you cna use tables
and/or queries as your datasource.
Hope this helps.
Fons
-----Original Message-----
I've mostly worked with queries where it's very easy to
to include the
output from many tables and/or queries in a new query.
If this is possible to do in reports it's sure not as
obvious as it is in
queries. Can it be done and if so how?


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top