ARGH! Please provide help!

  • Thread starter Thread starter DevilDog1978
  • Start date Start date
D

DevilDog1978

I am new to Access and I am trying to develop a rather large database for my
company. I have successfully fumbled through a good portion of the database
and now I am stuck. I am trying to get this query (make table query) to work:
Like "*67*" Or Like "*Q" Or Like "*X" Or Like "7D*" Or Like "AK*" Or Like
"MA*" Or Like "NC*" Or Like "*B" Or Like "*C" Or Like "*L" Or Like "*P" Or
"9LN" Or "9WI" Or "BAM" Or "DIA" Or "ICE" Or "SBA" Or "XTF" Or "ZXQ"

Where the field name is sub_cust and the table is pcinventry
Using the above criteria, I was able to significantly reduce the records
that appeared; however, I am still getting data I do not want in my table.
Some of the unwanted sub_custs showing up in my new table are 93567, HMLA*,
and Electric.
Can some one tell me where I am going wrong? I have tried delete queries to
no avail.
 
93567 -- from your Like "*67*" as it pulls anything with 67 in it.
HMLA* -- from your Like "*L" as it pulls anything with L in it.
Electric -- from your Like "*L" as it pulls anything with L in it.
 
The first is true, but the pattern would have to be "*L* for the second and
third to be so.

Ken Sheridan
Stafford, England
 
Suggest you post back with the complete SQL of the query.

Ken Sheridan
Stafford, England
 
INSERT INTO STDForReports ( MODEL, serial, fscm, ecn, nomen, customer,
sub_cust, std_cal )
SELECT [Standards with extra customers].MODEL, pcinventry.serial,
pcinventry.fscm, pcinventry.ecn, pcinventry.nomen, pcinventry.customer,
pcinventry.sub_cust, pcinventry.std_cal
FROM [Standards with extra customers] INNER JOIN pcinventry ON [Standards
with extra customers].ecn = pcinventry.ecn
GROUP BY [Standards with extra customers].MODEL, pcinventry.serial,
pcinventry.fscm, pcinventry.ecn, pcinventry.nomen, pcinventry.customer,
pcinventry.sub_cust, pcinventry.std_cal
HAVING (((pcinventry.sub_cust) Like "67*" Or (pcinventry.sub_cust) Like "*Q"
Or (pcinventry.sub_cust) Like "*X" Or (pcinventry.sub_cust) Like "7D*" Or
(pcinventry.sub_cust) Like "AK*" Or (pcinventry.sub_cust) Like "MA*" Or
(pcinventry.sub_cust) Like "NC*" Or (pcinventry.sub_cust) Like "CAB" Or
(pcinventry.sub_cust) Like "CPB" Or (pcinventry.sub_cust) Like "JFB" Or
(pcinventry.sub_cust) Like "SDB" Or (pcinventry.sub_cust) Like "*C" Or
(pcinventry.sub_cust) Like "*L" Or (pcinventry.sub_cust) Like "SDP" Or
(pcinventry.sub_cust)="WPP" Or (pcinventry.sub_cust)="9LN" Or
(pcinventry.sub_cust)="9WI" Or (pcinventry.sub_cust)="BAM" Or
(pcinventry.sub_cust)="DIA" Or (pcinventry.sub_cust)="ICE" Or
(pcinventry.sub_cust)="SBA" Or (pcinventry.sub_cust)="XTF" Or
(pcinventry.sub_cust)="ZXQ"))
ORDER BY pcinventry.serial;
 
Hi,

Try the sql statement below. It really shouldn't fix the problem, however,
it is easier to read and maybe figure out the problem.

Karl already mentioned the 67 problem.

I'm wondering if the HMLA* is showing up because there is already a wildcard
in it.

INSERT INTO STDForReports ( MODEL, serial, fscm,
ecn, nomen, customer, sub_cust, std_cal )
SELECT DISTINCT
[Standards with extra customers].MODEL,
pcinventry.serial,
pcinventry.fscm,
pcinventry.ecn,
pcinventry.nomen,
pcinventry.customer,
pcinventry.sub_cust,
pcinventry.std_cal
FROM [Standards with extra customers]
INNER JOIN pcinventry
ON [Standards with extra customers].ecn = pcinventry.ecn
WHERE pcinventry.sub_cust) Like "67*"
OR (pcinventry.sub_cust) Like "*Q"
OR (pcinventry.sub_cust) Like "*X"
OR (pcinventry.sub_cust) Like "7D*"
OR (pcinventry.sub_cust) Like "AK*"
OR (pcinventry.sub_cust) Like "MA*"
OR (pcinventry.sub_cust) Like "NC*"
OR (pcinventry.sub_cust) Like "*C"
OR (pcinventry.sub_cust) Like "*L"
OR (pcinventry.sub_cust) IN ("SDP", "SDB", "JFB", "CPB","CAB",
"WPP", "9LN", "9WI", "BAM", "DIA", "ICE", "SBA", "XTF" ,"ZXQ")
ORDER BY pcinventry.serial;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

DevilDog1978 said:
INSERT INTO STDForReports ( MODEL, serial, fscm, ecn, nomen, customer,
sub_cust, std_cal )
SELECT [Standards with extra customers].MODEL, pcinventry.serial,
pcinventry.fscm, pcinventry.ecn, pcinventry.nomen, pcinventry.customer,
pcinventry.sub_cust, pcinventry.std_cal
FROM [Standards with extra customers] INNER JOIN pcinventry ON [Standards
with extra customers].ecn = pcinventry.ecn
GROUP BY [Standards with extra customers].MODEL, pcinventry.serial,
pcinventry.fscm, pcinventry.ecn, pcinventry.nomen, pcinventry.customer,
pcinventry.sub_cust, pcinventry.std_cal
HAVING (((pcinventry.sub_cust) Like "67*" Or (pcinventry.sub_cust) Like "*Q"
Or (pcinventry.sub_cust) Like "*X" Or (pcinventry.sub_cust) Like "7D*" Or
(pcinventry.sub_cust) Like "AK*" Or (pcinventry.sub_cust) Like "MA*" Or
(pcinventry.sub_cust) Like "NC*" Or (pcinventry.sub_cust) Like "CAB" Or
(pcinventry.sub_cust) Like "CPB" Or (pcinventry.sub_cust) Like "JFB" Or
(pcinventry.sub_cust) Like "SDB" Or (pcinventry.sub_cust) Like "*C" Or
(pcinventry.sub_cust) Like "*L" Or (pcinventry.sub_cust) Like "SDP" Or
(pcinventry.sub_cust)="WPP" Or (pcinventry.sub_cust)="9LN" Or
(pcinventry.sub_cust)="9WI" Or (pcinventry.sub_cust)="BAM" Or
(pcinventry.sub_cust)="DIA" Or (pcinventry.sub_cust)="ICE" Or
(pcinventry.sub_cust)="SBA" Or (pcinventry.sub_cust)="XTF" Or
(pcinventry.sub_cust)="ZXQ"))
ORDER BY pcinventry.serial;


Ken Sheridan said:
Suggest you post back with the complete SQL of the query.

Ken Sheridan
Stafford, England
Click to expand...
Click to expand...
 
Reformatting your criteria for easier understanding.
sub_custs IN ("9LN","9WI","BAM","DIA","ICE,"SBA","XTF","ZXQ")
OR sub_custs LIKE "*[BCLPQX]"
OR sub_custs LIKE Like "*67*"
Or sub_custs Like "7D*"
Or sub_custs Like "AK*"
Or sub_custs Like "MA*"
Or sub_custs Like "NC*"

Electric ends with a C
93567 contains 67
HMLA* should not match based on what you posted, so I can't see why it is
selected.

Your problem is difficult to solve since we don't know what other records you
would want to exclude.

Perhaps you could add a list of values you don't want to match as additional
criteria.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top