By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

omit from select query

P: 68
I have this select query :
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 13 '08 #1
Share this Question
Share on Google+
8 Replies


n8kindt
100+
P: 221
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.
Mar 13 '08 #2

P: 68
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
Mar 13 '08 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
  2. 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
Mar 13 '08 #4

P: 68
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
  2. 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.
Mar 14 '08 #5

P: 68
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU ORDER BY ReferredToLU.ReferredTo
  2.   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
Mar 14 '08 #6

Expert Mod 2.5K+
P: 2,545
[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
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.STATUS <> "Lead"
  2. ORDER BY ReferredToLU.ReferredTo;
-Stewart
Mar 14 '08 #7

P: 68
My apologies, I added the where clause and overlooked the ORDER BY clause which has to come last. The revised SQL should be
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ReferredToLU.ReferredTo FROM ReferredToLU WHERE ReferredToLU.ReferredTo <> "N/A" AND ReferredToLU.STATUS <> "Lead"
  2. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT ReferredToLU.ReferredTo
  2. FROM ReferredToLU
  3. WHERE (((ReferredToLU.ReferredTo)<>"N/A") AND ((ReferredToLU.Status)<>"Lead"))
  4. ORDER BY ReferredToLU.ReferredTo;
Thanks for your help
Mar 14 '08 #8

NeoPa
Expert Mod 15k+
P: 31,271
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT ReferredTo
  2. FROM ReferredToLU
  3. WHERE ((ReferredTo<>'N/A')
  4.   AND ([Status])<>'Lead'))
  5. 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.
Mar 14 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.