Pull a list from a table to use as exclusion criteria in a query

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

Guest

I want to make a table of names to pull into my query and exclude when the
query runs. How do I pull the list into the sql statement?

WHERE ((([Report-Gender Name Problems].salutation)<>"James"));

I want James to become a variable that is populated from a table.
 
Dear Egg:

Probably the most efficient way would be to create a LEFT JOIN to the
table and then filter where the column in this table of names IS NULL.

SELECT T1.*
FROM YourTable T1
LEFT JOIN TableOfNames T2
WHERE T2.ColumnName IS NULL

Fix this up for your actual table and column names.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
That is a great idea and works up to the point in which you want to edit the
data. If I'm correct in my thinking you can't edit the contents of the join
query when it comes up. What would be the simplest way around that. I
solved this by creating a parse field and populating it with the join
information and then query the nulls edit them and delete the parse. I
didn't know if there was a more direct way to acomplish this.

Tom Ellison said:
Dear Egg:

Probably the most efficient way would be to create a LEFT JOIN to the
table and then filter where the column in this table of names IS NULL.

SELECT T1.*
FROM YourTable T1
LEFT JOIN TableOfNames T2
WHERE T2.ColumnName IS NULL

Fix this up for your actual table and column names.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I want to make a table of names to pull into my query and exclude when the
query runs. How do I pull the list into the sql statement?

WHERE ((([Report-Gender Name Problems].salutation)<>"James"));

I want James to become a variable that is populated from a table.
Click to expand...
Click to expand...
 
Dear Egg:

Are you saying you want to edit the contents of the TableOfNames to be
excluded? Make a subform of this on your form and have at it.

I would tend to use a combo box for this single column table. I would
include all the possible names, but exclude those already entered in
the table. There's no need to entery the same name twice.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


That is a great idea and works up to the point in which you want to edit the
data. If I'm correct in my thinking you can't edit the contents of the join
query when it comes up. What would be the simplest way around that. I
solved this by creating a parse field and populating it with the join
information and then query the nulls edit them and delete the parse. I
didn't know if there was a more direct way to acomplish this.

Tom Ellison said:
Dear Egg:

Probably the most efficient way would be to create a LEFT JOIN to the
table and then filter where the column in this table of names IS NULL.

SELECT T1.*
FROM YourTable T1
LEFT JOIN TableOfNames T2
WHERE T2.ColumnName IS NULL

Fix this up for your actual table and column names.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I want to make a table of names to pull into my query and exclude when the
query runs. How do I pull the list into the sql statement?

WHERE ((([Report-Gender Name Problems].salutation)<>"James"));

I want James to become a variable that is populated from a table.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top