A nice quick easy one...

S

scubadiver

....though I'm not so sure myself!

Hello,

From the following list in a query

product

ALS
ALS/24
CDWS
CDWS/4
CDWS/OSS
CDWS/RSR
DWS
DWS/4
DWS/OSS
DWS/HER
DWS/RSR
DWS/BUNDLE

I would like just the following

ALS
CDWS
DWS

thanks
 
M

Michel Walsh

Get the basic data with something like:

SELECT product As root, (product & "/*" ) As mask
FROM tableName
WHERE NOT(product LIKE "*/*")
GROUP BY product



then, to 'replace' the product by its root:


SELECT a.*, b.root
FROM tableName As a INNER JOIN previousQuery AS b
ON a.product LIKE b.mask




Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer

WHERE Product Not Like "*/*"

That should return all records that don't contain a "/"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

scubadiver

Cheers

John Spencer said:
My error, appended the slash in the wrong place.


Field: NoSlash: Left([Your TableName].[Your Field Name] ,
Instr(1,[Your TableName].[Your Field Name] & "/","/")-1)


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

scubadiver said:
I guess the code is assuming that every line has a "/" ??


John Spencer said:
You can use a calculated field to get the result you want.

Field: NoSlash: Left([Your TableName].[Your Field Name] & "/",
Instr(1,[Your
TableName].[Your Field Name],"/")-1)

NoSlash can be replaced with the name of your field or with any other
name
you want to give the calculated field.

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


Hello,

thanks for the reply. I didn't think that queries could be filtered the
way
you suggested so I wasn't too clear with my question.

I don't want to filter the records, just get rid of the / and
everything
to
the right.

So this...

ALS
ALS/24
CDWS
CDWS/4
CDWS/OSS
CDWS/RSR
DWS
DWS/4
DWS/OSS
DWS/HER
DWS/RSR
DWS/BUNDLE
OSS/OSS

should become this...

ALS
ALS
CDWS
CDWS
CDWS
CDWS
DWS
DWS
DWS
DWS
DWS
DWS
OSS

cheers
Click to expand...
Click to expand...
Click to expand...
 
Top