Using Top predicate in complex pivot query (1 Viewer)

Hi all,


With help from Thedbguy, I have created quite a complex query feeding into a report. I now need to change the query to include a subset of the records for sorting and grouping. It looks to me like I need the Top predicate (asc) but is this so and where should it go within the query? The query looks like this:-


Dim frm As Form
Dim qdf As DAO.QueryDef, strwall As String
------------------------------------------------------------------------------------------
'11/23/2019 - modified by thedbguy@gmail.com
strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
strwall = strwall & " SELECT Count([pos])>=" & Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 & " as sort1, IIf([sort1],[points],0) as sort2, "
strwall = strwall & " [membername] & Space(30) & '.' & [seriesname]& Space(30)& '..' & [tblseries].[rtc] AS HELM, count (tblResults.pos) as Raced, sum (tblResults.pos) as Points "
strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
strwall = strwall & " WHERE (((tblRace.seriesID)= " & Me!Combo3 & "))"
strwall = strwall & " GROUP BY [tblresults].[membername],[tblseries].[seriesname],[tblRace].[seriesID], [tblSeries].[seriesname],[tblSeries].[rtc] "
strwall = strwall & " ORDER BY format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh:mm') "
strwall = strwall & " PIVOT format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh:mm');"

Set qdf = db.CreateQueryDef("qryWallQuery", strwall)
qdf.Close

--------------------------------------------------------------------------------

This piece determines the first sort ...


SELECT Count([pos])>=" & Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 & " as sort1,


and this piece the second ...


IIf([sort1],[points],0) as sort2, " ...


What I need for Sort2 is to sort on just the Top values (asc) rather than all the values that meet the Sort1 criteria. The value of Top is in this expression in the query:-

Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2

I've tried all sorts of 'Top' positioning but not found the answer yet. I'd be happy to have both All and the Top (asc) values for use in the subsequent report but would settle for just the Top values as Sort2.

I can upload a zipped copy of the current test db if that would help

Thanks for all and any views

Brian

The TOP predicate comes after SELECT, so:

SELECT TOP 10 Count(...

where 10 would be the number of records to return. I think it can also be a percent. It would be based on the sorting specified in the ORDER BY clause.

The TOP N is used immediately in a SQL statement after SELECT.
However if you try that in a crosstab query it fails possibly with a very misleading error message about delete queries

So I think you need to run the TOP N on the original SELECT query, save it and then run a new crosstab

Thanks, Paul, Colin,


I currently use all the information returned by the query within the report. If I SELECT the Top N, doesn't this mean that the query will only return the Top N records?


What I've been trying to do is get an additional sort (sort3 of if necessary replacement sort2) which if Top worked as I'd hoped would look something like this like this ...

IIf([sort1], Top ( Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 [points],0)) as sort3


It doesn't work of course and perhaps Top isn't what I need but hope it shows what I've been trying to achieve ...


Thanks all, Brian

Yes that's exactly what TOP N does.

Sorry, no it doesn't help. Perhaps you could upload something to explain what you mean

Sorry not to be clearer, Isladogs. I've attached a test db. The form 'wallchart' includes the query as the data source for the report 'seriesresults'.

In Sorting and Grouping the report, sort1 is fine. If sort1 is true, sort2 sums all the 'points' to determine the next sort order. My problem is that sort2 should sum only a subset of the total records where the number of records in the subset is derived from this expression in the query:-

Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2

So no matter how many records are returned, sort2 should be based upon only the lowest set of records. If 'Snowflake' is selected from the form, the value of the expression is 9 so sort2 should be based only on the sum of the lowest 9 'points'. That sounds like the 'Top asc' predicate to me but that's clearly not right way to do this.

I hope this explanation is better than my last attempt. Any further thoughts on how to go about this?

Thanks and regards, Brian

A couple of general observations:
1. You omitted the startup form frm_Switchboard which is causing some errors.
2. It would have been better to just upload the relevant parts of your database as there's a lot there that isn't needed for this issue

Its not easy to get my head around what you have provided but I'd say a major issue is that tblResults isn't normalised.
Its like a spreadsheet with MANY columns. That's not how databases should be designed
This structure is inevitably going to cause you problems when trying to analyse data

I would suggest dealing with that issue before trying to do anything else
I would also review your object names as having queries named e.g. tblRaceQuery is confusing

I've had a quick look at qryWallQuery
The sort2 expression is IIf([sort1],[points],0).
Its not immediately clear what that means ...
The expression should be IIf(sort1=some criteria, true part, false part)

But in fact sort2 is based on 2 other derived expressions sort1 & points.
You should use the original fields instead in sort2
Sort2: IIf(Count([pos])>=9,Sum([pos]),0)

That doesn't help with the rest of your question but it may help you determine how to proceed to the next stage
I'm unclear what the expected results are meant to be so can't devote any more time to looking at this. Perhaps someone else will step in to assist

To help others, I suggest you provide a stripped down database containing only what is needed to manage this issue
Provide sample data & also separately provide expected results!

However, as I've already said, I think you need to go back & restructure how your results data is stored.

Good luck

Thanks for the feedback folks. Thought I'd wait and see if there were any further pointers.

I included only the relevant tables, form and report (+ a few queries) into the test db; not enough to link all the forms such as frm_switchboard hence the error. The db did start out as a normalized though it's certainly true that tblresults has quite a few unused fields, a legacy from many attempts to improve data reporting over the years. A redesign has been suggested by some forum members - not sure I would know where to start on that and the impact on the many forms, reports and queries seems very daunting.

As Isladogs says, trying to use the Top predicate in the way that I have tried to use it does result in a less than helpful error message. As far as re sorting this particular report is concerned, I do seem to have come to a dead end. Mind you, I have thought this with other issues until the more knowledgeable have shown me a way forward ... so there's always hope!

Thanks again

Brian

Hi Brian
As previously mentioned, it would be easier for someone to help you if we knew what the expected results should be for a couple of examples from your data

briantjohnson said:

A redesign has been suggested by some forum members - not sure I would know where to start on that

Click to expand...

This might help:- https://www.niftyaccess.com/excel-in-access/

Mind you, it won't be much help for the forms...

The tool is available for free, just let me know and I will tell you how to get hold of it....

Thanks Isladogs, I hope the attached file is the sort of thing that would be helpful. This is the output of the report as it stands. The value of 'points' in column 3 is the sum of all the values off the right starting with 06/10 and ending with 08/12. It's sorted low to high.

However, what's wanted is to sort by the sum of only the lowest values where the number of lowest values to sum is identified by the statement 'Latest races to qualify' in this case 8 so the sort should be the sum of the lowest 8 'points' sorted ascending. The order under the label 'Qualified' should show the following:-

1st line Nick Blore 8 (from total of 11)
2nd line Brian Johnson 15 (from total of 36)
3rd line Miles Thomas 17 (from total of 28)
4th line Mike Harris 31 (from total of 36)

Each time the report is run, there will be more data to select and the 'Latest races to qualify' may change. I do seem to have all the data to do this but not the ability to sort it 'properly'.

Best regards, Brian

Thanks Uncle Gizmo, that's my Christmas reading sorted! I don't know much about Excel using it just for simple one-sheet things ... but as the db is sometimes used in places that don't have internet access, I coded in from the start (2005) the export of key data to Excel so that if the db becomes corrupted, it can be recreated.

Brian
I spent almost an hour on this, with more than half of this time spent disabling the startup form and autoexec so I could use it without error.
I then added Option Explicit to each mode module and fixed several compile errors due to undefined variables.
After all that I looked at the report only to find almost all controls were unbound.
I can't justify spending several hours fixing your database which I think it will need.

At the very least you need to write a select query that filters the data in the way you require. Then base your crosstab query on that.

However I seriously recommend you go back to the drawing board and start again with a fundamental redesign of your tables. You've done very well to get it to work as well as it does with this structure. However every added step will be a massive battle as this current task already is.

Perhaps you should consider paying a professional developer to work on this.
I should make it clear I'm not touting for work but someone else may be willing to take it on.

Good luck

Thanks Isladogs for your support and pragmatic recommendations. The Dcounts and unbound controls were a way to get me close to what I wanted and perhaps avoid the need to start again. I will look at separate select and crosstab queries ... though I think I'll need to accept that I can't have everything I want on one report. The db has done what's needful over the years and hasn't 'needed' much in the way of maintenance ... though this recent interchange has exposed design weaknesses.

Thanks also to thedbguy who showed me the flexibility of access sql queries and to vba_php who, at the outset pointed out the flaws in my design. UncleGizmo has suggested some resources and that's where I'll look for design inspiration.