omit from select query

[Trevor2007]

I have this select query :

SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo;

I would like to do two thing but not sure if they are possable or how to do them
1) using this query I would like to omitt the value "N/A" (w/o quotes)

2 ) omitt "N/A"(w/o quotes) and omite a persons name in a value in column [Referred to] where colume[status] does not equal "lead" (w/o quotes)

The reason, I have 3 tables with basicly the same info in them (all three are lookup tables)
thanks for help.

[n8kindt]

my suggestion would be to use the "totals" feature in your query. it's the button that sort of looks like an uppercase "E". perhaps u might even want to use a crosstab query. your microsoft help files should help you with the rest.

[Trevor2007]

my suggestion would be to use the "totals" feature in your query. it's the button that sort of looks like an uppercase "E". perhaps u might even want to use a crosstab query. your microsoft help files should help you with the rest.

sorry that doesn't make much since to me, I don't have tabed pages, and as for the "E" button I have never seen it. The query is placeed in rowsoucre property of a combobox

[Stewart Ross]

Hi Trevor2007. The easiest way to do what you ask is to use the graphical query builder from the Access Queries tab and add criteria to the query fields. This is very quick and simple. You don't say what the type of [Referred To] is, but as it is storing a name it is certainly a string value, in which case you will need the quotes in the query criteria (all string literal values are listed in quotes).

You could place the following SQL in the SQL view of your query and then see what it looks like in the graphical design view:

SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.Status <> "Lead"

You do not say which field holds the value "N/A" you wish to exclude. I have assumed it is the ReferredTo field, the only one returned in your sample SQL. Similarly, your request to omit a person's name when Status="Lead" is not meaningful when this is the only field returned by the query. For that reason the SQL I provided simply excludes matching rows altogether.

-Stewart

[Trevor2007]

Hi Trevor2007. The easiest way to do what you ask is to use the graphical query builder from the Access Queries tab and add criteria to the query fields. This is very quick and simple. You don't say what the type of [Referred To] is, but as it is storing a name it is certainly a string value, in which case you will need the quotes in the query criteria (all string literal values are listed in quotes).

You could place the following SQL in the SQL view of your query and then see what it looks like in the graphical design view:

SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.Status <> "Lead"

You do not say which field holds the value "N/A" you wish to exclude. I have assumed it is the ReferredTo field, the only one returned in your sample SQL. Similarly, your request to omit a person's name when Status="Lead" is not meaningful when this is the only field returned by the query. For that reason the SQL I provided simply excludes matching rows altogether.

-Stewart

yes, you assumed right , [referredTo] is a string value, it is the name of a person or team group.
I haven't tested it yet but am testing after I leave you this post.

[Trevor2007]

yes, you assumed right , [referredTo] is a string value, it is the name of a person or team group.
I haven't tested it yet but am testing after I leave you this post.

SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
  WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.STATUS <> "Lead";

I am getting an error when i try to save the query in either the query graphical representation or if i try to use the query in a row sourc, the error is :
syntax error (missing operator ) in query expression 'referredtolu.referredto where referredtolu.referredto <> "N/A" AND referredtolu.status <> "Lead"
I have tried this this way and with add a semicolin to the end of "lead";
and still the same error
I have also tried tripple quoting my sting values ie '" [value]"'
and that didn't help

[Stewart Ross]

[code]
SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.STATUS <> "Lead";
[code]

I am getting an error when i try to save the query in either the query graphical representation or if i try to use the query in a row sourc, the error is :
syntax error (missing operator ) in query expression 'referredtolu.referredto where referredtolu.referredto <> "N/A" AND referredtolu.status <> "Lead"
I have tried this this way and with add a semicolin to the end of "lead";
and still the same error
I have also tried tripple quoting my sting values ie '" [value]"'
and that didn't help

My apologies, I added the where clause and overlooked the ORDER BY clause which has to come last. The revised SQL should be

SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.STATUS <> "Lead"
ORDER BY ReferredToLU.ReferredTo;

-Stewart

[Trevor2007]

My apologies, I added the where clause and overlooked the ORDER BY clause which has to come last. The revised SQL should be

SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.STATUS <> "Lead"
ORDER BY ReferredToLU.ReferredTo;

-Stewart

I played with the query builder in graphical Display and came up with :, that seem to work
only to discomver I didn't need it, becacuse I needed the table just for one form :

SELECT ReferredToLU.ReferredTo
FROM ReferredToLU
WHERE (((ReferredToLU.ReferredTo)<>"N/A") AND ((ReferredToLU.Status)<>"Lead"))
ORDER BY ReferredToLU.ReferredTo;

Thanks for your help

[NeoPa]

Trevor, the easiest way to add [ CODE ] tags to your post is to select the code you want included, then click on the button which looks like a # in the post entry page.

Your code is nicely formatted with the different clauses on separate lines. This is good and makes it more readable. Another technique to remember when working with SQL is that the table name is only required when more than one table (data source) is used in the FROM clause of your SQL.

SELECT ReferredTo
FROM ReferredToLU
WHERE ((ReferredTo<>'N/A')
  AND ([Status])<>'Lead'))
ORDER BY ReferredTo;

Notice how much easier this is to read and digest.

Access often puts some quite naff SQL in its queries, so don't look to what it creates as a good format of SQL necessarily. It's very handy to use it to create the SQL for you though. Access also (quite incorrectly) uses double-quotes (") instead of single-quotes (') to denote string data. It will interpret single-quotes correctly though.

Hope all this helps.