getting GROUP top vavues in report 1

[tigi1]

Hi Access experts
I am a new user to Access.
I have a table of monthly production running into years of data
I want to make a report that shows only the top 3 values for every month - how can I get it ?
I tried topvalue(top 3) but that gives only top 3 values for the whole table, not every month.
Kindly help. I have been pulling out my hair over this.

Thanks in advance
Tigi1

 

[rafe]

Tigi,

Please notice that Gord & I have marked this thread as an interesting question. Actually, the post in the other thread that you noticed was a direct result if me trying to find a solution to this question.

I've gotten creamed (& recently) in these boards for posting incomplete/poorly tested answers, hence the delay & my reluctance now & you don't want this either. But... Here's where I'm at with your problem:

1) The SubQuery method you saw in the other thread is slooooow for your situation. It requires nesting Subquires and then using aggregate functions. So it's actually slow to the fifth or sixth power. For Neto it's only slow squared.

2) I was working on a string build up of Union queries but I haven't worked all the code out yet. This will be slow too but not nearly as slow as #1.

3) A programmer solution is to query with the proper sort order and then programmatically extract the needed records. May be the fastest, if done correctly, but I haven't gotten it to feed into a report just yet.

Neiter #2 or #3 are hard per se it's just a matter of me budgeting time to work this out so that it's bullet-proof to you.

Sorry, & your post was not ignored. Just an interesting (read new to me) problem.

rafe

 

[ghubbell]

Tigi,
And ditto on Rafe's last line. We'll solve this for you! Gord
ghubbell@total.net

 

[tigi1]

rafe/ghubbell
Thanks very much for putting your efforts on my problem.
As suggested by rafe, I have tried out a programming approach for reports. The solution i have struck on is very simple and as follows.
sorting and grouping is set so that grouping is formed on month followed by production(descending)

in the detail section I put an unbound lable named [sl](it is the serial no. to be displayed)
in the 'on format' event of detailed section, the following is entered

me![sl]=me![sl]+1 (this increments sl by 1)

if(me![sl] >3) then
reports[report_name].section(0).visible =false
else
reports[report_name].section(0).visible=true
endif

this switches off the detailed section after the top 3 records.

in the 'on format' event of the 'month' group header, I initialise the variable
me![sl]=0

This seems to be a very simple and elegant solution (and seems to be working fine on my reports).
Thanks to rafe for pointing the way.

However I still have not fully got to a solution for the full problem, as I would like to extract the same records via query so that I can export it.
So How can we do it in query ?

Thanks
Tigi

 

[ghubbell]

Tigi,
I can't speak for Rafe however I've been bashing my head on the desk about this one! In VB code I have a 10 second solution. Thru queries......I'm still looking. I'm terribly stubborn so I will find a way! Gord
ghubbell@total.net

 

[rafe]

Tigi,

Well you beat me to it!

I’ve done a slight variant that I’ll post for you so you can see the differences & similarities. Whole module for the report: Sorting & Grouping on Me.YearMonth with a Group header (or footer) to get blank spaces between months & Sorting on Me.Amount in descending order. Instead of a hidden control I use a global in the module & I use the Cancel flag of the Detail_Format. Me.YearMonth is Set to Format([MyRealDate],”yyyy-mm”) etc.…. but all-in-all pretty much the same.

Option Compare Database
Option Explicit

Const LineMax As Integer = 3
Dim LineCnt As Integer
Dim YMprev As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If YMprev <> CStr(Me.YearMonth) Then
        YMprev = CStr(Me.YearMonth)
        LineCnt = 0
    End If
    If LineCnt >= LineMax Then
        Cancel = True
    End If
    LineCnt = LineCnt + 1
End Sub

Private Sub Report_Open(Cancel As Integer)
    YMprev = &quot;YYYY-MM&quot; ‘Weird value so that Nulls are handles too.
End Sub

Truly, the nested sub-query is method (in #1) too slow. I did 2 record version & it took several minutes several minutes. To get output for 3 records will take, I’m guessing, about ten times that. & if you ever go to 4 or more records… a life time. I agree with Gord about the query being a real head-banger.. he's also got more experience.

But as the saying goes: “If you ain’t cheating you ain’t trying.” Translation: “Why not look for a work-around.”

If all you want to do is extract the data for export, why don’t we work on a form that builds a query string where clause. Using the same logic as for the report. Would that be a do-able, if not satisfying & elegant?

 

[ghubbell]

Please expedite some Asprin or Tylenol to.......! Gord
ghubbell@total.net

 

[rafe]

Seeing as you two are handy with VBA I'll share my latest angle. Not completed yet but you got to the report solution first so...

I'm looking into using a global module function to flag the records based upon the same criteria we used to filter the report records.

 

[ghubbell]

First let me say "Merci Beaucoup" to Total Net, for deciding to pull in and change the oil on their server at 9:00 PM EST. I really wanted to haunt you two with this prospect before bed time:
What if: you have a "tie" in any of your "top 3"? i.e. your top 3 should fairly display 4 or 5 or well, an infinite amount. I'm almost done so you two have to pick up the pace! Remember, I'm working for a solution that's really "code-less"...
To keep us in sync, I'm using a copy of Northwinds, and using the "Orders" and "Order Details" tables. I find a towel wrapped around my head has helped to stop from chipping the desk. Gord
ghubbell@total.net

 

[tigi1]

rafe/ghubbell

Thanks to rafe for the alternate route. Your suggestion of flagging the same records looks very interesting, but i persume, a bit of heavy coding will be required. Your suggestion of using forms is also interesting.
What crosses my mind right now is to use a form to do the flagging,counting upto 3 as in my report solution- instead of fiddling visible property, set the flag in a field in the D/B. Have a button on the form to run a query query to get the flagged records out and then have a macro to export it to exel.
This will minimize code requirement.
As to why I wanted the top records in group.
Since I mantain the database, these are required by certain departments for their analysis.Mostly printing out a report will do. But a few stubborn ones insist on getting the same thing in 'Exel'. thats why I wanted the same records extracted out.
For gubbell's query on tied values, frankly my approach dosnt consider it. It just counts 3 from top and stops there.
If you could evolve a query based solution, I think that will be most elegant.
Thanks
Tigi

 

[ghubbell]

Tigi/Rafe: Here it is…my headache is gone!
A short time ago I had to tally specific items from specific orders, counting and grouping and summing along the way. I built it in VB and it worked very well. Always trying to find a better way, I created a stack of queries to do the same thing. Results were identical but the run time using queries was only 10% of the VB’s. I began to wonder…
I marked this thread because I was given a similar challenge a few weeks earlier, and due to time constraints, was “beaten” by a VB solution.
I am very fortunate to have a father who worked with computers in the days when they occupied entire buildings. He is retired now, finds windows and most of these programs “fluff” as he can program in machine language if he wants, run programs on a 286 that will fly past anything we are doing here. He gave me one key tip to this solution: “no matter what, you have to cycle through the records by the number of months or, if there is less items than numbers of months, cycle the items instead.” It’s the cycle that’s the key. Thanks Dad!

Well Tigi, I don’t know how many items you have so I’ll cycle the months.
I’ve made a table that we’ll use to store the data only while we’re viewing the report. In the example, 3 fields: Month, ProductID, and Quantity.

What could be easier than making a query for each month? Cycle one at a time? Are we so stubborn trying sub queries, vb code that we loose sight of a simple query? After purchasing a helmet and having my desktop replaced, I did exactly that. 12 append queries. Each one checks a month, and returns the top 3 values. After the first query was built, it was just a matter of copy/paste and change the “Month” value. Time spent: maybe 10 minutes. It was here that I noticed the possibility that you could have a “tie” in any position and for any number of items. Tigi, you said this wouldn’t be an issue I assume based on your data. In any event, this setup will tell you the truth no matter what.

Add to this a Delete query (to clean out the table): 2 minutes to build.

Using a report wizard, build a report based on the table: 3 minutes and that includes the next step:

Add this code to the On Open event of the report:

DoCmd.SetWarnings False
DoCmd.OpenQuery "tigidelete"
DoCmd.OpenQuery "tigijan"
DoCmd.OpenQuery "tigifeb"
DoCmd.OpenQuery "tigimar"
DoCmd.OpenQuery "tigiapr"
DoCmd.OpenQuery "tigimay"
DoCmd.OpenQuery "tigijun"
DoCmd.OpenQuery "tigijul"
DoCmd.OpenQuery "tigiaug"
DoCmd.OpenQuery "tigisep"
DoCmd.OpenQuery "tigioct"
DoCmd.OpenQuery "tiginov"
DoCmd.OpenQuery "tigidec"
DoCmd.SetWarnings True

And this to the On Close:

DoCmd.SetWarnings False
DoCmd.OpenQuery "tigidelete"
DoCmd.SetWarnings True

Run time on my little 133 at home searching 400 records: about 6 seconds to preview. Here at work on “Cartman” an AMD950: you can’t even count.

I based everything off of the Northwinds sample Db, clipping the orders table to 400 records for size, and I test the year 1997. Just because we see more queries in the Db window doesn’t mean we’ve done a bad thing, especially if we get good results! Anyone who wants a copy, send me your email and tell me 97 or 2000. It has been a pleasure!
Gord
ghubbell@total.net

 

[tigi1]

Gord
Eureka! Thats a nice fast solution, though a plate-full of queries.
How true, often we fail to see whats below our nose.
I'll try and look into possibility of generating and deleting the queries dynamically so that the query window will not look overcrowed.
Of course before running 'tigidelete' i will export the data to 'Exel' which was one of the primary objectives.

I may comeback to you while doing the dynamic queries. I had done it for an earlier exercise when I did face some problems. Though I worked around those, it may not have been the most efficient solutions.

Thanks again Gord and i'm putting my email below so you can mail me the whole thing.
I dont live in your side of the world, else i would have happily send you a new helmet and some aspirins !!

happy querying
Tigi
tigilal@hotmail.com

 

[ghubbell]

Thank you Tigi. Don't worry about overcrowding: Did you ever delete a query? They're so tiny they're gone in a blink. The db in front of me at this moment is holding well over 800! Gord
ghubbell@total.net

 

[ghubbell]

Hi Tigi,
Here's a clip of a sub that I use to get the description for a given part number. In reality it is much longer as I have to work in four languages...in any case there should be enough for you here to get an idea of how to "grab" information from one place and apply it into your form. I am also doing a little speed test: this idea versus a similar append query, and I will do the test on 50K + records and 6 fields to make it fair. I'll post the results for all to see.

Private Sub PartNumber_AfterUpdate()
On Error GoTo ErrPN
Dim RS As Recordset, SQL1 As String, Db as Database
If IsNull(Me.PartNumber) Then Exit Sub
Set Db = CurrentDb()
SQL1 = "SELECT tblmaster.* FROM tblmaster WHERE (((tblmaster.[PartNumber])='" & Me.PartNumber & "'))"
Set RS = db.OpenRecordset(SQL1, dbOpenSnapshot)
If RS.RecordCount = 0 Then
RS.Close
Exit Sub
End If
Me.Description = RS![Description]'<-in goes the description!
RS.Close

ExitPN:
Exit Sub

ErrPN:
MsgBox Err.Number & &quot; &quot; & Err.Description, vbInformation, &quot;Part Number ''after-update'' error.&quot;
Resume ExitPN
End Sub Gord
ghubbell@total.net

 

[tigi1]

Hi Gord
Thanks a lot for the 'record grabber' code. I think it will make my d/b faster as i can avoid running through many many 'dlookup' statements.

I did download and run your 'tigireport'. It is a fast solution and will serve my purpose as a stopgap arrangement. On giving some thought to the problem, I realised that when data accumulates over 12+ months or if I bring in the data for earlier years, there will be a major roadblock.
I think we should look at a nested query solution also for a generalised approach to TOP VALUES in GROUPED DATA.
This is important to me because I will have to deal with this in various datasets.
Thanks for the immense help you have extended.
Since I am new to this discussion group, I was just wondering if this thread has become too long with so many queries and answers. I think you will be in a position to decide if this is to continue or a new thread is to be started.
Thanks once again and expecting to hear about the further tests u plan to do.
Tigi

 

[rafe]

yea it's a monster thread... Gord's been trying to fix a monster problem & has been real busy. so i decided to get back to the original method #2... way back at the top

i created a query &quot;Query2&quot; that sorts on &quot;yyyy-mm&quot; ascending, field called &quot;ym&quot; and amounts descending field called &quot;SumOfAmount&quot;

i'm using my own data here. hence the funky names. then upon a button click we generate a new query in vba.

Option Compare Database
Option Explicit

'you need to change this to get your year month
'i'm using my "GetDocTotals" query but...
Const ymQry As String = "SELECT Format([Stamp],'yyyy-mm') AS ym " _
    & "FROM GetDocTotals GROUP BY Format([Stamp],'yyyy-mm') " _
    & "ORDER BY Format([Stamp],'yyyy-mm');"

Dim ymRS As ADODB.RecordSet
Dim curDB As Database
Dim qryObj As QueryDef

Private Sub Command0_Click()
    Dim qryStr As String
    Dim i As Integer
    
    Set curDB = CurrentDb
    curDB.QueryDefs.Refresh
    Set ymRS = New ADODB.RecordSet
    ymRS.Open ymQry, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    qryStr = ""
    
    'Get rid of old version of "TopThree" query
    For Each qryObj In curDB.QueryDefs
        If qryObj.Name = "TopThree" Then curDB.QueryDefs.Delete qryObj.Name
    Next
    
    'Build up NEW "TopThree" Union Query
    ymRS.MoveFirst
    For i = 1 To ymRS.RecordCount
        If qryStr <> &quot;&quot; Then qryStr = qryStr & &quot;UNION &quot;
        qryStr = qryStr & &quot;SELECT TOP 3 Query2.ym, &quot; _
            & &quot;Query2.SumOfAmount FROM Query2 WHERE (((Query2.ym)='&quot; _
            & ymRS![ym] & &quot;'))&quot;
        ymRS.MoveNext
    Next
    ymRS.Close
    qryStr = qryStr & &quot; ORDER BY ym, SumOfAmount DESC;&quot;
    
    Set qryObj = curDB.CreateQueryDef(&quot;TopThree&quot;, qryStr)
End Sub

yes? yes?

 

[ghubbell]

You two....I nap for a moment and look what I find! (nice code Rafe!) Alright Tigi, you've raised the bar here so I'll head back to this +12 month issue as soon as I can, but it will be done with Queries! Thread length? Thank you Ms/Mr inventor of scrolling mouse: we've just begun! Gord
ghubbell@total.net

 

[tigi1]

Hi rafe/Gord
Quite a bit of heavy coding rafe-I'd need the aspirins now!
But I did pull out a few more of my hair and finally got to a very compact code solution.
Drawing upon the various ideas put up here, I found the flagging proposal very neat and have used it here.
This is how it's done.
I modify my original table (dalloc_all) to add a yes/no field called 'selected'.
I make a form with a button named 'exp2exl'
on click of the button add this code
-------------
Sub exp2exl_Click ()

Dim mydb As Database, mytable As Recordset
Dim cmonth, pmonth As Variant
Dim i,tc As Integer

Set mydb = DBengine.Workspaces(0).databases(0)
Set mytable = mydb.OpenRecordset(&quot;select * from dalloc_all where [mwrot]>0 order by [month],[mwrot]desc&quot
'[mwrot] is the field contaning the productions

tc = 0 'set top3 counter to 0
pmonth = &quot;&quot; 'set variable previous month
mytable.MoveFirst

Do Until mytable.EOF
cmonth = mytable!month 'get value of current month

If (cmonth = pmonth) Then 'see if current and previous
'month are same
If tc < 3 Then 'increment top3 counter if <3
tc = tc + 1
mytable.Edit
mytable!selected = -1 'set selected field to yes
mytable.Update
End If
ElseIf (cmonth <> pmonth) Then 'if current and previous
' month <>
tc = 1 'a new month group
'so set top 3 counter to 1
mytable.Edit
mytable!selected = -1 'update selected to yes.
mytable.Update
End If
pmonth = mytable!month 'get current month into
'previous month variable
mytable.MoveNext
Loop
MsgBox &quot;done&quot; ' DONE DONE !!!!

mytable.Close

End Sub
-------------------
This works fine and fantastically fast.
From here it's simple, put in a docmd to get out the records set to 'yes'

Now I want your comments on this, Im not very sure about all the implications of the code and may have missed something.
On my records it's working fine and should serve as a generalised solution to finding the
TOP values in GROUPED data.

waiting for your expert comments
regards
Tigi

 

[rafe]

code looks fine to me (Gord?)... we're on the same wavelenght. is being on my wavelenth good? is another question ;-)

But I did want to warn you that using a flag in the table to get records for reporting is a single thread solution. i've done it i know. that is: what happens if you use the flag for another report? what happens if two users go for the same report?

i'm NOT saying ditch it! definitely not! if these aren't a problem NOW then DO NOT WORRY ABOUT THEM NOW. i just want you to think about those issues if they are a problem NOW. i used the table yes/no flag method for several months & then had to make changes to accomodate things as my needs changed; but it may have just as easily turned out that I never would heve needed to change things.

My philosopy is &quot;Solve the problem at hand & worry about the future problems in the future.&quot; why? because you/me/anybody has no idea what your db is going to need in the future. so don't waste time fixing things that aren't a problem yet & may never be a problem. i'll bet that they're piling work on you as you're reading this.

BTW: you should take a minute to notice how far you've come since March 5th. Keep it up!

 

[ghubbell]

I love this thread. I think we should try and set a Tek-Tips record for the greatest thread length.

Tigi, that's some mighty fine code and should work pretty well.

Ditto Rafé <-(check that out!)

roblems may arise if you're in a fast moving network situation and someone else can hit your flag field by/for something else. You can solve this with arrays but that's getting too far from the query idea for me. When you close your report have it run an update QUERY that wipes any yes to no immediately, reduces the chance of wacky data best as you can.

Oh ya, Ditto Rafé again on that last two parts. So true....So true...

and...Way to go Tigi!!
Now back to my queries.....where was I....
Gord
ghubbell@total.net