Recent content by briantjohnson

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...

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...

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...

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...

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...

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)...

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...

Hi again thedbguy, I hope it is OK to add a piece to this ... The solution works perfectly. The query and the report grouping give the counting and summing I asked for. The first column shows the number of races undertaken and the second the number of points as shown in the attached report...

Great. Think I just need to change my DCounts to give Int values as testing for other series comes up with fractional values ...

... it all seems to be changes to the select statement ... :confused: I cant see any other changes ...

That's it! :):):). I will work on the report to get rid of the duplicate 'points' and 'raced' columns but can you say how you cracked it? Regards, Brian

Hi Guy, Not right as yet. The first sort should be to determine who has reached the target (ar-tr/2) ... that's where the value of 'raced' is 10 or above for the Snowflake Series. For those that reach that target, it's he who has the lowest number of points that should be on the top row...

Adam and Guy So grateful that you are both taking an interest and as an aside, I'm learning lots about Access that I just never knew. Whilst I'd really like to be able to solve the report sorting with the structure I have and with your help I do seem to be very much closer but ... it just may...

Sorry, exceeded file upload limit, here again is the report output ...

Adding that strwall line ... I didn't know you could do that ;) ! Result is that it adds the that ar-tr value to each record (that's the crosstab I suppose). I then made the Order By criteria like this iif([raced] >= (ar-tr/2), points, (raced desc, points asc) It didn't work (see the...