question

Ibehere569 avatar image
Ibehere569 asked

SQL records issue

Hello, Not sure what's up with this but the following SQL returns about 800K records... when I add a lookup table to it I get another 200K records. the returned dataset should be the same record count. SELECT qryLocations.Location, [qryAnalytical-NO-Orphans].samplecode AS SACODE, [qryAnalytical-NO-Orphans].LabSampleID AS LABSAMPID, [qryAnalytical-NO-Orphans].CasNo FROM qryLocations LEFT JOIN [qryAnalytical-NO-Orphans] ON qryLocations.Location = [qryAnalytical-NO-Orphans].Location WHERE ((([qryAnalytical-NO-Orphans].samplecode)="N" Or ([qryAnalytical-NO-Orphans].samplecode)="FD")); but this returns 100K records: SELECT qryLocations.Location, [qryAnalytical-NO-Orphans].samplecode AS SACODE, [qryAnalytical-NO-Orphans].LabSampleID AS LABSAMPID, ltblCASPar.ParLabel FROM (qryLocations LEFT JOIN [qryAnalytical-NO-Orphans] ON qryLocations.Location = [qryAnalytical-NO-Orphans].Location) LEFT JOIN ltblCASPar ON [qryAnalytical-NO-Orphans].CasNo = ltblCASPar.CAS WHERE ((([qryAnalytical-NO-Orphans].samplecode)="N" Or ([qryAnalytical-NO-Orphans].samplecode)="FD")); I can confirm that there are no duplicate values in the ltblCASPar table. What am I missing here? Thanks, Ernst.
sqlsyntax
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Does that first query return items where the `[qryAnalytical-NO-Orphans].CasNo` field is `NULL`?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.