summarizing table data

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

I'm fairly new to access and have a table containing a
column with 'pole numbers'. these numbers are in the form
##-#### or ?##-#### (for example, 88-3465 or E78-5687).
the two numbers to the left of the dash give the year of
the pole (88-3465 was installed in 1988). I would like to
count the number of poles per decade (starting with 1900-
1909) and sum these numbers up in a table, report, or
something... I'm not sure how to code this and best
implement this procedure..would the code go in a module
and be referenced by a query or what? thanks in advance
mike
 
you will have to do this with a nested query
first step will be to just get the year information from
your string
in the QBE for your pole field change what is displayed in
the query to:
cint(left(right(left([pole], len([pole])-5),2)),1)*10

this will give you just the 10s digit regardless if there
are three or two characters to the right of the '-' and
converts it to an int then multiplies by 10 to make 9 to
90.

Then with this information group and count.

let me know if this helps

mweyland at mnqio dot sdps dot org
 
Back
Top