problem with aggregating data

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Robin Gaster
    #1

    problem with aggregating data

    Hi all,

    I have a grants database with about 20,000 records. Grants are
    recorded annually, and given a 15 digit alphanumeric code. Extensions
    to the grant are given the same code with different last two letters.
    I need to be abel to aggregate all grants and extensions.

    Additionally, I need to be able to find codes ending in xxxAx, and
    change (or display in a dataset) those codes to xxx, eliminating the
    last two digits.

    This is apparently well beyond my skill level with access.

    Ideas?

    Thanks

    BTW, if you are an access consultant and interested in solving small
    problems for a fee, I'd also like to talk to you.

    Robin Gaster
    301-589-5965
    Tags: None
    • Phil Stanton
      #2
      Re: problem with aggregating data

      Are you sayind you want the total of the grants grouped by the 15 digit
      code?

      If so what do you call your fields?

      Phil

      "Robin Gaster" <rgaster@nort h-atlantic.com> wrote in message
      news:8d2ea509.0 311230612.4a515 ac1@posting.goo gle.com...[color=blue]
      > Hi all,
      >
      > I have a grants database with about 20,000 records. Grants are
      > recorded annually, and given a 15 digit alphanumeric code. Extensions
      > to the grant are given the same code with different last two letters.
      > I need to be abel to aggregate all grants and extensions.
      >
      > Additionally, I need to be able to find codes ending in xxxAx, and
      > change (or display in a dataset) those codes to xxx, eliminating the
      > last two digits.
      >
      > This is apparently well beyond my skill level with access.
      >
      > Ideas?
      >
      > Thanks
      >
      > BTW, if you are an access consultant and interested in solving small
      > problems for a fee, I'd also like to talk to you.
      >
      > Robin Gaster
      > 301-589-5965[/color]


        Comment

        • Michael Hopwood
          #3
          Re: problem with aggregating data

          What you should be doing is storing the 15 digit Grant code in one field and
          the extension in a seperate field, can you do this?

          If not, when you query your data you need to split the grant code from the
          extension:

          GrantCode: Left([GrantID],15)

          Extension: iif(Len([GrantID])=15,"",right([GrantID],2))

          The above was written on-the-fly, but I'm sure you get the gist?

          --
          Michael Hopwood (Phobos)


          "Robin Gaster" <rgaster@nort h-atlantic.com> wrote in message
          news:8d2ea509.0 311230612.4a515 ac1@posting.goo gle.com...[color=blue]
          > Hi all,
          >
          > I have a grants database with about 20,000 records. Grants are
          > recorded annually, and given a 15 digit alphanumeric code. Extensions
          > to the grant are given the same code with different last two letters.
          > I need to be abel to aggregate all grants and extensions.
          >
          > Additionally, I need to be able to find codes ending in xxxAx, and
          > change (or display in a dataset) those codes to xxx, eliminating the
          > last two digits.
          >
          > This is apparently well beyond my skill level with access.
          >
          > Ideas?
          >
          > Thanks
          >
          > BTW, if you are an access consultant and interested in solving small
          > problems for a fee, I'd also like to talk to you.
          >
          > Robin Gaster
          > 301-589-5965[/color]


            Comment

            Previous template Next
            Working...