Access vs Excel

[mari_hitz]

Hi everyone, hope you are great. I was looking for some advice.
It turns out that I have a pretty good "tool" in excel that basically whats it does, you enter information in the cells and returns you a number based on a calculation. This formula contains a simple "IF", if there is an "X" written in that cell, at the end of the table a sumatory is done.
Well, since excel is pretty limited in amount of data, I was searching to move it to access and I was wondering if it was actually possible, because I don't know if you can enter in access information in a cell from a table and in another cell for a formula to be performed. Do you know if this is feaseble or not?


Thanks in advance.

 

[CJ_London]

would need to see what your expectations actually are, but Access can do everything that Excel can do and more, but does it differently.

You need to not think in Excel terms to get the most of of Access although many of the options are similar (IF in excel is Iif in Access for example).

Access is stricter about datatypes - 1234 in Excel could be text or a number but in Access it would have to be a number to add or multiple for example - but then there are plenty of functions to do a conversion.

 

[Alansidman]

This may be helpful

http://forums.aspfree.com/microsoft-access-help-18/access-vs-excel-349267.html

 

[Pat Hartman]

"Cell" is a concept unique to spreadsheets and does not exist in a relational database. A cell in Excel may contain data or a formula and it may contain formatting information. In a relational database, tables only contain data. Any calculations you need are done with queries, VBA, or macros and formatting is done to controls in forms and reports so queries are never formatted with color and fonts the way cells are.

 

[mari_hitz]

Hi everyone!

All your answers have been great. Actually what I am trying to do it to create a data entry form that looks similar to a matrix. I could investigate that pivot tables in Access are possible, however, they are not editable.
Basically what I am trying to achieve is a table similar to this
Done? Time to perform task Total People Needed to perform task
Task 1 Yes 67 =if(B2="Yes",C2*150,0)
Task 2 No 120
Task 3 Yes 230
Task 4 Yes 15
........

I was asked to put this into Access, however I don't know if it possible. I mean, I know that access is different than excel, but similar to this or not similar, can this be replicated in access?

Thanks!

 

[Pat Hartman]

Relational databases are different from spreadsheets so you need to check your spreadsheet hat at the door.

Your data entry form will not look like a matrix. It will look like a list. If you want reports that look like a matrix, that is a different issue and quite doable even though the data is stored as a list.

Calculations are done either in queries, code, macros, or as the ControlSource property of a control. In this case, the latter might work for you.

= IIf(TimeToPerformTask = True, PeopleCount * 150, 0)

Fields are referred to by their names not by their position. If TimeToPerformTask is really a text field that contains "Yes" or "No", then use "Yes" rather than True in the IIf().

 

[mari_hitz]

Hi Pat,

Thanks for your help and support. Actually I was looking to do a form, because I am going to need that several people can enter the specific data for each task, so I was looking for a entry data form. I thought that could be done. Maybe, like you have said I should leave the excel mind behind and try to achieve something similar with access.

Anyway, can I please ask if I prepare a beta version of the database to ask for your help and tell me what's wrong or how can I achieve that in a better way?

Thanks in advance and I am really sorry if I have made some mistake

 

[Pat Hartman]

This is a learning experience for all of us. Not a week goes by that I don't learn something new about Access.

Start by defining your tables and post the definitions. Don't rush into building forms until your tables are solid.

 

[namliam]

This is a learning experience for all of us. Not a week goes by that I don't learn something new about Access.

Start by defining your tables and post the definitions. Don't rush into building forms until your tables are solid.

QFT, 100% facts