Help with a multicolumn report query/design question?

  • Thread starter Thread starter David
  • Start date Start date
D

David

I had posted this originally in the queries group w/o much help. So
since it is really applicable to both, I am posting it here. Any hints
are appreciated. I also want to apologize for being long winded and
reposting as opposed to originally cross posting as I intended.

I am a novice at both Access 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, but I can add tables, queries and reports at
will). 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 (where source and Dest are same as in Unit.ID). 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'. For this
query, I populate the SrcDest field with "Feeds". Then I perform a
Union on those two queries.

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.

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
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) is with 'SQL'. And I also get better at queries and
reports (which I really need.)

Any assitance is appreciated and again sorry for being long winded.

Dave
 
The way I think you could approach this is;
1. Create a main report grouped by Unit.ID wiht the unit
description etc.
2. Create a subreport linked by the Steams.Src to Units.ID
3. Create a second subreport linked by Streams.Dest to
Units.ID
You can average the values by usinfg a summing query.
I think this will get you on the way.
Fons
 
Sorry I ment a crosstab query.
Fons
-----Original Message-----
The way I think you could approach this is;
1. Create a main report grouped by Unit.ID wiht the unit
description etc.
2. Create a subreport linked by the Steams.Src to Units.ID
3. Create a second subreport linked by Streams.Dest to
Units.ID
You can average the values by usinfg a summing query.
I think this will get you on the way.
Fons program
so I can't and
reports at to
(Dest). The source
data for the days,
weeks, the flexible,
easier to
.
Click to expand...
 
Back
Top