Your suggested solution worked like a charm. Thank you very much. What a great forum to learn things.
Terryomsn
--- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno" <graham@...> wrote:
>
> 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
>
Tidak ada komentar:
Posting Komentar