Query design

[FJM]

I have a query which when run contains 79 records.
One of the fileds in the query is "Customer account" and this field can contain the following account codes


F003, N034, P064, H064, I004, ZIN01, XD12345. XD12346, YE12345, YE12346, G056, YA12345, YA12346, CO29
In order to make up the 79 records, some of theses codes are repeated.
There are also records where the Customer account field has nothing entered into it.
I want to run a second and third query where some of the records are removed.
With the second query I want to remove I004, any XD accounts, any YE accounts and all records where the account code has not been entered.
With the third query I want the opposite i.e all the remaining records which are not shown in query 2.

I am sure the code for this is simple, but I can't seem to get the combination correct where it gives me exactly what I need
Any help ......?
Thank you

[ranman256]

Q2 , filter out the accts not wanted:
where left([Customer account],2) <> "XD" AND left([Customer account],2) <> "YE" AND ([Customer account]) <> "I004" AND ([Customer account]) IS NULL

Q3, will be an OUTER JOIN, of Q1 & Q2 to find the opposite
start Q3, bring in Q1 & Q2 into the query
join the two on [Customer account]
dbl-click the JOIN LINE,
set to outer join: ALL records in Q1, SOME records in Q2
bring down Q1.* (all fields) into the fields to show
bring down Q2.[Customer account]
in criteria under Q2.[Customer account] set to: IS NULL

this will pull all records in Q1 that are NOT in Q2.

[orange]

FJM,

Please tell us more about your database and what it is used for.
What is/are the table names?
What exactly are you trying to accomplish--in plain English?

Good luck.

[FJM]

Here is the the current group of records which have already been selected for the month of November
I want to run a query where :-
The CUSTACCTNO does not contain any of the following: ZIN01, I004, any ACCTNO which contains XD, any ACCTNO which contains YE, any ACCTNO which contains YA, C029, G056
and it also contains all of the records where the CUSTACCTNO does not have an entry.

NCRNO	DATE	REPORTTYPE	ANALYSISCODE	CUSTACCTNO
21203	01/11/2017	Customer Return	Not Proven
21204	01/11/2017	Customer Return	Not Proven	ZIN01
21205	01/11/2017	Customer Return	Not Proven	B020
21206	01/11/2017	Customer Return	Not Proven	G027
21207	01/11/2017	Customer Complaint	CPE Stores - Stock shown in Incorrect location	I004
21208	01/11/2017	Customer Return	Customer	I004
21209	02/11/2017	Reject	Supp G In Incorrect
21210	02/11/2017	Reject	Supp G In Incorrect
21211	02/11/2017	Customer Return	Not Proven	XD009859
21212	02/11/2017	Customer Return	Not Proven - FOC items issued	ZIN01
21213	02/11/2017	Customer Complaint	Cancelled
21214	03/11/2017	Failed Delivery	Carrier
21215	06/11/2017	Customer Fault	Customer	ZIN01
21216	06/11/2017	CPE Fault	CPE Stores - Label after Despatch	C032
21217	06/11/2017	CPE Fault	CPE Sales - Delivery	I004
21218	07/11/2017	Customer Return	Customer	YE029382
21219	07/11/2017	Customer Complaint	CPE Stores - wrong part picked	G056
21220	07/11/2017	Customer Return	CPE Stores - wrong part picked	T019
21221	08/11/2017	Not Proven	Supplier, wrong paper work
21222	08/11/2017	Customer Complaint	Other	I004
21223	09/11/2017	Customer Complaint	Not Proven	E012
21224	10/11/2017	Reject	Supp G In Incorrect
21225	10/11/2017	Customer Complaint	CPE Stores - Label before Despatch	E012
21226	10/11/2017	Customer Complaint	CPE Sales - Coding	S014
21227	13/11/2017	Customer Return	Customer	I004
21228	13/11/2017	Customer Return	Customer	ZIN01
21229	13/11/2017	Customer Complaint	Not Proven - FOC items issued	T055
21230	14/11/2017	Customer Complaint	Supp G In Incorrect	W014
21231	10/11/2017	Customer Complaint	Supp In Service (failure or safety)
21232	15/11/2017	Reject	Supp G In Pallet problems
21233	15/11/2017	Customer Complaint	Supp In Service (failure or safety)	B020
21234	14/11/2017	Customer Complaint	CPE Stores - incorrect qty picked
21235	15/11/2017	CPE Fault	QA	M004
21236	15/11/2017	CPE Fault	QA	M004
21237	16/11/2017	Customer Return	Customer	j039
21238	16/11/2017	CPE Fault	CPE Sales - Misinterp	A039
21239	17/11/2017	Customer Return	Customer	E026
21240	20/11/2017	Customer Complaint	CPE Stores - Mixed parts on pallets	L037
21241	20/11/2017	Reject	Not Proven
21242	20/11/2017	Reject	Supp G In Incorrect
21243	20/11/2017	CPE Fault	QA	M004
21244	21/11/2017	Customer Return	Customer	ZIN01
21245	21/11/2017	Not Proven	Not Proven	S014
21246	22/11/2017	Customer Complaint	Not Proven	S033
21247	22/11/2017	Customer Complaint	Not Proven	T007
21248	22/11/2017	CPE Fault	CPE Sales - Input	F003
21249	23/11/2017	CPE Fault	CPE Despatch - address	C069
21250	23/11/2017	Customer Return	Not Proven	N034
21251	23/11/2017	Customer Return	Not Proven	P064
21252	23/11/2017	Customer Return	Not Proven
21253	23/11/2017	Customer Return	Supp Ex Stk Faulty (failure or safety)
21254	23/11/2017	Customer Complaint	CPE Sales - Input	H064
21255	24/11/2017	Customer Return	Customer	ZIN01
21256	27/11/2017	Customer Return	Not Proven	XD003927
21257	27/11/2017	Cancelled	Cancelled
21258	27/11/2017	Reject	Supp Ex Stk Faulty (non failure or non safety)
21259	27/11/2017	Customer Return	Not Proven	I004
21260	28/11/2017	Reject	Supp Ex Stk Faulty (non failure or non safety)
21261	28/11/2017	Customer Return	Not Proven	T058
21262	29/11/2017	Customer Return	Not Proven	E011
21263	29/11/2017	Customer Return	Not Proven	P064
21264	29/11/2017	Customer Return	Not Proven	C012
21265	29/11/2017	Customer Return	Customer	ZIN01
21266	29/11/2017	Customer Return	Not Proven	F200
21267	29/11/2017	Customer Return	Not Proven	W011
21268	29/11/2017	Customer Return	Not Proven	ZIN01
21269	29/11/2017	Customer Return	Customer	F003
21270	29/11/2017	Customer Return	Not Proven
21271	29/11/2017	Customer Return	Not Proven
21272	29/11/2017	Customer Return	Not Proven
21273	29/11/2017	Customer Return	Not Proven
21274	29/11/2017	Customer Return	Not Proven	T019
21275	30/11/2017	Reject	Supp Ex Stk Faulty (failure or safety)
21276	30/11/2017	Reject	Supp G In Shortage
21277	30/11/2017	Reject	Supp G In Incorrect
21278	30/11/2017	Reject	Supp Ex Stk Incorrect
21279	30/11/2017	Customer Complaint	Not Proven	E011
21280	30/11/2017	Reject	Supp G In Incorrect
21281	30/11/2017	Customer Complaint	Not Proven	C085

[orange]

Try

SELECT *
FROM Fjm
WHERE Fjm.CUSTACCTNO  Not In ("ZIN01","I004")  AND 
  Fjm.custacctno Not like  "YA*" AND
 Fjm.custacctno Not like  "XD*"  AND
 Fjm.custacctno Not like  "YE*"  AND
 Fjm.custacctno Not like  "C029*" AND
 Fjm.custacctno Not like  "G056*"

[FJM]

This gets rid of all of the aacount numbers I want to eliminate.
I also need to get rid of the entries where there is nothing entered into the account number.
This is the part Iam struggling with.....

[orange]

Try adding this to the end of previous sql

AND Len(Fjm.custacctno & "") > 0

Edit: > 0 since dealing with length.

[ArviLaanemets]

Try adding this to the end of previous sql

AND Len(Fjm.custacctno & "") > ""

("0" > "") = TRUE

?
, like

("999" > "") = TRUE

?

Maybe

... AND Nz(Fjm.custacctno,"") > ""

or

... AND Len(Fjm.custacctno) > 0

[FJM]

I added

OR Len(Fjm.custacctno) is NULL

this seems to have got the result I expect

[Minty]

OR Len(Fjm.custacctno) is NULL

this seems to have got the result I expect

I would warn you that this isn't totally reliable - if someone has deleted an entry and left an empty string "" (Sometimes called a zero length string or ZLS) behind then your Len statement would equate to 0 which is not the same as null.
Hence the reason for using

Len(YourField & "") > 0

to exclude Nulls and ZLS from your answers.