Sort Changes Query Record Count???

[alr0]

Hi All,

Using Access 03 & Win XP Pro:

When I run the following query it creates 769 records.

SELECT Claimant.*
FROM ClaimantNew INNER JOIN Claimant ON ClaimantNew.Barcode = Claimant.Barcode
WHERE ((Claimant.Flag)="x");

Then when I sort, ascending by barcode the record count goes to 754. Remove filter/sort brings the record count back up to 769. You can back and forth many times. I know that there are some dupes, that is what I am looking for but I can't explain this.

I have moved lots of data in my life and never run into this before. I created and empty mdb and imported the tables again and did a compact/repair before starting. The same thing happens.

I will figure some way to get what I need by doing make table queries and double checking results but I am very curious what circumstances could make this happen. Anyone run into this?

Thanks,

alr


_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[lynchg]

Your SQL is a little odd in that you are selecting and filtering from Claimant but driving the joins from ClaimantNew, try:

FROM Claimant INNER JOIN ClaimantNew ON Claimant.Barcode = ClaimantNew.Barcode

Maybe it won't make any difference, but Access is goofy sometimes.

 

[alr0]

Hi lynchg,

Thanks for taking a look at this. I should explain that this was a query and I pasted the SQL that Access creates, this was not written in native SQL.

I tried the change you proposed and got the same result. (different quantity of records when sorted and return to original quantity after "Remove Filter/Sort")

I have solved the original problem by using make table queries and carefully checking each step. However I am very curious how this could happen. While a filter could change the number of records returned, a sort should never change the quantitiy. Right???

There is now a further wrinkle. Instead of claimant.*, I put all of the fields in the grid and sorted by barcode. The same number of records are returned but now when sorted in the result I get 750 records. A different quantity than in either case before. How odd is that?

I would like to understand what the sort feature could possibly be doing to cause the number of returned records to change.

Anyone know what is going on here?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.