Counting LIKE records

[matt330]

Quick overview. I have a site # and Subject #. The subject # is 7 digits and the first 4 are the site # (exp. Site # 1000, Subject # 1000001, 1000002, etc). At times the subjects switch sites but their subject # remains the same so Subject # 1000001 now resides at site # 2000.

How would I write a query like the following?

Count [tbl_Enrollment]![Subject #] WHERE [first 4 digits of the subject #] LIKE [tbl_Site_Dem]![Site #]

 

[RV]

The subject # is 7 digits and the first 4 are the site #

Not an answer to your question but some free advice
Your subject is based on hence derived from your site.
Storing derived data is a no no.
You've just encountered why (site changes, subject doen't).
Remove the "site" part from your subject.
Use a query to concatenate site and subject.

RV

 

[matt330]

Thanks for the reply, that was how I set up the tables initially, but doing that there's no way to capture transferred subjects (which is also essential), that I can think of. If the first four digits of the subject # come from a concatenation of the site from which they were enrolled, when I change the site that they are being seen, their subject number will change.

For instance, a subject is the 1st patient enrolled at site 1001. Concatenating the two #'s would result in a subject # of 1001001, which would be fine. But in the event they transfer to another site (say 1002), the DB set up that way would change the subject # to 1002001. My goal, on transfers, is that the subject # stays the same, in other words to keep it subject 1001001, being seen at site 1002.

Otherwise your idea is ideal and how I would prefer this would be set up.

 

[neileg]

Left([Subject #],4) will extract the site number from your subject number. Don't use LIKE as a comparator, use =

It's a bad idea to use spaces or punctuation in your object names. SiteNo is much preferred to [Site #].