This discussion should likely be moved to the query group but I have used
"Union All" to combine queries but that brought up some "head scratchers"
for me when I tried to use it.
For one thing. Is it possible to combine queries with different number of
columns? As I've said I'm working with weather data. Some have a time
element and some don't.
Let me show you an example. It's very likely this will not wrap correctly
the lines are quite long. I'm trying to let Access actually do what takes a
number of fortran programs tied together to do.
This is what's called an Operational Climate Data Summary (OCDS). It's 4
pages long but I'm just going to paste in pieces of the creature. I have
generated Access queries which can accomplish almost every item in the
thing.
OPERATIONAL CLIMATIC DATA SUMMARY
STATION: ASHEVILLE MUNI NORTH CAROLINA STATION #: 723150 ICAO:
KAVL
LOCATION: 3526N 08233W ELEVATION (FEET): 2169 LST =
GMT -5
PREPARED BY: AFCCC/DOS, FEB 1998 PERIOD: 7301-9612
---------------------------------------------------------------------------
---------
SOURCE NO. JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV
DEC ANN
1. TEMPERATURE ( F)
EXTREME MAX 1 78 79 83 89 93 99 101 100 96 88 81
81 101
MEAN DAILY MAX 1 46 50 58 66 74 80 84 82 76 67 58
50 66
MEAN 1 36 39 47 54 62 69 73 71 65 55 46
39 55
MEAN DAILY MIN 1 28 30 37 44 53 60 64 64 58 45 38
31 46
EXTREME MIN 1 -16 -2 2 22 28 35 44 42 30 16
8 -7 -16
# DAYS GE 90 1 0 0 0 0 # 1 4 2 # 0 0
0 7
# DAYS LE 32 1 20 16 10 3 # 0 0 0 # 3 11
18 81
# DAYS LE 0 1 # # 0 0 0 0 0 0 0 0 0
# #
This is the first paragraph. For one thing I found that if I included a
month from a combined query I've been building. ie, [Jan] it immedially
repeats the whole column, all 8 combined parameters. Any label I give it (ie
Extreme Max) is repeated 8 times for all the other lines. No obvious way to
tell it otherwise.
Then there's this:
7. PERCENTAGE FREQUENCY OF OCCURRENCE (% FREQ) OF CEILING AND/OR VISIBILITY
(CIG/VIS) LT 3000/3 STATUTE MILES (MI) (SOURCE NO. 1)
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
ANN
00-02 LST 31 27 25 16 24 27 26 40 40 28 29 28
28
03-05 LST 34 29 30 19 35 41 46 63 58 40 32 31
38
06-08 LST 35 32 30 20 33 42 50 70 64 42 36 33
41
09-11 LST 31 28 26 16 18 16 16 26 30 24 28 27
24
12-14 LST 23 21 19 13 14 8 9 12 17 14 20 22
16
15-17 LST 20 17 17 11 11 7 7 9 14 12 19 19
14
18-20 LST 23 19 17 11 10 7 7 9 16 13 20 22
14
21-23 LST 26 23 20 13 15 13 12 17 22 17 23 25
19
ALL HOURS 28 25 23 15 20 20 21 31 33 24 26 26
24
I have a query that spits this out nicely. But it includes a time parameter
that the monthly temperatures do not.
So is it possible in a Union query to include a "blank" column in one so it
can line up with others. Is it possible to include some type of "labeled"
column up front.
And/or can I stop Access from thinking that if put in [Jan] to immediately
list in the column every Jan value but to actually let me specify which one
I'm talking about. As you can see if I could just "assemble" my multiple
queries in a Report it would be a snap. Having to build some type of monster
all inclusive query first is a pain.
Marshall Barton said:
mcl said:
Accomplishing what I want to do would be trivial if I could combine the
output of multiple tables/queries in a report. Without that capacity it's a
project breaker at least for someone with the level of expertise I have.
Do you know what a query Join is? How do you want to
combine the data from multiple tables/querys? In just about
any modern relational database system, the language used to
express the data relationships and retrieval is SQL. Are
you familar with it? If not, we out here in the newsgroups
will be able to provide more specific suggestions if you
would describe your data tables/querys and **what** you want
to accomplish in more detail instead of asking how to do
what you think should be the "right" way.
Is MS interested at all at adding functionality and ease of use to their
applications?
Is that a non-productive expression of your frustration in
learning to work in an unfamiliar environment? Even it that
kind of philosophical question were useful, the folks that
volunteer to help others in these newsgroups are in no way
qualified to answer it.
--
Marsh
MVP [MS Access]
mcl wrote:
I have asked this before and I still can't believe I can't do it. If I right
click on a text box, select properties and control source it will not
let
me
specify a control source other that the "ONE" I initially chose. If I can't
combine multiple sources in one Report then the MS Access Report generater
is underpowered useless garbage. I have tried entering [Table or
Query].[parameter] and [Table or Query]![parameter] and both times I
get
the
following error when I try to run it: " The Micrsoft Jet Database engine
does not recognize "what I entered" as a valid field name or expression.
What am I supposed to put there to do this. I'm going to give MS the benefit
of the doubt and assume I'm just using the wrong syntax rather that the
alternate that MS Access sucks.
"Marshall Barton" wrote
I guess Access must suck and all the rest of us that have
been using it are all wet.
A control can only be bound to a field in the report's
record source table/query of an expression consisting of
those field and/or other controls and/or constants.
If you want to pull data from two or more related tables,
then you can create a query that Joins the tables and makes
all of their fields available to the report.
If you just want to retrieve a value from a table, then you
can use the DLookup function in an expression.
