UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Output Only Three Most Recent Records, Office 2010    
 
   
Dorv
post May 4 2012, 10:25 AM
Post #1

UtterAccess Enthusiast
Posts: 53
Man, has UA been helpful for me this week. (TheDBGuy in particular; thanks!).

So I lay my next challenge(s) at your collective feet.

I've got a report that contains five subreports, the first of two are lists of records. Except, I only want to show the three most recent records in one subreport, and the next three records scheduled (All based on a single date field) in the other subreport.

As I've mentioned in other posts, I'm trying to stay away from multiple single-use queries and handle as many of these challenges that pop up programmatically. Is there a way to handle this without creating new queries?
Go to the top of the page
 
+
theDBguy
post May 4 2012, 10:35 AM
Post #2

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
Hi,

Can you modify the Record Source of the subreports to include the TOP keyword in the SQL? Make sure the data is sorted in descending order. For example:

SELECT TOP 3 FieldName FROM TableName ORDER BY FieldName DESC

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
Dorv
post May 4 2012, 10:46 AM
Post #3

UtterAccess Enthusiast
Posts: 53
hmmm...

Currently the RecordSource is set to the Missions Query (What the records are referred to as colloquially).

So something like:

CODE
SELECT TOP 3 * FROM [Missions]


?

I should be able to set order in the SQL statement too, right?
Go to the top of the page
 
+
Dorv
post May 4 2012, 10:56 AM
Post #4

UtterAccess Enthusiast
Posts: 53
Hmmm...


CODE
SELECT TOP 3 * FROM [Angel Bus Missions];


Works in the sense that it returns three missions, but only in the order which the original query outputs them (Which makes sense).

When I try to do this:

CODE
SELECT TOP 3 * FROM [Angel Bus Missions] ORDER BY [DepartDate] DESC;


The output is actually six lines. The three missions I would expect to see output twice (ordered 1, 1, 2, 2, 3, 3)
Go to the top of the page
 
+
theDBguy
post May 4 2012, 01:00 PM
Post #5

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
QUOTE (Dorv @ May 4 2012, 08:56 AM) *
When I try to do this:

CODE
SELECT TOP 3 * FROM [Angel Bus Missions] ORDER BY [DepartDate] DESC;


The output is actually six lines. The three missions I would expect to see output twice (ordered 1, 1, 2, 2, 3, 3)

Try using that SQL in a brand new query and see if it still returns six records. Just want to check if there's anything else going on with the subreport, so let's eliminate it for now just to troubleshoot.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
Dorv
post May 4 2012, 01:44 PM
Post #6

UtterAccess Enthusiast
Posts: 53
Dropping that code into it's own query and the results were the same: 6 records show.

Testing it the same way, removing the "Order By [DepartDate] DESC" bit from the end, and it does output 3 records only (in the order in which they appear in the source query).
Go to the top of the page
 
+
theDBguy
post May 4 2012, 01:48 PM
Post #7

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
I see... You might have to post the SQL for your query so we can see what's going on with it when an ORDER BY is included.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
Dorv
post May 4 2012, 01:54 PM
Post #8

UtterAccess Enthusiast
Posts: 53
The orignal query?

Sure:

CODE
Select [PaxMission] as [Mission Number], [PaxFirst],[TripDistance], [DepartDate], [PatFirst], Year([DepartDate])-Year([PatDOB]) AS [PaxAge], [PatSex] as [PaxSex], "P" AS [Cat], [DepartCounty], [DepartState], [ArriveCounty], [ArriveState], [TicketCost]/[TicketDivider] AS [PublicBenefit], [ArriveCity], [DepartCity], [Resource], [Mode], [VetCount], iif([VetCount]>0,[TicketCost]/[TicketDivider],0) As [VetPublicBenefit], [PatMedCon] as [PMC] , [Program],[Resource]

From [Angel Bus] Where [PaxMission] <> "" and [PaxFirst] <> ""

UNION ALL Select [EscortAMission], [EscortAFirst],[TripDistance], [DepartDate], [PatFirst], [EscortAAge], [EscortASex], "E" As [Cat], [DepartCounty], [DepartState], [ArriveCounty], [ArriveState], [TicketCost]/[TicketDivider] , [ArriveCity], [DepartCity], [resource], [Mode], [VetCount], iif([VetCount]>0,[TicketCost]/[TicketDivider],0), [PatMedCon],[Program],[Resource]

From [Angel Bus] Where [EscortAMission] <> "" AND [EscortAFirst] <>""

UNION ALL Select [EscortBMission],[EscortBFirst],[TripDistance], [DepartDate], [PatFirst], [EscortBAge], [EscortBSex], "E" as [Cat], [DepartCounty], [DepartState], [ArriveCounty],[ArriveState], [TicketCost]/[TicketDivider] , [ArriveCity], [DepartCity], [resource], [Mode], [VetCount], iif([VetCount]>0,[TicketCost]/[TicketDivider],0), [PatMedCon],[Program],[Resource]

From [Angel Bus]  Where [EscortBMission] <> "" AND [EscortBFirst] <>""
WITH OWNERACCESS OPTION;
Go to the top of the page
 
+
theDBguy
post May 4 2012, 02:03 PM
Post #9

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
Hmm, so although you are using Acc2010, your database is actually a MDB, correct? Not sure what else is going on in there but I just did a quick test in 2010 and it did not duplicate the data when using the TOP keyword and the ORDER BY against a UNION query. (IMG:style_emoticons/default/iconfused.gif)

Try adding the ORDER BY in the UNION query and just use the TOP 3 in the subreport Record Source.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
Dorv
post May 4 2012, 02:12 PM
Post #10

UtterAccess Enthusiast
Posts: 53
I'm not sure I understand your question, but it is a .accdb / Access 2007 file format.

The challenge with changing the order by in the Missions query is that I use that same query in several places (Including twice on this master report: One subreport for the last three missions and one subreport for the next three upcoming missions), and changing the Order By would affect those. Not that it is ideal, but I'd rather have 6 missions shown than trying to rein in all those other cascading changes.
Go to the top of the page
 
+
theDBguy
post May 4 2012, 02:35 PM
Post #11

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
QUOTE (Dorv @ May 4 2012, 12:12 PM) *
I'm not sure I understand your question, but it is a .accdb / Access 2007 file format.

Ah, okay. I thought it was MDB because you were using WITH OWNERACCESS OPTION, which is usually only used with ULS, and ULS is not available with ACCDBs.


QUOTE
The challenge with changing the order by in the Missions query is that I use that same query in several places (Including twice on this master report: One subreport for the last three missions and one subreport for the next three upcoming missions), and changing the Order By would affect those. Not that it is ideal, but I'd rather have 6 missions shown than trying to rein in all those other cascading changes.

Again, I did a quick test but did not get the same result as you. For more testing, you could also try creating a different UNION query based on different tables and then do the TOP and ORDER BY against it just to see if the problem is something about the original UNION query, or your database file itself.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

PS. By the way, are the results of the TOP 3 query "really" duplicates? I mean, all the fields in the resultset are exact matches? Just wondering if it's got something to do with the date values in the data.

This post has been edited by theDBguy: May 4 2012, 02:39 PM
Go to the top of the page
 
+
Dorv
post May 4 2012, 02:43 PM
Post #12

UtterAccess Enthusiast
Posts: 53
No, they're not duplicates as a matter of fact (They just originally looked liked they were).

I think the result of the "Top 3" is showing the results of any records that match the "Top 3" dates.
Go to the top of the page
 
+
theDBguy
post May 4 2012, 02:48 PM
Post #13

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
Hmm, okay, let's try the following:

1. Create a query like this:

SELECT * FROM [Angel Bus Missions] ORDER BY [DepartDate] DESC;

2. Write down or remember the first six records (on top) of the result.
3. Now, modify the query to add the TOP 3:

SELECT TOP 3 * FROM [Angel Bus Missions] ORDER BY [DepartDate] DESC;

Compare the result with the results you've written down from step 1. Do they make sense?
Go to the top of the page
 
+
Dorv
post May 5 2012, 07:17 AM
Post #14

UtterAccess Enthusiast
Posts: 53
The first several (well, 6 really) records that show up without the "Top 3" qualifier are the same records that are output w/ the "Top 3" qualifier.

I had thought that the output was somehow tied to the top 3 dates, but the records that appear are spread out over two days (5/9 and 5/2), not three.
Go to the top of the page
 
+
theDBguy
post May 5 2012, 09:52 AM
Post #15

Access Wiki and Forums Moderator
Posts: 48,003
From: SoCal, USA
Okay, well it would be hard for me to continue guessing what's going on with your data unless I can see them. Are you able to post a zip copy of your db with test data?

By the way, did you also try the experiment I mentioned earlier to create a separate UNION query based on some other tables to see if you get the same result?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
« Next Oldest · Access Reports · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 22nd May 2013 - 02:55 PM