How can I only show part of a list in a report?

[Mike]

I have a report within another report. How can I tell the report within to
only show the last few records?

A customer has 50 orders, I want to only show the last 5 orders for that
customer within the report. By default the reports shows all the orders. I
ca not seem to find a way to only show the last 5 records.

I tried using the TOP 5 but it might only show 1 record when there are
actually 1000 records.

SELECT TOP 5 ProductCodes.ProductCode, TankInfo.TankNumber,
ProductCodes.ProductShortDescr, ProductCodes.ProductLongDescr,
ProductCodes.SpecificGravity, TankInfo.TankID, TankInventory.MeasuredDate,
TankInventory.MeasuredHeightFT, TankInventory.MeasuredHeightIN,
TankInventory.Temperature, TankInventory.Dept, TankInventory.Gravity
FROM TankInfo INNER JOIN (ProductCodes INNER JOIN TankInventory ON
ProductCodes.ProductCodeID = TankInventory.ProductCodeID) ON TankInfo.TankID
= TankInventory.TankID
ORDER BY TankInventory.MeasuredDate DESC;

Thank you in advance for your help.
Mike

 

[Albert D.Kallal]

The "top" keys its info off of the order by clause.

If you order by date, and you have 50 of the same date, then you get all 50,
even when you ask for 5 (since, the top date is the SAME).

So, just introduce a autonumber field into the order by

ORDER BY TankInventory.MeasuredDate DESC, DESC id

In fact, the above is why I ALWAYS have a primary key field (autonumber) in
my child tables. Often, you don't have any additional tables related to
those child tables (a child child table!), but for order by, and retrieving
the last order, you need the autonumber when there can be multiple dates
etc.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

"

 

[Guest]

Mike:

One option, assuming the order numbers can be sorted Asc/Desc, would be to
create a temporary list of customer orders, sorted descending, and apply a
second number to them (1 thru whatever). Then simply choose records <= 5.

Sharkbyte

 

[Guest]

As this is being used as the recordsource for a subreport I think you'll have
to reference as a parameter in the query's WHERE clause whatever in the
parent report the LinkMasterFields of the subreport control points to e.g.

WHERE SomeField = Reports!YourParentReport!SomeControl

This should restrict the query's result set to those rows where the field's
value matches the current value in the parent report and the TOP predicate
will, I think, be applied to that result set. It follows from this of course
that you don't in fact need to link the subreport to the parent report via
the LinkMasterFields and LinkChildFields properties as the use of the
parameter replaces the need for this.

While I've frequently referenced a parent report's control in a subreport's
underlying query in this way I've never done it in conjunction with the TOP
predicate as far as I can recall, but I think it should do the trick.