Limiting number of instances in output

[dehere]

Okay, here's one for the access whizz guys out there. Is there some way that I can limit the number of entries that show in a report? That is a very confusing question I suspect but I'll try to explain.

I am printing out a profile report. When I do this for just one track, surface and distance I can easily do what I want by setting a limit on the number of entries on the top-center of the query design view (the drop down box that lets you limit output to 5, 25, 100 or whatever).

The problem I am having is when I do not limit the output to that "one track, one surface and one distance" deal. If I impose the limitation in the query design view I end up just getting the first 25 (or whatever) entries which do not even make it through one track.

So, I'm curious if there is a workaround that I can set as a criteria or such. For example, I can limit the output to a certain extent by putting in a criteria like this to get the records for just the past 30 days -

>Date()-30

That limitation works in most instances but in several instances 70 or more races are run at 6.0 furlongs on fast dirt at many tracks.

I like to see what the trend has been over the past 30 or so starts at each distance and surface, but it gets a bit overwhelming when seeing 70 and more races showing up.

Anyway, anyone have any thoughts? (sorry about the length of all of this).

[Donnie]

Yeah Henry.....

(but you really should take tonight and enjoy a nice bottle of wine and a big thick cigar and ponder the events of the past year.....but WTF...time is wasting, right??)

This is done in the report, not in the query!

Place a textbox in the Detail Section with the following properties:
Name: GroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

In the On Format event for the detail section put:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If GroupCount > 4 then Me.Detail.Visible = False Else Me.Detail.Visible = True
End Sub

Let me know if you have any problems with this! I use it to limit my contenders to 4. Where you see the 4 above, change it to the number of records you want to see for each group.

[DanG]

I was hoping someone would answer this.

Thanks for the question Henry (something I always wanted to know how to do.) and great tip Donnie!!!

PS: Big congratulations for Iowa’s own Maggie Moss on leading owner in the country!!! You spotted her way before the rest of us Donnie.;)

[Donnie]

...and if you took the Hawks and the points yesterday you were another big winner!! They gave Texas a scare yesterday!

[DanG]

Nice;

Between the Hawkeye’s, Moss, HTR and EBay Santa’s been good to Donnie and family this year!:)

[Donnie]

We're not complaining none too loud around the ranch, Dan! Overall, been a good year! Excited about the new one starting!

[dehere]

Thanks for the suggestion Donnie - unfortunately, it did not work for me. When I tried doing as you suggested I get an error message about a missing macro. I've enclosed a screen shot of the error message I'm getting.

Oh, by the way, the spelling error for integer (shows as interger on the screenshot) was corrected - but it still didn't work.

Any thoughts?

[MikeDee]

not real sure what you are doing so this may not help but you mentioned trying to limit the number of rows based on a date

i.e.
>Date()-30


how about this
Expr1: DateDiff("d",Date(),[mytable]![tDATE])

the date diff function finds the number of days between two dates

d is the interval (in this example days)
date() is a access function the returns today's system date

[mytable]![tdate] this would be a field in your table that has a date in it from a htr table.

This will give you a number in the table that is the number days from the date in the row from today

you could then set this to a criteria like <=30 and this would then limit the number of rows displayed in the query.

[dehere]

Mike - Thanks for the reply. I think Donnie had the idea in this post, but I just haven't implemented it correctly yet.

I am not trying to limit the number of days at this point. I have already done that with the criteria that I mentioned in the query itself. What I am trying to do now is to limit the size of the output in the report. What I want to get in the report is the data for the last 30 days or the last 30 races run at the track, whichever is less. I've got the former limit figured out, but not the latter.

As I mentioned I think Donnie's suggestion gets to what I am trying to do - i just messed up when I tried entering it into the report design.

[MVM]

Dehere,

There is a very simple solution to this if i understand what you are trying to do from your most recent post.

Design your query just as you would as if there were no possibility of getting more than 30 records, but make certain you set the sort order to bring the most recent races to the top.

For example, use your date criteria (ideally the one Mike mentioned above (date()-30), and then make sure that the date sort order is descending. Do the same for the race number field.

After all this is done, right-click in the query design pane and select "properties" from the pop-up. The 3rd item in the Properties dialog is titled "Top values". There is a drop-down list associated with this property, but just ignore it. Use the number 30 (or any other number you want) as the value for this property. This will limit the number of records returned to whatever number you want. Just make certain that the fields and the order you sort them in are correct....the property name "Top values" means exactly that. If you don't have 30 records in the dataset, no problem, as your criteria becomes the limiting factor.

Hope this helps.

[MVM]

BTW....The suggestion I made involves the same property you are manipulating with the drop-down box in the top-center (I didn't read the 1st post close enough).

The power of the "Top-Values" property comes when used in conjunction with selection criteria and sort orders.

Here is a very simple example, this would give me the final times for the most recent 30 6f races at Mnr, provided they occured within the last 30 days. The key is in the ORDER BY clause.

SELECT TOP 30 tblRace.RaceDate, tblRace.RaceNumber, tblRace.FinalTime
FROM (tblRace INNER JOIN tblTrack ON tblRace.TrackID = tblTrack.TrackID) INNER JOIN tblRaceCourse ON (tblTrack.TrackID = tblRaceCourse.TrackID) AND (tblRace.RaceCourseID = tblRaceCourse.RaceCourseID)
WHERE (((tblRace.RaceDate)>Date()-30) AND ((tblTrack.TrackCode)="Mnr") AND ((tblRaceCourse.RaceDistance)=6))
ORDER BY tblRace.RaceDate DESC , tblRace.RaceNumber DESC;

[dehere]

MVM - i was with you until the last part of your second post. First, the problem and then the question.

Problem.

Unless the last part of what you wrote provides a workaround, your solution does not fix the problem I am having. Well, take that back a bit. It does fix the problem if the query I am doing is limited to one track, one distance and one surface; i.e., if i want the profile output for the past 30 races or days for dirt races at 6 furlongs at AQU for example.

The problem arises when I do a broader query where I want the same data (profile data for the last 30 races or days) for all tracks, all surfaces and all distances, but sorted according to (1) track, (2) surface and (3) distance. What happens with that 30 race limitation is that only the profile data for the past 30 races shows up in the query. And, in the profile data that I have in the profiles table doesn't even get past the 6 furlong races on dirt at Aqueduct. So, I never get to see the data for 8.5 furlong races at AQU, or anything from the other tracks that I want the report to produce.

Maybe I can make myself more clear by mentioning the purpose of this report. Basically, one of the nifty things about the new profile "out" file created from HTR is that it provides detailed data for every race that is considered when the summary race profile data is developed in HTR. I use the table created from the HTR export to create a nice history of the past 30 races for the different races on the card on the day I'm handicapping. I have posted an example for 6 furlong dirt races over the last 30 days at AQU (file labeled "Profile Report - Dirt").

Anyway, I'm lazy. So, rather than go through the hassle of running this report for every track and every race type that is carded for a specific day I've developed a query that grabs the different profile reports for each distance and surface carded for every track I'm handicapping and a report that prints out all of that data.

Therein lies the problem - I can't use the "fix" you suggested for the reason mentioned above. And without a limit on the number of races I get upwards of 70 or so races included in the multi-track profile report. I've posted as second file ("Profile All") that shows what happens when I include multiple tracks, surfaces and distances in the same query. In my opinion this is too cumbersom and it fails to track what is most important to me in seeing if there are any biases present, which is the data for just the past 30 races or so.

I hope this more detailed explanation helps.

Now for the Question

Does the last part of what you wrote fix that problem?

[Donnie]

Henry-
Add a new field to your report in the Details section (use the Ab button on the toolbox). Remove the label, leaving an unbound field box. Right click on that new field and choose properties. Go to the all tab and enter the following properties:

Name: GroupCount
Control Source: 1
Running Sum: Over Group
Visible: No

This gives a new hidden field the value of 1, with a running sum going over each Group.

Now right click on the section bar called Detail. Select properties. Go to the Event tab. Clcik in the On Format event. Click the little "..." button that now shows up. Select Code Builder from the list. Copy and paste this line in there:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If GroupCount > 4 then Me.Detail.Visible = False Else Me.Detail.Visible = True
End Sub

Let me know if you still have trouble!

[Donnie]

Ohhhh again that will give you only the top 4. Let's say you want the last 15 running lines...then change it to:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If GroupCount > 15 then Me.Detail.Visible = False Else Me.Detail.Visible = True
End Sub

[Donnie]

BTW--
I have a database somewhere around here that is almost the exact same thing! I guess one of my New Year's resolutions is to get more organized!! FAT CHANCE!!! LOL
I limited my last 6 F D to 15 last races....what I wrote above should work!

You can always call me if you are still getting that error message!

[dehere]

AHAH! Thar she blows! The fish is in the net! The beer is in the fridge!

I think you meant "=1" for the Control Source rather than "1". Other than that change the puppy works like a charm.

Something I just noticed though and makes me wonder if there is some way to modify matters to deal with this minor issue. The average, max and min functions still apply to all of the races in the last 30 days even though only the last 30 show up in the report.

Something else I noticed when setting this thing up. The "tCOURSE" shows up as "Dirt" for TP (and I suspect all Poly tracks). Any thoughts on how we can fix that problem - other than manually changing the data from Dirt to Poly for such tracks?

[Donnie]

Neat thing is Henry, if you set it up as a parameter query you can be prompted for the Track, the Dist and the Surf....so when in Tournament play, you can quickly see EXACTLY what has been winning for this type of race!

[dehere]

Yeah, that is what I had already, but I kinda like this way better. Now I can just print out one report for all of the relevant race types on the card(s) for the day and off I go.

How the hell do you know all this stuff anyway?

[Donnie]

you could take a list of Poly tracks and do an update query......

start a new query.
Click on the word Query on the menu system.
Change to an update query.
bring down the trk field.
bring down the surf field.
Under the criteria for track list the abbreviations of the Poly Tracks (KEE, HOL, WO, TP)
Under the surf field put the designation for dirt on the criteria line.
Put what you want for the designation for poly (probably "A")
When you run the query it will take all "D" designations at those tracks and change them to "A".

Always back up your db before doing an update query.
File>>>>>BackUp Database
Save it somewhere safe!

[Donnie]

Have you already forgotten? That is my fulltime job...I teach about 30 different applications at New Horizons. Obviously Access just happens to be one of my favorites!! Wonder why.....:rolleyes: ??

[Donnie]

I think to get the summary numbers you would have to do the query the way MVM has decsribed, change it to a make-table query, then run your summary functions against that. The new table would hold only the records you would see in the report, hence the summaries would calculate across just those records.

[Donnie]

ohhh...one modification to what I put above...to this line add the bold....

Put what you want for the designation for poly (probably "A") in the Update To row

[dehere]

Thanks for all the help Donnie, Mike, MVM. Things seem to be working for what I need at this point - now on to other housecleaning chores.