Doing calcs on query selections

[Noel]

Hi, I have a simple db which records cumulative monthly
meter readings for a range of devices. I have produced a
query and report which shows the date of reading in one
column and the reading in the next. How do I produce a
third column showing the volume for each month? I know
how to work out the overall volume but not the
intermediate ones. Thanks, Noel

 

[Michel Walsh]

Hi,

untested, but

SELECT b.meterID, b.dateTime, a.reading - b.reading as volume
FROM myTable As a INNER JOIN myTable As b
ON (a.meterID=b.meterID AND a.dateTime > b.dateTime)
WHERE a.dateTime = (SELECT MIN(c.dateTime)
FROM myTable As c
WHERE c.meterID=b.meterID
AND c.dateTime> b.dateTime)


should do. Basically, the where clause insure us that a.datetime is the
minimum date, for the given meterID, that succeed b.dateTime. Technically,
the " AND a.dateTime > b.dateTime " in the ON clause is superfluous, and
can be forgotten, but it should not hurt including it either.




Hoping it may help,
Vanderghast, Access MVP

 

[Noel]

Thanks Michel, Ill have a play with that and get back to
you. Noel

 

[Noel]

Hi again Michel. Im having trouble getting your code to
work. As Im not too good at this code stuff, Ive built a
sample db around your own choice of table and field names.
Ive built a table named myTable and given it fields
meterID (autonumber), dateTime and reading. I then built
a query, based on myTable, selected the three fields and
then entered your code in one of the field boxes. The SQL
reads as follows.

SELECT (SELECT b.meterID, b.dateTime, a.reading -
b.reading as volume
FROM myTable As a INNER JOIN myTable As b
ON (a.meterID=b.meterID AND a.dateTime >
b.dateTime)
WHERE a.dateTime = (SELECT MIN(c.dateTime)
FROM myTable As c
WHERE
c.meterID=b.meterID
AND
c.dateTime> b.dateTime)) AS Expr1, myTable.meterID,
myTable.dateTime, myTable.reading
FROM myTable;

When I run the query, I get the message You’ve written a
query that can return more than one field without using
the EXISTS reserved word in the main querys FROM clause.
Revise the SELECT statement of the Subquery to request
only one field.

My test db is Access 97 (at home) but the real db (at
work) has now been converted to Access 2002.

Where am I going wrong? Thanks for your help, Noel

 

[Michel Walsh]

Hi,


The external SELECT can't live with the inner one:

SELECT (SELECT ... ) , ...


since the inner one returns, already many results. Instead, try:



SELECT b.meterID, b.dateTime, b.reading, a.reading - b.reading as
volume
FROM myTable As a INNER JOIN myTable As b
ON (a.meterID=b.meterID AND a.dateTime > b.dateTime)
WHERE a.dateTime = (SELECT MIN(c.dateTime)
FROM myTable As c
WHERE c.meterID=b.meterID
AND c.dateTime> b.dateTime)



( I added b.reading in the SELECT ).

Hoping it may help,
Vanderghast, Access MVP

 

[Noel]

Thanks Michel. Actually I didnt create the outer Select -
my query did that all on its own so I thought it must be
correct. I think Its cos I got mixed up with my
parenthesies - I should perhaps have put SELECT
(........) but instead I put (SELECT ..............). Ill
try your suggestion tonight. Cheers, Noel

 

[Noel]

Hi Michel,

Ive tried putting the code in exactly as written but it
now says something about the subquery needing to be
enclosed in parenthesis. Are there brackets missing
somewhere? Sorry to be a bit vague - I brought a disk
copy of my sample db into work but cant open it here so I
cant quote the error message exactly. Does it suggest
anything to you? If not Ill check again at home and let
you have the full message text. Cheers, Noel

 

[Michel Walsh]

Hi,


It may be possible that Jet does not like the ( ) after the ON clause, so
you may try

ON (a.meterID=b.meterID AND a.dateTime > b.dateTime)

or

ON a.meterID=b.meterID AND a.dateTime > b.dateTime


otherwise, the WHERE clause seems right, on set of ( ) around the whole
SELECT, and one set after the MIN, that's all


WHERE a.dateTime = (
SELECT MIN(c.dateTime)
FROM myTable As c
WHERE
c.meterID=b.meterID
AND
c.dateTime> b.dateTime
)

 

[Noel]

Thanks Michel, Will try that tonight. Cheers, Noel

 

[Noel]

Hi Michel. Still cant get this to work. The message Im
getting is: The syntax of the Subquery in this expression
is incorrect. Check the Subquerys syntax and enclose the
Subquery in parentheses.

Just to repeat, Im using the code exactly as follows:

SELECT b.meterID, b.dateTime, b.reading, a.reading -
b.reading as volume

FROM myTable As a INNER JOIN myTable As b
ON (a.meterID=b.meterID AND a.dateTime > b.dateTime)
WHERE a.dateTime = (SELECT MIN(c.dateTime)
FROM myTable As c
WHERE c.meterID=b.meterID
AND

c.dateTime> b.dateTime)


Ive tried removing the () after the ON clause but still
get this message.

Any further ideas now you know the exact error message?
Again, thanks for the help. Noel

 

[Michel Walsh]

Hi,


No. Can you send me back a reduced, compacted and zipped db with just
(part of ) your table with readings ?



Vanderghast, Access MVP

 

[Noel]

Thanks. Will try to do ASAP. Noel

 

[Noel]

Michel. Have now built an Access 2002 db here in work.
Same problem. I'll send a zipped version to your E Mail
address - is vandergast@VirusAreFunnierThanSpam an EMail
address? Otherwise please advise. Its a very simple
Sample db, just one table and one Query. The real db has
two tables, one for devices, one for readings and your
code will call on data from a query that selects readings
for each device for a given date range. I can't actually
save your code in the query - it keeps bringing up the
bad Syntax message and blocks my save. Thanks for your
help. Noel

 

[Noel]

OK I give up. How do I send you the Sample db. What EMail
address do I use? Thanks, Noel

 

[Michel Walsh]

Hi,

Sorry, I changed my address since SWEN pops its ugly head. Try
msn-dot-com for the ISP provider. Vanderghast is the right thing that goes
before the commercial at.


Vanderghast, Access MVP

 

[Noel]

Have just sent it Michel. Cheers, Noel

-----Original Message-----
Hi,

Sorry, I changed my address since SWEN pops its ugly head. Try
msn-dot-com for the ISP provider. Vanderghast is the right thing that goes
before the commercial at.


Vanderghast, Access MVP




.

 

[Michel Walsh]

Hi,

The working query is on its way back to you. The only problem I saw is that
if you open the query in design view, you may loose the join, so the query
become useless in that case...


Hoping everything turns ok for you,
Vanderghast, Access MVP

 

[Noel]

Thanks Michel. Unfortunately my office system wont allow
me to get at the attached db, so Ive forwarded it to my
Hotmail address but that was over an hour ago and it
hasnt appeared yet. I hope I dont lose the join - I
really need to get into design view to build the more
complex query for my real project. Perhaps I can do most
of the work in design then paste your code into the SQL,
but what if I need to go back into design later? Could be
interesting. Will report back on progress. Cheers, Noel

 

[Noel]

Sorry about this Michel. My forwarding the EMail didnt
forward the db, only the text. Could you please resend to
my Hotmail address (e-mail address removed) Thanks again,
Noel

 

[Michel Walsh]

Hi,


Sure... I also changed the formulation a little bit, slower (imperceptible)
but you can edit it in design view....


Vanderghast, Access MVP