Count number of records that start with specific text

[Guest]

I have an ID card system and would like to create a query that shows how many
cards in a certain range are still valid (haven't been turned in). We
currently have cards numbered from AP150001 through AP150856, with some of
them turned in. Also, the cards are "grouped" together on separate log
sheets (for accountability purposes). Here is an example of what I need:
100 cards (#AP150501 through #AP150600) are on a specific log, with 10 of
them being turned in. I need a query to calculate that 100 cards were
issued, with 90 still outstanding. I tried counting using "Like" statements
and a DCount statement, but nothing brought up the correct information.
"BadgeNumber" is the field and "Person" is the table. Both fields are text.
I didn't create this database, but I have been tasked with querying the
information for reports.

 

[Guest]

I'm soooo close. If the range was something like #AP150500 through #AP150599,
and it always started with two characters, and the numbers were always 6
characters, you could use the Mid function to extract the important part and
group on it.

Something like:

Select Mid([BadgeNumber],3,4) as BadgeGroup,
Count(([BadgeNumber]) as BadgeCount
From Person
Group By Mid([BadgeNumber],3,4)
Order By Mid([BadgeNumber],3,4) ;

Close but no cigar.

 

[Larry Daugherty]

In the QBE Query Wizard select "Totals" as the query type. In the
criteria line for Badge put Between *150501 and *130600

HTH

 

[Guest]

The Between Function works great, but now I'm finding that I need to pull
another query to count the total number of records because the Between
results bring up a "1" for each badge. Example: 150001=1, 150002=1,
150003=1, etc. It's not totalling unless I pull another query to total the
CountofBadgeNumber field. I also need to pull the information on how many
badges are outstanding (BadgeReturned is False) or Lost (Form90 is True). Do
I need to create 4 queries for each set of badges? Example: Records
AP150501-AP150600 equal 100 badges. I need one query to pull the 'Between'
and 'BadgeReturned is False' functions, then another query to count the
number of records that were pulled. Same for pulling a query for 'Between'
and 'Form90 is True' functions and then another to count the records.

I have 4 queries for each set of 100 records (they must be grouped to
coincide with the accountability log sheet that they are listed on), so
seeing as we're in the 800 series now, that's a lot of queries. I then need
to create a whole new query to bring these all together so that I can put the
information on a report. Is there an easier way?

 

[Guest]

Thanks for your help, but I found out how to do this on my own. I created
one query for each set of records and simply added the fields for
BadgeReturned and Form90 to the query. Then on the form, I simply added
unbound text boxes and entered the expression =DCount("*", "Person",
"[BadgeReturned] = 'F'") to calculate how many badges were still valid. I
modified the same DCount equation to calculate Form90=T to count how many
badges were lost (not simply destroyed). I then simply created unbound text
boxes that then calculated totals.

Thanks again for your assistance.