Results 1 to 2 of 2
2004-11-26, 02:06 #1
- 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
2004-11-26, 07:13 #2
- 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:
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.