Ask A Data Miner - 75,000+ Members
I am facing a problem writing a query.
The problem is I have a column with different formulas of chemical molecules. for eg CH3Cl
CbF-(Cb2)
CBF-(Ca)
Now I wrote this query
SELECT *from tbl where MolFormula LIKE 'Cb%'
This returns me row 2 and row 3 whereas I just need row 2 to be returned. I am using MS Access. Thanks for looking into it.
Replies (6)
A small change to your SQL Query may get it to work. You may need to alias your inner 'Select' statement.
Try this and give me a feedback:
SELECT MolFormula FROM
(
SELECT MolFormula
FROM Group_Table
where INSTR(1,MolFormula,'Cbf',0)<>0
) AS S
WHERE MolFormula LIKE 'Cbf%'
This should work:
SELECT MolFormula FROM
(
SELECT MolFormula
FROM Group_Table
where INSTR(1,MolFormula,'Cbf',0)<>0
) AS S
WHERE MolFormula LIKE 'Cbf*'
MsAccess may not support case-insensitive queries. You may have to compare the casing of the words returned in VBA or some other middle tiercode.
Will you be able to do that?
Follow On Twitter
Request More Information
SELECT MolFormuala
FROM Group_Table
where INSTR(1,MolFormula,'Cbf',0)<>0
This query returns all molecules containing 'Cbf' but making a case sensitive search. This is what the result set is
MolFormula
Cbf-(Cb,2Cbf)
Cbf-(3Cbf)
Cbf-(2Cb,Cbf)
Cbf-(Cbf,2Nb)
Cb-(C,Cbf,Cb)
CbBr-(Cbf,Cb)
CbCl-(Cbf,Cb)
CbH-(Cbf,Cb)
pretty good. But now I want only those that start with Cbf i.e. the first 4 rows.
I tried to further filter :
SELECT MolFormula FROM
(
SELECT MolFormula
FROM Group_Table
where INSTR(1,MolFormula,'Cbf',0)<>0
)
WHERE MolFormula LIKE 'Cbf%'
but I don't get any results. Where am I going wrong?