ACCESS FEED: RE: [MS_AccessPros] Count of Values for One Field Within Another

Kamis, 08 September 2011

RE: [MS_AccessPros] Count of Values for One Field Within Another

Hi Terry

You can ignore the last two digits and group by the first three, counting
all records:

SELECT Left(SvcBlock,3) as SvcRte, Count(*) as SvcRunCount FROM <YourTable>
GROUP BY Left(SvcBlock,3);

I am assuming, from the leading zeroes, that the field is text. If the
field really is numeric, then use integer division instead:

SELECT SvcBlock\100 as SvcRte, Count(*) as SvcRunCount FROM <YourTable>
GROUP BY SvcBlock\100;

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terry Olsen
Sent: Friday, 9 September 2011 10:03
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Count of Values for One Field Within Another

I need help in either query builder syntax of SQL syntax to count values for
one field within another.
I have a table with a column called SvcBlock (Length of 5, numeric)
SvcBlock is parsed into two columns:
SvcRte (Left (SvcBlock,3)) and SvcRun (Right (SvcBlock,2))
09701
09702
09801
10001
10002
10003
10104
I need a query that will return the count of SvcRun within SvcBlock
From the data example above, the result set I need would be:
097 2
100 3
101 1