Query Wildcards

G

GOL

I have a query that returns products in different colors. When the query is
ran the results look something like this

Product Color Units
EGGG44/5 YHHH 5
EGGG44/1 YHHH 25
EGGG44/1/A THHE 50
PHA899/A/1 THH88 100

The product is paint, which is available in the size shown (/1 which is 1
gallon or /5 which is 5 gallon; color, and units available. I am trying to
run a query that shows how many products to each color without paying
attention to size (1 or 5 gallon). In other words I would like the query to
show

Product Color Units
EGGG44 YHHH 80
PHA899 THH88 100
which adds up all the EGGG44'S to that color, not taking into account
anything after the /. I'm thinking some kind of wildcard criteria or filter
may be able to fix this, or some kind of subdatasheet in my query. Anyone
have any direction?
 
D

Douglas J. Steele

You can add a computed field to your query that strips off everything after
the first slash:

SubProduct: Left$([Product], InStr([Product], "/") -1)

If not all products have that slash in them, you'll need to use

SubProduct: IIf(InStr([Product], "/") > 0, Left$([Product], InStr([Product],
"/") -1, [Product])
 
Top