Big problem with Where Clause URGENT PLS HELP!

  • Thread starter Thread starter GR
  • Start date Start date
G

GR

Hi, I have a report. The report has the following
groupings levels

a. Project
b. Readings

Then in each group I have the Project name. for each
project I have Services done on it on different days.
In the readings group I see all the services dates and the
readings for each of them (for the corresponding project).

E.g.

Project A
Air Gap
21.01.2003 Mill Drive 1 2.35
05.02.2003 Mill Drive 1 4.35
10.03.2003 Mill Drive 1 3.35
Motor Voltage
21.01.2003 Mill Drive 1 500
05.02.2003 Mill Drive 1 550
10.03.2003 Mill Drive 1 480

Project B

Air Gap
..... similar as Project A ....

Now, in the Reading Group footer I placed a Chart. In this
chart I only want to display the values (in order to see
the trend) for each reading. The problem is that the chart
displays the values for all the readings (e.g. For motor
voltage and air gap, instead that for only Air Gap)

The underlying query of the chart is:
TRANSFORM Sum([Value]) AS [SumOfValue] SELECT [SubProduct]
FROM [qryTrendVMS] GROUP BY [SubProduct] PIVOT [Readings] ;

Since this query didn't give the chart I wanted I think I
need something like:
TRANSFORM Sum([Value]) AS [SumOfValue] SELECT [SubProduct]
FROM [qryTrendVMS] GROUP BY [SubProduct] PIVOT [Readings]
WHERE [qryTrendVMS].[Reading] = ?????;

where ?????? should be the Reading (Air Gap or Motor
voltage or current, etc etc) so that the chart displays
values only for that reading.
a Where clause like
WHERE [qryTrendVMS].[Reading] = "Air Gap x [mm]"
won't be desirable since the readings will vary depending
on user input.

How can I make this query work??

thx.
 
Dear GR:

You do not disclose much about what is in your tables. Is there
something in your Readings table that would allow you to choose what
kind of readings you have?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top