Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Thanked 0 Times in 0 Posts

    Changing Excel formulas into access (2000)

    Hi This is my 4th day using Access. I have attached a Excel worksheet that I need to put into access and I'm not sure how to tell access to do the two of the formulas. I need to let the boss now how many clients we have and how much room we have left.
    Thanks for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 Posts

    Re: Changing Excel formulas into access (2000)

    I recommend that you suggest to your boss that (s)he should send you to one or more Access courses, and let you buy a good book about Access, for example Alison Balter's Mastering Microsoft Access 2000 Development. Without that, you'll be floundering in Access.

    Say that you have created a table tblData looking like A1:C5 in your workbook. I assume that the Client field will never be blank (that might make sense in Excel, but not in Access), but that the Shelf field might be blank for some records. Create a query in design view based on this table and add two calculated columns:

    NumberOfClients: Count(*)

    ShelvesLeft: 1000+Sum(Not IsNull([Shelf]))

    The first expression simply counts the number of records. The second one uses the fact that True = -1 and False = 0 in Access. Not IsNull([Shelf]) is -1 if the Shelf field is filled, 0 if it is blank. Taking the Sum counts each non-blank value as -1, so by adding it to 1000, we subtract the number of used shelves.

Posting Permissions

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