need help with query

[gls858]

I have a database of item numbers. It contains the same item with
different item numbers ending in CT or EA and one without either
of these suffixes. Example:

1234EA
1234CT
1234
The item number can be alpha numeric and varies in length.

I can find the first two (CT, EA) with a query but how can I get
the last example without the suffix included in the data set?

 

[John Spencer]

Field: ItemNumber
Criteria: Not Like "*EA" and Not Like "*CT"

Or alternative
Field: LastTwo: Right(ItemNumber,2)
Criteria: Not In ("EA","CT")



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[gls858]

Field: ItemNumber
Criteria: Not Like "*EA" and Not Like "*CT"

Or alternative
Field: LastTwo: Right(ItemNumber,2)
Criteria: Not In ("EA","CT")

i see now from the answer that I didn't state my needs
well at all. I need for all three item numbers to show
in the resulting data set. The EA the CT AND the item
number without the suffix.

When I run the query I want to get 1234EA 1234CT and 1234.

Is this possible?

gls858

 

[John Spencer]

Criteria: Like "1234*"

That will return all records where the field starts with 1234

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

i see now from the answer that I didn't state my needs
well at all. I need for all three item numbers to show
in the resulting data set. The EA the CT AND the item
number without the suffix.

When I run the query I want to get 1234EA 1234CT and 1234.

Since the first part of the item number is "alphanumeric" - apparently without
ANY constraints on its content - I think you'll need a fairly complex query.
I'd suggest a UNION query with a subquery:

SELECT <whatever>
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT <whatever>
FROM yourtable
WHERE ItemNumber IN
(SELECT Left([ItemNumber], Len([ItemNumber]) - 1)
FROM yourtable AS X
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

The complexity of this query is a good example of why fields should be
"atomic" - if you're storing 1234EA, 1234CT and 1234 all in a table, and
they're all in a sense "1234 parts", then you really should have TWO fields,
one for the 1234 and a different field for the suffix!

John W. Vinson [MVP]

 

[gls858]

i see now from the answer that I didn't state my needs
well at all. I need for all three item numbers to show
in the resulting data set. The EA the CT AND the item
number without the suffix.

When I run the query I want to get 1234EA 1234CT and 1234.

Since the first part of the item number is "alphanumeric" - apparently without
ANY constraints on its content - I think you'll need a fairly complex query.
I'd suggest a UNION query with a subquery:

SELECT <whatever>
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT <whatever>
FROM yourtable
WHERE ItemNumber IN
(SELECT Left([ItemNumber], Len([ItemNumber]) - 1)
FROM yourtable AS X
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

The complexity of this query is a good example of why fields should be
"atomic" - if you're storing 1234EA, 1234CT and 1234 all in a table, and
they're all in a sense "1234 parts", then you really should have TWO fields,
one for the 1234 and a different field for the suffix!

John W. Vinson [MVP]

John,
Thanks for your input. I think you've got the right idea. The only
part I don't understand is this. Seems like it should be - 2.

WHERE ItemNumber IN

(SELECT Left([ItemNumber], Len([ItemNumber]) - 1)

FROM yourtable AS X

I have about 400 item numbers like this and I would like to identify all
of them.

I certainly agree with you about the way the number should be stored.
Unfortunately this db is provided by my wholesaler and they insist on
selling the same item number in two different units of measure. It's
been a problem in our industry (office supplies) ever since I started 25
years ago. It makes it easy for the customer to order but its areal PITA
for me

 

[gls858]

Criteria: Like "1234*"

That will return all records where the field starts with 1234

John,
Thanks for the input. I need to identify a pretty large number
of items. I think John Vinson is on the right track.

gls858

 

[John W. Vinson]

John,
Thanks for your input. I think you've got the right idea. The only
part I don't understand is this. Seems like it should be - 2.

Right you are. Is it working?

John W. Vinson [MVP]

 

[gls858]

Right you are. Is it working?

John W. Vinson [MVP]

I haven't had a chance to test it yet. Probably going to be tomorrow
now. I'll definely let you know. Really appreciate the help.

The whole thing is a a maintenance nightmare. You have manufactures
buying companies and changing the company codes, you have different
suppliers using different numbers for the same item and selling them
in different units of measure, then add in a third party to process the
data before I get it...well you get the picture.
I'm amazed that it works at all


gls858

 

[gls858]

Right you are. Is it working?

John W. Vinson [MVP]

John,

I understand everything but this line
FROM yourtable AS X
what is X supposed to be?

gls858

 

[gls858]

Right you are. Is it working?

John W. Vinson [MVP]

After some searching I thing the X is a column heading
if I understood what I found in the help file.
I cut and pasted the code into the SQL window of a new
query and when I run it it appears to execute the first
portion then it pauses for a while and gives me an error
message:

Invalid Procedure call

Here's the code:
SELECT ItemNumber
FROM ECNIMaster
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT ItemNumber
FROM ECNIMaster
WHERE ItemNumber IN
(SELECT Left([ItemNumber], Len([ItemNumber]) - 2)
FROM ECNIMaster AS X
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

Input from other is welcome also.

gls858

 

[John W. Vinson]

I understand everything but this line
FROM yourtable AS X
what is X supposed to be?

An Alias for the table name. I'm using the same table in two different
contexts, so I need an alternate name so Access can tell which is which.

John W. Vinson [MVP]

 

[John W. Vinson]

John,

I understand everything but this line
FROM yourtable AS X
what is X supposed to be?

Actually the query should explicitly use the alias:

SELECT <whatever>
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT <whatever>
FROM yourtable
WHERE ItemNumber IN
(SELECT Left(X.[ItemNumber], Len(X.[ItemNumber]) - 2)
FROM yourtable AS X
WHERE X.ItemNumber LIKE "*EA" OR X.ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

John W. Vinson [MVP]

 

[John W. Vinson]

After some searching I thing the X is a column heading
if I understood what I found in the help file.
I cut and pasted the code into the SQL window of a new
query and when I run it it appears to execute the first
portion then it pauses for a while and gives me an error
message:

Try my revised query:

SELECT <whatever>
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT <whatever>
FROM yourtable
WHERE ItemNumber IN
(SELECT Left(X.[ItemNumber], Len(X.[ItemNumber]) - 2)
FROM yourtable AS X
WHERE X.ItemNumber LIKE "*EA" OR X.ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

The Invalid Procedure Call might happen if there are records with a non-NULL
ItemNumber field that is only one character long, giving an invalid negative
argument to the Left() function... is that possible?

John W. Vinson [MVP]

 

[gls858]

After some searching I thing the X is a column heading
if I understood what I found in the help file.
I cut and pasted the code into the SQL window of a new
query and when I run it it appears to execute the first
portion then it pauses for a while and gives me an error
message:

Try my revised query:

SELECT <whatever>
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT <whatever>
FROM yourtable
WHERE ItemNumber IN
(SELECT Left(X.[ItemNumber], Len(X.[ItemNumber]) - 2)
FROM yourtable AS X
WHERE X.ItemNumber LIKE "*EA" OR X.ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

The Invalid Procedure Call might happen if there are records with a non-NULL
ItemNumber field that is only one character long, giving an invalid negative
argument to the Left() function... is that possible?

John W. Vinson [MVP]

Yes that would be possible. If I understand the query, the second part
is searching the entire table and trimming 2 digits from the item
number. I'm sure there are some items numbers that are just 1 digit.

I ran the revised code and I'm now getting a prompt
Enter parameter value: X.ItemNumber

What I would like to see is a query that ends up with just the EA CT
items and their matching item number without the CT or EA.


Maybe what I'm trying to do isn't possible or I'm just taking the wrong
approach.

I'll understand if you just want to pass on this one LOL

gls858

 

[John Spencer]

Perhaps the following will work for you

SELECT ItemNumber
FROM YourTable
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"

This should return all itemNumbers that end in EA or CT AND
AND
any item numbers that don't have EA or CT but that do correspond to those
with EA or CT at the end.

Of course, you need to fill in the correct table and field names.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

After some searching I thing the X is a column heading
if I understood what I found in the help file.
I cut and pasted the code into the SQL window of a new
query and when I run it it appears to execute the first
portion then it pauses for a while and gives me an error
message:

Try my revised query:

SELECT <whatever>
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
UNION ALL
SELECT <whatever>
FROM yourtable
WHERE ItemNumber IN
(SELECT Left(X.[ItemNumber], Len(X.[ItemNumber]) - 2)
FROM yourtable AS X
WHERE X.ItemNumber LIKE "*EA" OR X.ItemNumber LIKE "*CT")
AND ItemNumber NOT LIKE "*EA"
AND ItemNumber NOT LIKE "*CT"

The Invalid Procedure Call might happen if there are records with a
non-NULL
ItemNumber field that is only one character long, giving an invalid
negative
argument to the Left() function... is that possible?

John W. Vinson [MVP]

Yes that would be possible. If I understand the query, the second part is
searching the entire table and trimming 2 digits from the item number. I'm
sure there are some items numbers that are just 1 digit.

I ran the revised code and I'm now getting a prompt
Enter parameter value: X.ItemNumber

What I would like to see is a query that ends up with just the EA CT items
and their matching item number without the CT or EA.


Maybe what I'm trying to do isn't possible or I'm just taking the wrong
approach.

I'll understand if you just want to pass on this one LOL

gls858

 

[gls858]

Perhaps the following will work for you

SELECT ItemNumber
FROM YourTable
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM yourtable
WHERE ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"

This should return all itemNumbers that end in EA or CT AND
AND
any item numbers that don't have EA or CT but that do correspond to those
with EA or CT at the end.

Of course, you need to fill in the correct table and field names.

Thanks for the input John S. I'm getting a invalid procedure call. I
think John V. called it right on this. I have some items numbers that
are only one digit that are causing the invalid call. Maybe if I did
a make table query and eliminated all items less than 3 digits first it
would work. Any idea how I could do that?

You have in fact stated what I'm trying to do quite succinctly.

gls858

 

[John Spencer]

If you are getting an Invalid Procedure error then
-- are you running this query in Access?

-- have you checked your references?

-- will you post the SQL of the query that you are trying to use?

The len of your item numbers should not generate an invalid procedure call
UNLESS you have an ItemNumber that consists solely of the Letters EA or CT.

Try the following, where the match is based on at least one character
followed by CT or EA.

SELECT ItemNumber
FROM YourTable
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM yourtable
WHERE ItemNumber LIKE "*?EA" OR ItemNumber LIKE "*?CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
WHERE ItemNumber Like "*?EA" or ItemNumber LIKE "*?CT"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[gls858]

Thanks for the input John S. I'm getting a invalid procedure call. I
think John V. called it right on this. I have some items numbers that
are only one digit that are causing the invalid call. Maybe if I did
a make table query and eliminated all items less than 3 digits first it
would work. Any idea how I could do that?

You have in fact stated what I'm trying to do quite succinctly.

gls858

Round about solution. I did a make table query and then a find and
replace on ? and ?? and replaced those with ZZZZZZ did a sort by item
number and deleted the ZZZZZZ items. I have a table now with no numbers
less that 2 digits. I'll be testing the query later.

gls858

 

[John W. Vinson]

Thanks for the input John S. I'm getting a invalid procedure call. I
think John V. called it right on this. I have some items numbers that
are only one digit that are causing the invalid call. Maybe if I did
a make table query and eliminated all items less than 3 digits first it
would work. Any idea how I could do that?

Don't need a make table query! Just add an additional criterion on the field

LIKE "??*"

John W. Vinson [MVP]