Fewer query results after alphabetical sort?

[Lost500]

Hi all,

I am runnig a query (including a few tables) and it runs fine except that initially there are 500+ records in the result until I highlight a column and sort alphabetically and suddenly there are only 475 records... i don't know what is happening and it scares me. this is going on in the same data set view of the query. the only thing i can think of is that it is picking up on duplicate results after the sort, but that seems unlikely. any help would be great!

 

[Remou]

Please post some SQL.

http://lessthandot.com

 

[Lost500]

ok here it is, there is alot but really its just pulling fields from a couple tables using the criteria of a specific unit name and that a couple of check boxes are false, this is in access and i view the results in a dataset and then i just highlight a column and click sort and the record count at the bottom goes down to 475 from 500+... i'm not using DISTINCT or any other unique statements, the only function really is the NZ i use to pull a expiration date so i really am lost here. thanks for the quick response.

SELECT dbo_tblUnitInfo.UnitName, dbo_tblLeaseInfo.LeaseNumber, dbo_tblLeaseInfo.Lessor, dbo_tblLeaseInfo.Lessee, dbo_tblLeaseInfo.Volume, dbo_tblLeaseInfo.Page, dbo_tblLeaseInfo.LeaseDate, dbo_tblLeaseInfo.ExtensionOptionsMonths, dbo_tblLeaseInfo.ExtensioinExercised, Nz([ExtensionExpirationDate],[ExpirationDate]) AS ExprDate, dbo_tblLeaseInfo.PoolingTerms, dbo_tblLeaseInfo.PughClause, dbo_tblLeaseInfo.PughClauseDescription, dbo_tblLeaseInfo.VerticalPughNotes, dbo_tblLeaseInfo.AntiDilutionProv, dbo_tblLeaseInfo.AntiDilutionProvNotes, dbo_tblLeaseInfo.Comments, dbo_tblTractLeaseAcres.ConfirmedNetAcres, dbo_tblTractLeaseAcres.TractInterest, dbo_tblLeaseInfo.Expired, dbo_tblLeaseInfo.LifeEstate, dbo_tblLeaseInfo.ProtectionLease, dbo_tblTractLeaseAcres.TractPughedOut, dbo_tblTractInfo.UnitTractNumber, dbo_tblTractInfo.TractName
FROM dbo_tblLeaseUnitized RIGHT JOIN ((dbo_tblLeaseInfo INNER JOIN dbo_tblTractLeaseAcres ON dbo_tblLeaseInfo.LeaseNumber = dbo_tblTractLeaseAcres.LeaseNumber) INNER JOIN (dbo_tblTractInfo INNER JOIN dbo_tblUnitInfo ON dbo_tblTractInfo.UnitName = dbo_tblUnitInfo.UnitName) ON dbo_tblTractLeaseAcres.TractName = dbo_tblTractInfo.TractName) ON dbo_tblLeaseUnitized.LeaseNumber = dbo_tblLeaseInfo.LeaseNumber
WHERE (((dbo_tblUnitInfo.UnitName)="Underwood") AND ((dbo_tblLeaseInfo.Expired)=0) AND ((dbo_tblLeaseInfo.LifeEstate)=0) AND ((dbo_tblLeaseInfo.ProtectionLease)=0) AND ((dbo_tblTractLeaseAcres.TractPughedOut)=0));

 

[Remou]

As it stands, I have no idea.

I notice that you have a misspelling, and wonder if this is a cut-and-paste?

ExtensioinExercised

I wonder how many records you get if you sort in the SQL (ORDER BY), rather than click and sort?

http://lessthandot.com

 

[Lost500]

so i looked into the misspelling, and i just can't spell but, i misspell consistanly so that is the actual field name...

i can tell now after reviewing the results that the 475 number is the correct record count im going for and the additional records are duplicates steming from dbo_tblLeaseUnitized.

However even if the query results resulted in duplicate values, why is it getting rid of those when i sort in the datasheet view? Something i checked is that when i do sort it is not inserting an ORDER BY statement and rerunning the query so it should just be sorting the data that is already there.. please help!