Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Formula (Access2000)

    Hello Access Gurus,

    Is there a way I can create an array formula in Access. I know it's possible in Excel using the Ctrl+Shft+Enter but what about Access?

    Thanks a bunch!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    New Lounger
    Join Date
    May 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Formula (Access2000)

    Hi,

    At my knowledge the answer is no. But, what do you need an array formula in Access if I may ask?

    Regards,

    Nick

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Formula (Access2000)

    I have a table called "employment category" with column names PT, FT, VP, FP, UV, Dept#. I would like to create a formula called "fulltime employees" that will give me a sum of all FT and FP for a selected dept number.
    such as: {=SUM(IF(dept#=7865,FT+FP,0))}

    Please let me know if this example is confusing. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Formula (Access2000)

    You're confusing yourself. Even though an Access table *looks* much like an Excel spreadsheet, it is entirely different. Rows in Access tables are independent, not inherently ordered as in a spreadsheet, and an "array formula" is meaningless in an Access context.

    First of all, you can't base table fields on values in other fields. Second, You don't store totals in a record. You need to get familiar with queries, which is where you would total up information like that when you need it, but you wouldn't actually store those totals anywhere.
    Charlotte

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Formula (Access2000)

    You should create a query where you will create a field for the sum of those two fields and then after in a form or report with record source based on that newly created query you will filter info based on your dept or whatever you want.

    Regards,

    Nick

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Formula (Access2000)

    You could use =dsum("[FT]+[PV]","fulltime employees","Dept=7865")

    I think that is right but Charlotte will comment if I have too many quotes in it.
    David Grugeon
    Brisbane Australia

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Formula (Access2000)

    Do I look like the quotes monitor?? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> There's nothing wrong with your formula. However, it can only be used in a form or in an expression in a query. You still can't use it in a table.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Formula (Access2000)

    Now that you mention it. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>


    Just kidding.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Formula (Access2000)

    Charlotte , Thanks

    I agree you can't use dsum in a table but then you woudn't want to.

    The question didn't imply that. It just wanted a total, I assumed it would be in a control in a form.

    NCordero

    It is so easy to misunderstand what people really want. Starting to use Access is a tremendous leap from Excel and it takes quite a time to acquire the different way of looking at things. In Excel there is really only a cell to put things in. In Access they can be put in a table, a form or a report and you really have to understand the different purposes of each.

    I hope you let us know if we gave you anything useful.
    David Grugeon
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •