Identify Ties

[Daniel Yantis]

I don't know what else to send to you!
This code:

Produces this result (which is where I am at now):


This is the ties in the entire class, not ties in the top 3 scores...

[NeoPa]

Daniel:
I just DON'T understand!!!

Don't worry. Many experienced designers fall over this concept using aggregated queries (That means those which group records together to find a Sum(), Max(), etc. value). I'm out all today but will look at this when I can.

PS. Using pictures can be very helpful in some circumstances, but there are drawbacks.

1. Using the IMG tag rather than IMGNOTHUMB one makes looking at them quite difficult.
2. A picture doesn't allow easy working with the contents. No copy/pasting of the contents for testing out for instance.

I applaud your ingenuity, but next time it's probably better to do as much in the text of the post as possible.

[NeoPa]

@Daniel
I've sent you a PM discussing how best to continue. It's about communicating and working together rather than technical so not appropriate to be here and public.

However, I will include an update here to indicate where we're at should you choose to continue working sensibly together in order to get to the bottom of this issue.

The data we're working with is below (Amended slightly from your original to illustrate how well the SQL copes with duplicates outside of the top three scores for a class) :

[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
19    AM       20   False    0
20    AM       20   False    0

The SQL here is the same as that in post #2, except that I've added in [Class] to the SELECT statement. From your added comments it seems you will require detail from all the records reflected by the results of this query, so with that in mind the [Class] value will be required :

SELECT   [Class]
       , [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

When we're both happy this works we can move on to extending it to show the detail records, with all their data, associated with these results.

If you find that this works on the data above, but you're still having having problems with your live data (This is probably premature at this stage but as you've already indicated this already) then your question was misrepresented from the start and we need to see where. If we can get to that stage we can look into that issue more deeply - and even that we can resolve. Let's get to that stage before we go there though. It won't help anyone to jump to any conclusions or miss out any logical steps in the process.

Alternatively, should you choose to attach the database instead for my perusal as covered in the PM, I'll do what I can for you.

PS. As for the comments in post #14 to do with aggregate queries. Although it is true technically, I just reread it back to myself and realised it indicates access to the data is entirely impossible, when in reality it is only impossible within the aggregate query itself. It is possible in many cases to use the aggregate query produced to filter the records from the main dataset and thereby produce the information required. Before we can get to that stage though, we need to ensure the aggregate query in question produces results we can rely on.

[Daniel Yantis]

No, I do not understand the text you are writing to me.
Remember, I am a noob and i do not know all the technical terms.

I further do not understand your last part in post 14.
I do not understand aggregates or even what the word means.

However, for the sake of attempting to solve my problem...
I Cut and paste your code above (and after deleting all my fields in the report so the errors of control source not found are resolved) I get the following results:

ACCF 62
ACCF 40
AM   40

It does not show how many ties.
Also, ACCF 40 should not be returned as it will not be in the top 3 (there are several 62s)

This is what my goal output would be:

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

[Daniel Yantis]

As far as sending the file to you, it is in Access 2010 and will not give me the option to save as 2007 or 2003. Apparently my data source has options that are not backward compatible and therefore that option is not available.

[NeoPa]

Daniel:
Remember, I am a noob and I do not know all the technical terms.

Knowing this cannot tell me which terms you are going to struggle with. I can only know from that comment that there may be some terms and some concepts you do not yet understand. Which they are in the multitude covered by the subject I have no way of knowing. This is why I rely on you to communicate with me and let me know when I have said something you cannot follow.

AGGREGATE
Let's start with this then. Aggregating is when you take many and return one. You get this when you use GROUP BY queries. Some of the Aggregating functions are Sum(), Max(), Min(), First(), etc. They take a number of values and return a value reflecting one aspect of those multiple values. When a query is aggregated it is not possible to return a non-aggregated value for any of the fields. Consider the following data :

[B][U]ID   Score[/U][/B]
 1     20
 2     25
 3     20
 4     15

If we were to show [ID] results for each record, that would make sense, but what would the matching [ID] value be for [Score] = 20 when GROUPed BY [Score]? There is no valid value that is not aggregated. If you wanted an aggregated value using Sum(), Max(), Min(), First(), etc. then that would make sense, but a specific value for [ID] would not.

As it happens there is a way around this (to a certain extent). We can use the results from an aggregated query to select the requisite records and then show the detailed results of all those records, but then we are no longer returning aggregated results as such, but detailed results selected with the aid of aggregated results.

[NeoPa]

As you can't get the mountain to Mohammed, I will knock up a very small database here to reflect the issue and attach it to the thread for you to download. I do not include any reports, as testing with reports is not a good way to proceed. All testing, and reporting of results, should be directly from the query itself. Reporting on how the report behaves only serves to muddy the waters and I don't think we need any more of that at this stage.

Please run the query in the attached database and report on the results you get. If you want to, after that's been done, enter all of your data and test with that, but if we're to discuss any results from that I would need the updated database back of course.

After seeing that all your responses so far appear to have been based on results seen in your report I'm hoping that all the problems are related to the report itself, which hasn't been a subject of our discussion. I can't see any reason why your data would behave differently in the query from the test data provided.

NB. Until saved in Design View, the queries in the attached database will reflect the original SQL entered. Access can be relied upon to interpret the SQL perfectly adequately, but not to maintain it in a way that makes working with it practicable - especially when under discussion by two parties - so please maintain the original queries in their current condition until the discussion is over and they need no longer be relied upon.

PS. The database is signed as NeoPa. You can trust NeoPa and its issuing authority (That's me too). Trusting the signature ensures you don't keep getting prompted to accept the code in the project every time you open it (or any other of my dbs). In this case there is actually no code included anyway (Trust MS to require confirmation anyway) so you will lose nothing by disabling the code in the database if you're happier to do that.

[Daniel Yantis]

I actually think this is working as it is.
However, qrySubMedalDups is not very useful in my needs.

I noticed that qryMedalDups is nothing like anything we have discussed thus far. But that is what was needed. It seems complicated, but I do see why the inner join was added. I did not understand previously that the returned scores did not return the entire record details. I see now where my limited understanding was messing me up.

Attached please find the actual data I am using in my test. This is truncated data as FName, LName, Year, Make and Model are not relevant. I also added the query I am using for the winner's list.

[NeoPa]

Daniel:
I actually think this is working as it is.
However, qrySubMedalDups is not very useful in my needs.

Shame that was what your question asked for then I would say. If you want the right answer you need the right question. Seems obvious, but there it is.

I also need (as the request in my last post indicated) a response from you as to whether or not that query answers the question of the thread. IE. Does it produce a list of the duplicates in the medal-winning positions only?

Daniel:
I noticed that qryMedalDups is nothing like anything we have discussed thus far. But that is what was needed.

It is what I have eluded to on a few occasions, but quite sensibly, I was not going there until we'd got past your claim that the query it is based on is not producing the correct results. I still can't proceed to that point due to the absence of a response to the question. It's presence is simply an indication that the end result is reachable if you just respond properly to my posts.

Daniel:
Attached please find the actual data I am using in my test.

I'm really not sure what you expect me to do with it at this point. You haven't stated yet whether or not your data works as expected with [qrySubMedalDups] or not. I know you're not very experienced in Access, but what experience do you need to respond sensibly to questions asked? Maybe I'll just have to look through your database and try to work out what you couldn't manage to tell me.

[NeoPa]

I should just point out that, unless you tell me otherwise, I will assume that your results were exactly the same as mine and there was no evidence whatsoever of any of the queries suggested returning invalid results.

Presumably then, all this was about testing the results in the report instead of testing those of the query itself and there hasn't been a real problem with the suggestion right from the start.

[Daniel Yantis]

I think I did answer your question when I said this seems to work...
However it was qryMedalDups that was working for me.

I made it clear in my post #19 what the results were to the other query. (since it was the same query)

[Daniel Yantis]

Thank you NeoPa, This works!

SELECT tD.*
FROM Data AS tD INNER JOIN (SELECT   [Class]
               , [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
       )  AS sQ ON (tD.Class=sQ.Class) AND (tD.Score=sQ.Score);

[NeoPa]

There is no facility for explaining the reasoning behind resetting a Best Answer, so I'll have to post in here as a clarification is only fair.

The SQL you posted (from the test database I supplied in post #22) certainly does work, and produces the results you want. What it doesn't do though, is match the question of the thread. It was provided as a bonus to provide the extra functionality requested after the question was already answered (even though you were unable to realise this for reasons I can't go into publicly). The only answer to the original question is the one in post #2, which has been there largely unrecognised from almost the beginning.