Identify Ties

[Daniel Yantis]

You guys have been great! Thank you for all your help. I am learning as I go...

So now that I have a working SQL statement for a winner's list, I need to have a new report that shows ONLY TIE score in the top 3 from the winner's list so that I can quickly see tie problems before giving trophies out.

Here is the sample data:

[B][U]ID    Class  Score   DSQ   BestOf[/U][/B]
11    ACCF     81   False    0
 2    ACCF     62    True    0
 6    ACCF     62   False    0
12    ACCF     62   False    0
16    ACCF     62   False    0
 3    ACCF     40    True    0
13    ACCF     40   False    0
14    ACCF     24   False    0
 4    ACCF     21   False    0
 1    ACCF      0   False    0
10    AM      101   False    1
 9    AM      100   False    2
 5    AM       81    True    0
15    AM       80   False    0
 7    AM       40   False    0
17    AM       40   False    0
 8    AM       23   False    0
18    AM       20   False    0

Here is my working winner's list SQL statement

SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
FROM Data AS Dupe
WHERE (((Dupe.DSQ)=False)
  AND ((Dupe.BestOf)=0)
  AND ((Dupe.Score)>0
  And (Dupe.Score) In (SELECT TOP 3 Data.Score
                       FROM Data
                       WHERE ((Data.Class=Dupe.Class
                         AND Data.DSQ=False
                         AND Data.BestOf=0
                         AND Data.Score>0))
                       ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC;

And here is the current results:

[B][U]ID    Class  Score   DSQ   BestOf[/U][/B]
11    ACCF     81   False    0
 6    ACCF     62   False    0
12    ACCF     62   False    0
16    ACCF     62   False    0
15    AM       80   False    0
 7    AM       40   False    0
17    AM       40   False    0

What I need is just a list of ties within the above results to that I can identify problems quickly before trophies are handed out:

[B][U]ID    Class  Score   DSQ   BestOf[/U][/B]
 6    ACCF     62   False    0
12    ACCF     62   False    0
16    ACCF     62   False    0
 7    AM       40   False    0
17    AM       40   False    0

I understand I need to use a HAVING sql statement. I just don't understand enough to figure out where that goes.

Be patient! I'm a noob and a bit stupid.

[NeoPa]

Your query qould be largely similar to what you have. You would need a HAVING clause. I think the following should work. Try it out :

SELECT   [Score]
FROM     [Data] AS [Dupe]
WHERE    (NOT [DSQ])
  AND    ([BestOf]=0)
  AND    ([Score] In (SELECT   TOP 3 [Score]
                      FROM     [Data]
                      WHERE    ([Class]=Dupe.Class)
                        AND    ([Score]>0)
                        AND    (NOT [DSQ])
                        AND    ([BestOf]=0)
                      ORDER BY [Score] DESC))
GROUP BY [Class]
       , [Score]
HAVING   (Count(*) > 1)
ORDER BY [Class]
       , [Score] DESC

[Daniel Yantis]

If I try to save the following SQL it saves ok.
Then run it and I get "cannot group on fields selected with '*'(Dupe)"
Then when I go back into query editor (not SQL view) it is all messed up...

SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
FROM Data AS Dupe
WHERE (((Dupe.Class)<>"**** - U n k n o w n - NEEDS CLASSIFICATION")
  AND ((Dupe.BestOf)=0)
  AND ((Dupe.DSQ)=False)
  AND ((Dupe.Score)>0
  And (Dupe.Score) In (SELECT TOP 3 Data.Score
                       FROM Data
                       WHERE ((Data.Class=Dupe.Class
                         AND Data.DSQ=False
                         AND Data.BestOf=0
                         AND Data.Score>0))
                       ORDER BY Data.Score DESC)))
GROUP BY Dupe.Class, Dupe.Score
HAVING (Count(*)>1)
ORDER BY Dupe.Class, Dupe.Score DESC;

This is the EXACT CODE from my working winner's list with GROUP and HAVING added in. What am I doing wrong? It must be punctual.

[NeoPa]

You have made various changes to this from what I suggested. I'm happy to work with code I've suggested, but when you make a bunch of changes - some of which are stylistic while others are more fundamental - then, if I'm to answer your questions, I'm at a big disadvantage. I have to start again from scratch. That can get tiresome as I'm sure you can appreciate.

If I'm to help you go forward then you need to work with my suggestions. Test them out and report on what you get. That way we both know where we are. If you test them then go ahead with your own ideas without reporting back, I get left behind and it all gets much more complicated than it needs to be.

[Daniel Yantis]

The code you provided will not run in my version of Access (2010).

Access changes punctuation when you save. No amount of editing will force it to keep some punctuation changes. Access also changes (NOT(Data.DSQ) into (Data.DSQ=False ) and will not allow me to keep the NOT statement.

If I cut and paste your code above EXACTLY AS YOU HAVE IT I get a syntax error and can't continue to save it.

Maybe you could use the code I provided in my original question?

[Daniel Yantis]

This works:

SELECT Dupe.Class, Dupe.DSQ, Dupe.Score, Dupe.*
FROM Data AS Dupe
WHERE (((Dupe.Class)<>"**** - U n k n o w n - NEEDS CLASSIFICATION")
  AND ((Dupe.DSQ)=False)
  AND ((Dupe.BestOf)=0)
  AND ((Dupe.Score)>0
  And (Dupe.Score) In (SELECT TOP 3 Data.Score
                       FROM Data
                       WHERE ((Data.Class=Dupe.Class
                         AND Data.DSQ=False
                         AND Data.BestOf=0
                         AND Data.Score>0))
                       ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC;

This Addition Does Not Work:

GROUP BY Dupe.Class, Dupe.Score
HAVING (Count(*)>1)

The only change was a filter for "Unknown" that I had been removing because it was not relevant to the issue at hand...

[NeoPa]

Daniel:
If I cut and paste your code above EXACTLY AS YOU HAVE IT I get a syntax error and can't continue to save it.

Maybe you could use the code I provided in my original question?

That wouldn't be too clever a way to approach the problem Daniel (My trying to continue with code that I have no confidence in). Remember you're asking for help - not the other way around.

What would make sense is for you to report properly on the errors you get reported and we progress from there.

[Daniel Yantis]

I cut and Pasted you code from #4 above and I get this error on saving:

** Edit **
[imgnothumb]http://bytes.com/attachments/attachment/5336d1313723660/error.jpg[/imgnothumb]

[NeoPa]

Daniel:
I cut and Pasted your code from #4 above and I get this error on saving:

So much easier to work in co-operation Daniel.

In light of your response I was able to check over the SQL posted and noticed a missing closing parenthesis. I've now added that to the original post and tested it. It works fine.

Now you know what's required, you can reformat it into whatever style suits you in your own database.

[Daniel Yantis]

I pasted your code from #2 above (after your edit) and Access reformat's it to this:

SELECT Dupe.[Score]
FROM Data AS Dupe
WHERE (((Dupe.[DSQ])=False)
   AND ((Dupe.[BestOf])=0)
   AND ((Dupe.[Score]) In (SELECT   TOP 3 [Score]
                           FROM     [Data]                       
                           WHERE    ([Class]=Dupe.Class)
                             AND    ([Score]>0)
                             AND    ([DSQ])=False)
                             AND    ([BestOf]=0)
                           ORDER BY [Score] DESC)))
                           GROUP BY Dupe.[Score],
                                    Dupe.[Class]
                           HAVING   (((Count(*))>1))
ORDER BY Dupe.[Class], Dupe.[Score] DESC;

Then in the report design view I get this error for all my Text Boxes:
Invalid Control Property: Control Source "No Such Field in the Field List"

I notice that there is no fields to choose from in the control source pull down list.

If I try to run the report I get dialog boxes asking for those fields.

[NeoPa]

Daniel:
I pasted your code from #2 above (after your edit) and Access reformat's it to this:

I know. Access is pretty basic like that. SQL produced by Access is definitely not the highest level to aim for. Nevertheless, it shouldn't cause you any problems, as only in very rare cases will it take workable SQL and screw it up so badly it won't still run when re-entered (See Access QueryDefs Mis-save Subquery SQL if you're interested).

Daniel:
What I need is just a list of ties within the above results to that I can identify problems quickly before trophies are handed out:

As this was the original request, it's what I suggested a solution for. This may seem a very fussy approach to you. It probably is, but I can do what I do mainly because I have such a fussy attitude. Programming in general, and database work in particular, is a very fussy/precise environment. Many problems are caused by lack of attention to detail and fussy b45t*rds like me get fewer problems than most. The good news is that it's very little extra work to get it to work as you need it.

If you need SQL to match your illustration instead of the posted request then simply change the SELECT clause (currently found on line #1) to include the extra fields you need. If you have any trouble with this then let me know and I'll look into it more deeply for you. It should be straightforward though. I only say that as you have already said all this is quite new to you and sometimes what appears simple to the experienced eye is less so without the benefit of that experience.

[Daniel Yantis]

wow that was cool....
It found the TOP 3 duplicates under the entire CLASS.
(My actual data file has about 1200 records)
I just need the duplicates in the TOP 3 SCORES for each CLASS.

I wish I knew how to use SQL better, but it just confuses me.

Here is some code that finds TOP 3 TIES in an old database I had.
(useless now because I changed so much other stuff and also lost the original data structure).

SELECT ByClass.Score, ByClass.Class, ByClass.Judge, ByClass.ID, ByClass.FName, ByClass.LName, ByClass.Year, ByClass.Make, ByClass.Model
FROM ByClass
WHERE (((ByClass.Class) In (SELECT [Class]
                            FROM [Data] As Tmp
                            GROUP BY [Class],[Score]
                            HAVING Count(*)>1
                               And [Score] = [Data].[Score]))
                               AND ((ByClass.Score)<>0
                               And (ByClass.Score) In (SELECT TOP 3 Data.Score
                                                       FROM Data
                                                       WHERE (((Data.Class)=ByClass.[Class]))
                                                       ORDER BY Data.Score DESC)))
ORDER BY ByClass.Class, ByClass.Score DESC;

I guess you realize i don't know how this works... else I would have edited it to my needs.

[NeoPa]

I'm confused Daniel. Your explanation of the results you got doesn't fit with my understanding of the SQL.

Certainly with the data set you provided I got the results I expected and that matched your request. I also looked at the SQL itself and considered what ought to happen with other duplicates in the lower scores, and line #5 ensures what you describe should not happen.

Just to be sure I added two extra records with values as below :

[B][U]ID    Class  Score   DSQ   BestOf[/U][/B]
19    AM       20   False    0
20    AM       20   False    0

This left three records of [Class] = 'AM', [DSQ] = False and [BestOf] = 0, but which didn't end up in the medal positions. The results, when I ran the SQL, were that these values were not represented at all. Only [Score]s of 62 and 40 showed up in the results.

I have no idea what is going on in your database, but the SQL suggested is designed to find duplicates only within the top 3 [Score]s (and according to my tests does exactly that).

[NeoPa]

However, just as I was feeling that the questions were all answered, I remembered you'd asked for extra fields from the matching records to populate your query. It's not really relevant to the original question, but my response was not accurate or reliable.

One extra field can be added to the SELECT clasue, because only one field is included in the GROUP BY clause, [Class]. No other fields can be returned, for the simple reason that there are no other fields within the group that are defined. Only [Class] and [Score] have values in that context. All the other fields from the data source (the [Data] table) are represented by any number of values and as such cannot logically be represented. Exceptions to this (if we play around with the GROUP BY clause or even simply include literals to represent them) are [DSQ] - which we know to be False for all the records we cover - and [BestOf] - which again, we know to be 0.

Returning a non-aggregated value for [ID] though, for instance, would be impossible as it makes no sense in that scenario. Does that all make sense to you?

[Daniel Yantis]

I just DON'T understand!!!
I am ready to quit but I have to get this project done.

Taking your code from post #2 above, I have to add to line 1 (Select) so that the fields in my report will have control sources...
But that still does not work!

This is what error comes up:

Here is the code for my winner's report:

And that code shows this report:

Here is the date type: