This might be a report question but I think it is more a query issue
than a report. I am a novice at bothAccess reporting and queries and
have been struggling to build the following. (Note using Access2000)
I have three tables (that are part of a different program so I can't
change their structure). They are Units, Streams, and _Strms. Units
contains info about various units.
The fields within Units that I am interested in are ID, Description,
and Type.
The fields in Streams I am working with are ID, Description, Src and
Dest. These describe the stream and which unit it comes from (Src) and
goes to (Dest). The source and dest match up with records in Units.
All streams have a source, some may not have a dest.
The third table (_Strms) contains the stream values for particular
days. The fields I need to access are ID (same as in Streams), Date_,
Prop and Value_.
What I am trying to get is (eventually) a report which looks something
like:
Unit.ID Unit.Description
Feeds
Streams.ID Streams.Description Average1 Average2 Average3
. . . . .
. . . . .
. . . . .
Products
Streams.ID Streams.Description Average1 Average2 Average3
. . . . .
. . . . .
. . . . .
Next Unit.ID
etc.
The averages are the average of the stream value (Strms.Value_) for a
particular property (Strms.Prop) where the unit type (Units.Type) is
XXX (a particular type) where the stream values are averaged across
three date ranges (entered as paramaters to the query/report).
I have been able to get close. I can do a query for 'src=unit.id AND
type=XXX AND Prop=YYY AND within a data range' and pull the Values_
data. I also add/populate a field, SrcDest, with "Products". Then do a
second query which is identical except for 'Dest=Unit.ID' and I
populate SrcDest with "Feeds". Then I perform a Union on those two
queires.
This union (and the underlying queries) are used a source data for the
report where I group accordingly and aggregate (actually Average) but
I don't show the individual returned records only the aggregated
group (i.e. I leave out the details section and only show the group
footer aggregate information.)
The problem I have is this only gets me one period of averages and I
would like to get three as we typically report 3 months at a time
(though it in theory could be any 3 periods be they days, weeks, the
first 15 days of the last 3 months, etc.)
Sorry for being so long winded. Can someone point me in the right
direction to get the multiple period averages? I know I could do this
pretty easily in VBA (where I am much more at home) but I have the
feeling the better way (and faster?, should be easier, more flexible,
easier to support by others, etc) is with 'SQL'. It seems I have/do
see reports like this frequently so it is likely to be more my
ignorance than actually difficult.
Any assitance is appreciated.
