Counting until change in field

[Guest]

It's been too long since I've worked with VBA...

How do I create a query that returns a badge number and the number of
records in a table with that badge number? (I also need to see the position
title.) The data is sorted by badge number.

While I'm at it, please remind me of the format for a select case, because
once I have the above results, I'll need to check if sales reps have 7
records, managers 3, etc., with a flag if the number of records are different
than they should be.

Thanks!

 

[John Spencer]

What tables do you have that are involved in the query? If it is just one
table then

SELECT BadgeNo, PositionTitle, Count(BadgeNo) as CountThem
FROM YourTable
GROUP BY BadgeNo

Of course that would probably be a bad design, since your PositionTitle is
bound to be a repeating field.

In VBA - (this does not work in a query)
SELECT CASE [SomeValue]
Case "A"
Case "B"
END Select