better way to design left join qry

  • Thread starter Thread starter mcnews
  • Start date Start date
M

mcnews

is there a better way to design this query?

SELECT DISTINCT tblSpecimenLog.ASTRO, tblSpecimenLog.PDSpecID,
tblSpecimenLog.BatchID, tblSpecimenLog.PatientID, tblSpecimenLog.DASH
FROM tblSpecimenLog LEFT JOIN qrySpecimenExtracts ON
left(tblSpecimenLog.PDSpecID,10) =
left(qrySpecimenExtracts.pdspecid,10)
ORDER BY tblSpecimenLog.PDSpecID;


it seems to work ok, but design view doesn't like the join being made
on the left() function.

thanks,
mcnews
 
Hi,



You have "encoded" the information. If you would have designed the table so
that there is one and just one information, per field, you would not have
need to use left and your query would be able to use index on its fields.
So, yes, there is a better way, but it requires you re-design your tables to
follow the very first normalization rule: one and just one information per
field. (That does not mean you can "encode" the information when you present
it in a FORM, but in a TABLE, it is generally preferable to follow
normalization).

Hoping it may help,
Vanderghast, Access MVP
 
mcnews said:
is there a better way to design this query?

SELECT DISTINCT tblSpecimenLog.ASTRO, tblSpecimenLog.PDSpecID,
tblSpecimenLog.BatchID, tblSpecimenLog.PatientID, tblSpecimenLog.DASH
FROM tblSpecimenLog LEFT JOIN qrySpecimenExtracts ON
left(tblSpecimenLog.PDSpecID,10) =
left(qrySpecimenExtracts.pdspecid,10)
ORDER BY tblSpecimenLog.PDSpecID;


it seems to work ok, but design view doesn't like the join being made
on the left() function.
Click to expand...
Hi mcnews,

For starters, you could have already included
a field in qrySpecimenExtracts that grabs the
left 10 chars of pdspecid.

And you could just as easily create a
qrySpecimenLog that does the same thing,
then use it in place of tblSpecimenLog
in your query above...so you then join
on these calculated fields.

Then....one has to wonder about storing
important piece of data "within" a field....

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
guess what i really need is to select IDs that first of all do not contain
the letter 'E' then find all IDs where the 10 charatcer part does not match
records that contain the letter 'E'.

only 13-1234-1234 etal would be valid matches in this scenario:

12-1234-1234-A0-E0
12-1234-1234-A1
12-1234-1234-A2
12-1234-1234
13-1234-1234-A0
13-1234-1234-A1
13-1234-1234-A2
13-1234-1234
 
Back
Top