If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I only show part of a list in a report?
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 |
#2
|
|||
|
|||
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 http://www.members.shaw.ca/AlbertKallal " |
#3
|
|||
|
|||
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 "Mike" wrote: 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 |
#4
|
|||
|
|||
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. "Mike" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting Groups in a Report | liz217 | General Discussion | 4 | July 5th, 2005 09:51 PM |
using a footer total in details section | jkendrick75 | Setting Up & Running Reports | 15 | January 5th, 2005 08:55 PM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 02:19 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |