Query expression using multiple records in same table

[Rick]

I know this will probably be something simple but it is beyond my
current experience. I have a table and I want to use a make table
query that will create a calculated field called pageDeltas. The
existing fields are printerID, pollDate, and pageCount. PageDeltas
will be the delta of the pageCount between pollDates. Each record
will have a different pollDate. The table will accumulate pageCount
data for many different printers so I would like to sort the data on
the printerID with ascending pollDates.

Thanks for the help

 

[Allen Browne]

Chances are you will need a subquery to accumulate the progressive count.

Here's a starting point:
Subquery basics:
at:
http://allenbrowne.com/subquery-01.html

 

[Rick]

Chances are you will need a subquery to accumulate the progressive count.

Here's a starting point:
Subquery basics:
at:
http://allenbrowne.com/subquery-01.html

Thanks for the pointer. I have already looked at your website and
appreciate all the info you have make available. I have a couple of
questions. First, will the query work if I include the fields I
mentioned and then set the printerID and pollDate to be in ascending
order? Next, in your example;

SELECT MeterReading.ID,
MeterReading.ReadDate,
MeterReading.MeterValue,
(SELECT TOP 1 Dupe.MeterValue
FROM MeterReading AS Dupe
WHERE Dupe.AddressID = MeterReading.AddressID
AND Dupe.ReadDate < MeterReading.ReadDate
ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue
FROM MeterReading;

when you refer to the dupe table (Dupe.MeterValue) does Access create
this on it's own or do I have to create it in the QBE?

Sorry for being so ignorant but I have never had to use subqueries
before and this is all new to me.

Thanks.

 

[Allen Browne]

The query is independent of how you sort your report, so there is no issue
there. However, when you use the results of the query for the repoprt, you
may get a "Multi-level group by not allowed" error. Details:
http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

To create the subquery, you must type the entire subquery expression into
the query. Access doesn't do this for you.

 

[Rick]

The query is independent of how you sort your report, so there is no issue
there. However, when you use the results of the query for the repoprt, you
may get a "Multi-level group by not allowed" error. Details:
http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

To create the subquery, you must type the entire subquery expression into
the query. Access doesn't do this for you.

I just finished adapting your SQL and it ran absolutely perfect the
first time!! I have been fighting this one for more than 3 weeks!! I
also downloaded the Search Sample Database that was on your
website...once again you solved some major stumbling blocks. Thanks
for the help.

 

[Allen Browne]

Good news!

Glad you are able to put the Search example to good use too.

Combining the Search example (generating the WHERE clause dynamically) with
subqueries (ability to search other tables as well) gives you some very
powerful form/report search/filtering.