Select query - fewer records when results are sorted??

[caringsharingbristolbilly]

Hi all.

I'm running a very simple SELECT query off some Oracle tables in Access 2007. When I first run it, I get 8,237 records appear. The last one is a duplicate which, as far as I can tell, shouldn't be in the results. When I sort the results by the ID field, I only get 7,853 records, and the previously duplicated entry only appears once. I guess the operative phrase is 'what the hell...?'

If I add the 'ascending' flag to the ID field in the query builder, I still get 8,237 records, but only 7,853 if I re-sort.

Looks like a bug to me. Anyone come across this before? Any tips?

Thanks,
CSBBB.

 

[Joe4]

I don't think I have ever heard of such a thing. Are there are blank or null values in the field you are sorting by?

Can you post the SQL code of each of your queries that is returning different results, so we can see if anything stands out?

You might want to do a comparison of each list to see which records are getting dropped. That may provide a clue as to what is going on.

 

[caringsharingbristolbilly]

Hi.

Thanks for your reply.

The SQL code is as follows. The field CAP_UCI2 is the ID field I'm searching by. It's not very easy to follow in SQL view!

SELECT BRS_SRS_CAP.CAP_UCI2, BRS_SRS_CAP.CAP_APFS, BRS_SRS_CAP.CAP_AYRC, BRS_SRS_CAP.CAP_IDRC, BRS_SRS_APF.APF_RECD AS [APP DATE], BRS_SRS_CAP.CAP_DECD AS [OFFER DATE], BRS_SRS_CAP.CAP_RSPD AS [REPLY DATE], BRS_SRS_CAP.CAP_UCRS, BRS_SRS_CAP.CAP_FACC, Left([CAP_IDRC],1) AS offerIDRC, BRS_SRS_CAP.CAP_STAC, BRS_SRS_CAP.CAP_DEC1, BRS_SRS_CAP.CAP_RSP1, BRS_SRS_CAP.CAP_DPTC
FROM (BRS_SRS_APF RIGHT JOIN BRS_SRS_CAP ON (BRS_SRS_APF.APF_STUC = BRS_SRS_CAP.CAP_STUC) AND (BRS_SRS_APF.APF_SEQN = BRS_SRS_CAP.CAP_APFS)) LEFT JOIN BRS_BRS_APPLICATIONS_RECEIVED ON (BRS_SRS_APF.APF_STUC = BRS_BRS_APPLICATIONS_RECEIVED.APF_STUC) AND (BRS_SRS_APF.APF_SEQN = BRS_BRS_APPLICATIONS_RECEIVED.APF_SEQN)
WHERE (((BRS_SRS_CAP.CAP_AYRC)="11/12") AND ((BRS_SRS_CAP.CAP_DEC1) In ("C","U")) AND ((BRS_SRS_CAP.CAP_CYCL)="2011") AND ((BRS_SRS_CAP.CAP_AESC)="U") AND ((BRS_SRS_CAP.CAP_APCC)="H"))
ORDER BY BRS_SRS_CAP.CAP_UCRS;

There aren't any blank or null fields. I'll investigate, see if any of the duplicate fields have anything in common. The problem is that it's intermittent. However, the very intermittence (is that a word?) of it leads me to believe it's a bug or a corruption somewhere...

Thanks again for your help,
CSBBB.

 

[Joe4]

What do you mean by?

The field CAP_UCI2 is the ID field I'm searching by

I do not see that field anywhere in your criteria.

One thing that I find helpful when experiencing problems of this nature is to strip you query down to the bar bones (one include the key fields), and build it piece by piece to make sure it is doing the right thing at each step along the way (i.e. you have three table at work here - start with two, then after you make sure things appear to be working correctly, add your third).

 

[caringsharingbristolbilly]

I do not see that field anywhere in your criteria.

Sorry, buddy. Should've given the full name. That's the BRS_SRS_CAP.CAP_UCI2, the first named one after 'SELECT'.

Thanks for the tips, that looks like a good place to start, so I'll do that and see what happens.

Thanks again,
CSBBB.