Using Switch to post a numberic value in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large group of item numbers which I need to group in a unique way.
Basically I need to take all my items which start with "G" and "H" and assign
them to one group and then things starting with "D" and "X" and assign them
to another group.
After hours of reading help screens I am trying to do the following. I
figured I could do a query that assigned a "1" to my G's and H's and then
assign a "2" to my "D"'s and "X"'s. I could then sort by these numbers for
my report.
I am not experienced with functions and must be doing something very stupid.
I am in the query design view. It have put the table in and have pulled
down the 'Item field". In the criteria section, I am trying the following.
Swith(([G*],1)([H*],1)([D*],2)([X*],2))
Any help would be SO appreciated.
Thank you, Teresa
 
Teresa said:
I have a large group of item numbers which I need to group in a unique way.
Basically I need to take all my items which start with "G" and "H" and assign
them to one group and then things starting with "D" and "X" and assign them
to another group.
After hours of reading help screens I am trying to do the following. I
figured I could do a query that assigned a "1" to my G's and H's and then
assign a "2" to my "D"'s and "X"'s. I could then sort by these numbers for
my report.
I am not experienced with functions and must be doing something very stupid.
I am in the query design view. It have put the table in and have pulled
down the 'Item field". In the criteria section, I am trying the following.
Swith(([G*],1)([H*],1)([D*],2)([X*],2))
Click to expand...


The first problem is that you would not use the expression
in a criteria, it should be a calculated field. Second, the
Switch function doesn't know how to use wildcard characters.
You could use Left to isolate the first character of the
field:
Switch(Left(Item,1)="G",1, Left(Item,1)="H",1, . . .)

But that will rapidly become too unwieldy to be practical.
Even ignoring the complexities of the expression, it would
be a poor practice to embed the grouping numbers in a query.
What will you do when you have to change the group values?

A far better appraoch is to have a grouping number field in
a table. Preferably you have a table with the names of the
items and you would add the grouping number field to that
table. With that arrangement, your query would join to that
table and include the grouping number field in the query so
it would be available for use where ever you need to do the
sorting and or grouping.
 
Back
Top