Select records based on what a field begins with? Select records based on what a field begins with? sql sql

Select records based on what a field begins with?

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 CountOfcomorbidityexplanationFROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.CountyGROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanationHAVING (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 CountOfcomorbidityexplanationFROM tblKentuckyCounties AS kINNER 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.CountyWHERE 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.