Select records based on what a field begins with?

I have a database with a table that has identifiers c1, c2, c3..etc..

Instead of writing a query that has a bunch of ORs in it, how can I modify the below query with something that will catch all the records that begin with a certain letter?

SELECT 
    Person.spineinjuryAdmit, 
    tblComorbidity.comorbidityexplanation,
    Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation

FROM tblKentuckyCounties 
INNER JOIN (tblComorbidity 
            INNER JOIN (Person 
                        INNER JOIN tblComorbidityPerson 
                            ON Person.PersonID = tblComorbidityPerson.personID) 
                ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) 
    ON tblKentuckyCounties.ID = Person.County
GROUP BY    Person.spineinjuryAdmit, 
            tblComorbidity.comorbidityexplanation
HAVING (((Person.spineinjuryAdmit)="c1" Or 
         (Person.spineinjuryAdmit)="c2开发者_运维技巧" Or 
         (Person.spineinjuryAdmit)="c3"));

Have you tried using LIKE? As an example:

SELECT * FROM patients WHERE lastName LIKE 'm%';

This would return records where patients.lastName starts with 'm'. The '%' character may be '*' for access, I can't remember. In some databases, you can also use '_' which will match a single character (or however many underscores you add).

    SELECT Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation, Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County
GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation
HAVING (Person.spineinjuryAdmit LIKE "c*");

You can use a WHERE clause to exclude the rows you don't want before doing the GROUP BY.

SELECT 
    p.spineinjuryAdmit, 
    c.comorbidityexplanation,
    Count(c.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties AS k
INNER JOIN (tblComorbidity AS c
            INNER JOIN (Person AS p
                        INNER JOIN tblComorbidityPerson AS cp
                            ON p.PersonID = cp.personID) 
                ON c.ID = cp.comorbidityFK) 
    ON k.ID = p.County
WHERE p.spineinjuryAdmit ALike "c%"
GROUP BY    p.spineinjuryAdmit, 
            c.comorbidityexplanation

If your query is executed in SQL-89 mode, you can use this as your WHERE clause.

WHERE p.spineinjuryAdmit Like "c*"

In SQL-92 mode, you need the standard ANSI wild card.

WHERE p.spineinjuryAdmit Like "c%"

I used ALike to tell the database engine to expect ANSI wild cards.

SQL-89 mode is used by DAO ... unless you've set the database option to use SQL-92 mode ("SQL Server compatible syntax).

If you're running a query with ADO, it will always use SQL-92 mode.

You can use regexp to query all rows that starts with several characters.

SELECT * FROM table WHERE column REGEXP '^[ c1, c2, c3]';

This query will return all rows where column starts with 'c1' or 'c2' or 'c3'.

You have two options:

1. Use the LIKE operator

2. Use the IN operator

For example:

Person.spineinjuryAdmit LIKE "c*"

Person.spineinjuryAdmit IN ("c1", "c2", "c3")

See http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx for details about LIKE.

Fair warning: The wildcards of LIKE in Access are * and ? instead of % and _ (as is the case for most other versions of SQL).

You could modify this to include the filter list in the WHERE clause. The following will find patients whose last name starts with Smith. (i.e. Smith and Smithson, etc), and those whose Admit start with c.

....
WHERE spineinjuryAdmit LIKE 'c*'
AND   Patient.FirstName LIKE 'Smith*'
GROUP BY    Person.spineinjuryAdmit,             
            tblComorbidity.comorbidityexplanation;