A nice quick easy one...

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

Guest

....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
 
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
 
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
..
 
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
 
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
..
 
that is the kind of thing I was thinking but there is a problem. Every line
that doesn't have a "/" I am getting an "#Error"


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
..

scubadiver said:
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...
 
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
..

scubadiver said:
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...
 
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
..

scubadiver said:
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...
 
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...
 
Back
Top