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.