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.
8 2243
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.
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
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
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.
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.r eferredto where referredtolu.re ferredto <> "N/A" AND referredtolu.st atus <> "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
[code]
SELECT DISTINCT ReferredToLU.Re ferredTo FROM ReferredToLU ORDER BY ReferredToLU.Re ferredTo
WHERE ReferredToLU.Re ferredTo <> "N/A" AND ReferredToLU.ST ATUS <> "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.r eferredto where referredtolu.re ferredto <> "N/A" AND referredtolu.st atus <> "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
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 32,572
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Miroslav I. |
last post by:
------=_NextPart_000_0009_01C35A6F.9908B420
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Hallo
suppose you have a table with great number of columns (20 or 30),
and you would like to specify every one but two or tree columns in a =
SELECT statement.
|
by: sinister |
last post by:
After doing a websearch, it appears that it's OK to omit the "http:" to form
a relative URL. Are there any pitfalls to this?
For example, if there is a page
http://www.domain1.com/page1.html
with a link to
http://www.domain2.com/page2.html
you can abbreviate the second link as
//www.domain2.com/page2.html
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: Rinee |
last post by:
Hi,
I have a database of a bunch of contractors who have signed a safety
agreement with us, their contact info, their insurance info, etc. Each
was assigned an ID number as the main key. Each contractor agreement
has an expiry date. I have a report that lists the contractors and a
couple other relevant fields for reference for our company employees.
But I don't want a contractor to appear on this report (which looks
like a list of...
| |
by: athos |
last post by:
Hi guys, please help.
What I did is :
Step1. create a Database named , it's default owner is
Step2. create a User in Database named , which maps
to the Login on this Database Server, it's a windows
domain user.
Step3. create a Schema in Database named , whose Schema
Owner is User
Step4. created two tables: . and .
|
by: swsdam |
last post by:
Thank you for even reading this...
I have an xml doc that feeds into html. I am being passed the xml via asp.net. I have used xsl to create an html. Now I'm being asked to let the user omit which elements get copied to new xml (via another xsl?). I would like to present the xml elements to my user and let them select which elements to copy onto another xml doc by clicking a checkbox. Is this possible and how?
So far I am thiking maybe I...
|
by: emandel |
last post by:
I have a query which is giving me and
This is a Union Query and therefor some records are repeated. In addition some households have differant members wusing the same e-mail. How do I make a second query to show me all of the data from the first query but to OMIT the second entry when an e-mail is repeated?
Thak you!
|
by: ZAVILA |
last post by:
This is the SQL code for starters.
SELECT DISTINCTROW .CYCLE_DATE, .SERIES_NBR, .SERVICER_NBR, .SERVICER_NAME, .SERVICER_LOAN_NBR, .LOAN_NBR, .INTERNAL_LOAN_ID, .ARCC_LOAN_NBR, .LIEN_STATUS_CODE, .MaxOfACTL_END_PRIN_BAL, .REPORTED_ACTION_CODE, .MONTHS_DELQ, IIf(="CX",,"") AS NEW_COMMENT_DATE_TIME, (IIf(="CX",,"")) AS NEW_STAFF_NAME, (IIf(="CX",,"")) AS NEW_COMMENT_TEXT, (IIf(="CX","CX","")) AS NEW_COMMENT_CODE
FROM ( LEFT JOIN...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |