Select Query to find ID

[Guest]

Hello,

I need assistance in the expression to select a unique symbol or number to
search for all records that end with this unique ID.For example.....I have
product names in my search but some products have been assigned an * or a
number 1 at the end of the name such as SHOES* or TIES1. Is there a "Like"
expression that would only select all records that only end in either one of
these symbols so i could end up with a column for ties1 and one for shoes*?
Thanks

 

[John Spencer (MVP)]

The criteria for shoes* would be

LIKE "*[*]"

That is because * is a wild card character meaning zero to many characters. If
you put it into the brackets it loses its special meaning.

For the other case, it is simply
LIKE "*1"

That simply means any string that ends in "1".

I'm not sure what you mean by "...i could end up with a column for ties1 and one
for shoes*?".

Does this mean you want two separate columns and want to query for both things
at once?

 

[Guest]

Hello John,

Would your previous response change much for the LIKE expression because the
answer is...
Yes, i want to run a query to get two separate columns and want to query for
both things

at once?I will also be using the sum function to produce totals for each column since each name will have an invoice amount and these totals will in effect give me how much in sales for TIES1 and SHOES* .......

Thanks

The criteria for shoes* would be

LIKE "*[*]"

That is because * is a wild card character meaning zero to many characters. If
you put it into the brackets it loses its special meaning.

For the other case, it is simply
LIKE "*1"

That simply means any string that ends in "1".

I'm not sure what you mean by "...i could end up with a column for ties1 and one
for shoes*?".

Does this mean you want two separate columns and want to query for both things
at once?

Hello,

I need assistance in the expression to select a unique symbol or number to
search for all records that end with this unique ID.For example.....I have
product names in my search but some products have been assigned an * or a
number 1 at the end of the name such as SHOES* or TIES1. Is there a "Like"
expression that would only select all records that only end in either one of
these symbols so i could end up with a column for ties1 and one for shoes*?
Thanks

 

[John Spencer (MVP)]

Well it is a bit more complex if you don't have the items in two columns. You
will have to add two calculated columns to your query.

Field: Stars: IIF(Right(YourField,1)="*",YourField,"")

Field: Ones: IIF(Right(YourField,1)="1",YourField,"")


And then apply the criteria as follows

Field: FindThis: Right(YourField,1)
Criteria: IN ("*","1")

This is becoming more and more indicative of a bad table design. You seem to be
storing two (or more) pieces of data in one field - bad idea in most cases. One
thing is Clothing Item and second thing is "Special identifier".

Anyhow, I hope this will solve your problem for right now.

Hello John,

Would your previous response change much for the LIKE expression because the
answer is...
Yes, i want to run a query to get two separate columns and want to query for
both things

at once?I will also be using the sum function to produce totals for each column since each name will have an invoice amount and these totals will in effect give me how much in sales for TIES1 and SHOES* .......

Thanks

The criteria for shoes* would be

LIKE "*[*]"

That is because * is a wild card character meaning zero to many characters. If
you put it into the brackets it loses its special meaning.

For the other case, it is simply
LIKE "*1"

That simply means any string that ends in "1".

I'm not sure what you mean by "...i could end up with a column for ties1 and one
for shoes*?".

Does this mean you want two separate columns and want to query for both things
at once?

Hello,

I need assistance in the expression to select a unique symbol or number to
search for all records that end with this unique ID.For example.....I have
product names in my search but some products have been assigned an * or a
number 1 at the end of the name such as SHOES* or TIES1. Is there a "Like"
expression that would only select all records that only end in either one of
these symbols so i could end up with a column for ties1 and one for shoes*?
Thanks

 

[Guest]

Thanks John,
I did not think of creating a table just for a number 1 and an *...I am new
to Acess and the thought was to select records with an identifier to get
separate results.If you could give me a heads up on how it should be done,
please give me a short example since your responses have been spot on and i
want to get it right.Thanks for all the support and help

Well it is a bit more complex if you don't have the items in two columns. You
will have to add two calculated columns to your query.

Field: Stars: IIF(Right(YourField,1)="*",YourField,"")

Field: Ones: IIF(Right(YourField,1)="1",YourField,"")


And then apply the criteria as follows

Field: FindThis: Right(YourField,1)
Criteria: IN ("*","1")

This is becoming more and more indicative of a bad table design. You seem to be
storing two (or more) pieces of data in one field - bad idea in most cases. One
thing is Clothing Item and second thing is "Special identifier".

Anyhow, I hope this will solve your problem for right now.

Hello John,

Would your previous response change much for the LIKE expression because the
answer is...
Yes, i want to run a query to get two separate columns and want to query for
both things

at once?I will also be using the sum function to produce totals for each column since each name will have an invoice amount and these totals will in effect give me how much in sales for TIES1 and SHOES* .......

Thanks

The criteria for shoes* would be

LIKE "*[*]"

That is because * is a wild card character meaning zero to many characters. If
you put it into the brackets it loses its special meaning.

For the other case, it is simply
LIKE "*1"

That simply means any string that ends in "1".

I'm not sure what you mean by "...i could end up with a column for ties1 and one
for shoes*?".

Does this mean you want two separate columns and want to query for both things
at once?

joe wrote:

Hello,

I need assistance in the expression to select a unique symbol or number to
search for all records that end with this unique ID.For example.....I have
product names in my search but some products have been assigned an * or a
number 1 at the end of the name such as SHOES* or TIES1. Is there a "Like"
expression that would only select all records that only end in either one of
these symbols so i could end up with a column for ties1 and one for shoes*?
Thanks

 

[John Spencer (MVP)]

Sorry to take so long to respond back. NO, I would probably not make a separate
table, I would make a separate column in the table.

Thanks John,
I did not think of creating a table just for a number 1 and an *...I am new
to Acess and the thought was to select records with an identifier to get
separate results.If you could give me a heads up on how it should be done,
please give me a short example since your responses have been spot on and i
want to get it right.Thanks for all the support and help

Well it is a bit more complex if you don't have the items in two columns. You
will have to add two calculated columns to your query.

Field: Stars: IIF(Right(YourField,1)="*",YourField,"")

Field: Ones: IIF(Right(YourField,1)="1",YourField,"")


And then apply the criteria as follows

Field: FindThis: Right(YourField,1)
Criteria: IN ("*","1")

This is becoming more and more indicative of a bad table design. You seem to be
storing two (or more) pieces of data in one field - bad idea in most cases. One
thing is Clothing Item and second thing is "Special identifier".

Anyhow, I hope this will solve your problem for right now.

Hello John,

Would your previous response change much for the LIKE expression because the
answer is...
Yes, i want to run a query to get two separate columns and want to query for
both things
at once?I will also be using the sum function to produce totals for each column since each name will have an invoice amount and these totals will in effect give me how much in sales for TIES1 and SHOES* .......

Thanks

:

The criteria for shoes* would be

LIKE "*[*]"

That is because * is a wild card character meaning zero to many characters. If
you put it into the brackets it loses its special meaning.

For the other case, it is simply
LIKE "*1"

That simply means any string that ends in "1".

I'm not sure what you mean by "...i could end up with a column for ties1 and one
for shoes*?".

Does this mean you want two separate columns and want to query for both things
at once?

joe wrote:

Hello,

I need assistance in the expression to select a unique symbol or number to
search for all records that end with this unique ID.For example.....I have
product names in my search but some products have been assigned an * or a
number 1 at the end of the name such as SHOES* or TIES1. Is there a "Like"
expression that would only select all records that only end in either one of
these symbols so i could end up with a column for ties1 and one for shoes*?
Thanks