how can I use a table as a list of records not to includ in a qry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to get a table of records that have certain keywords and don't have
other keywords. The records are pulled from a mainframe database, and in the
conversion they are split into a separate record for each line of the
original listing. Querying with Not just leaves out the lines that would tell
us the part # is unusable. I can run a query/make a table that lists all the
part #s that should be excluded from the query that has the wanted keywords,
how do I use that exclude information to narrow down the wanted results?
 
Tell us specifically what your criteria is. This should be done by building
a query to include and exclude the criteria desired. A query can take many
criteria into account and can use ANDs and ORs to narrow down further.
Other special statements can be used to include a list of valid (or invalid)
values for a field.

Tell us specifically what you want and perhaps we can help you build the
query.
 
For example a part no 336017 has 15 lines of description on the mainframe. In
the database it has all 15 lines listed according to the part no, as separate
records. I want to find all the part nos that have the words braze and plate
in the description, but don't also have watertested in the description. I
tried querying - Like"*braze*" And Like"*plate*" And Not Like"*watertested*",
and that only leaves out the lines of description that would tell me the part
doesn't match what I'm looking for. I can query the DB and come up with a
list of part nos to exclude, but I don't know how to use that list to filter
the query of parts with the required keywords.
 
Sounds as if you might want to use an exists clause or perhaps some subqueries.
GENERIC QUERIES follow.

SELECT PartNo
FROM TheTable
WHERE PartNo in
(SELECT T.PartNo
FROM TheTable as T
WHERE T.Description Like "*Braze*")
AND PartNo In
(SELECT T.PartNo
FROM TheTable as T
WHERE T.Description Like "*Plate*")
AND PartNo NOT in
(SELECT T.PartNo
FROM TheTable as T
WHERE T.Description Like "*WaterTested*")

Another method would be to use your query that identifies those parts to exclude

SELECT PartNo
FROM TheTable
LEFT JOIN YourQuery
ON TheTable.PartNo = TheQuery.PartNo
WHERE TheQuery.PartNo is Null
AND PartNo in
(SELECT T.PartNo
FROM TheTable as T
WHERE T.Description Like "*Braze*")
AND PartNo in
(SELECT T.PartNo
FROM TheTable as T
WHERE T.Description Like "*Plate*")


That excludes the records you want to exclude, but you still have to test for
records that do match.
 
Back
Top