Search results for query: *

Re I have been using this type of crosstab logic for decades Yes, I was Googling around last night looking for reasons why my crosstab was erroring out and found something similar with your name on it from 2007! [medal]

Duane, that's a masterclass in how to approach a complex problem and goes to show what you can do in SQL rather than heaps of VBA! Conscious that this must have taken you a great deal of time and I'm very grateful. I'm now running through the pieces to learn how I can apply this logic elsewhere...

Hi Duane or indeed anyone who would like to take an interest. Trying to take Duane's SQl above (which finds the correct value TOP using a sub query) to the next step which is to get this working as a crosstab and then add the more complex elements on a step by step basis. Running this crosstab...

Duane, Sumofpos is now correct!! And yes, you are right, the number of races would probably be different for each member in each series. I guess another subquery would be something like AND WHERE ... between the AND ((tblSeries.seriesname) = "Autumn Series")) and the GROUP By statement...

Hi Duane, The SQL seems to give the same results as the querydef within the form that drives the report. As suggested, I've stripped the .mdb to just the tables, queries, form and report concerned and also stripped most of the other data columns in the tblresults to make it as clear as...

Hi Duane, Huge apologies, I hadn't received your response in my Inbox ... just looked at the forum and there is was! membername = Nick Blore was just me trying to find an answer and clearly not the thing to do. The points I'm seeking are for just one named series each time the report is run...

Hi Duane, Following your initial response, I'd really like to get the value of 'Pos' from the report record source rather then from the report event code. I took your 'Northwind' SQL, added some tables and relationships and hoped to see the TOP N values returned. That's not working as yet, I've...

Hi Duane, I see what you mean - I hadn't thought to use Northwinds! I used that example in a query adding my other table relationships with some test values. It does give the Sumofpos and Counntofraces but oddly not for the member(employeeID) selected ... the values returned seems to be the...

Thanks Duane, That sounds ideal! I think my problem is that the underlying querydef is already complex (at least for me). 'Points' is calculated within the a form querydef and is 'correct' unless the total number of races counted is >= sort1 at which point the value of 'points' should be...

Hi folks, Following last week's SQL solution (thanks combo and andy), I've come across another snag. A have a report that uses a cross tab query as it's record source. I use a particular field (sort1) in the report header to support the the report sort order and that works fine. However, I now...

My thanks to Andy too. For combo and Andy, here's the final outcome which works just fine. Still not sure that using that DSUM but hey, it works well enough! And until I found it on this forum, I never knew about debug.print and that's proved really useful. Dim strname As String strname =...

Great Post, understood. In the query, I used test data for TOP (=6) and [seriesID] (=45). I have dynamic values for TOP and [seriesID).Should I use this CHR(34) approach to replace the test values with these values which may change each time the report is run? ie. for [seriesID] I have a...

Spot on! Thank you combo. I'll pin that one on the wall. Chr(34) is double quote ... somehow I was expecting a single quote somewhere. Never been able to find a really simple description of what to use where [mad] My use of DSum to get the total value of 'pos' works too though I still think...

Hi folks, Could someone point out what I'm doing wrong with this query? The basics ran OK in the query grid and it compiles but I get the error when I try to run it within VBA ... Dim qdf2 As DAO.QueryDef, strq As String strq = strq & " SELECT TOP 6 tblResults.pos, tblResults.membername "...