Excel vs Access

[pmarques]

I'm new to Access.
I Work in excel but the Sheets start to have many records and, sometimes it's difficult to apply the formulas.
My doubt is: It´s possible to make with the Access "everything"
that i make with Excel.
Example I apply formulas like:
=IF(ISNA(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";IF(ISBLANK(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE)))

Thanks in advance

 

[Pat Hartman]

Access can probably do whatever calculations you are doing in your spreadsheet but it doesn't support all the statistical and financial functions that Excel does. You would need to purchase a library of statistical and financial functions if you needed them. I don't work much with Excel so I don't really know what the VLookup()s are doing. What I can tell you is that Access has a DLookup() which I understand is similar in functionality.

Do not expect your conversion to a database to be straightforward. You need to change the way you think and you will most likely need to normalize your data. You will be very unhappy with Access if you just import your spreadsheet and expect it to keep working as it worked in Excel.

Relational databases do not have cells as spreadsheets do. They have rows and columns. If you want to sum a set of data, you need to provide criteria in a query to limit the records returned to only the set you want to sum. For example if you wanted to sum the amounts by department, you would order the spreadsheet by department and add totals rows at department breaks. To sum by department in Access, you would create a query similar to:
Select Department, Sum(Amt) As AmtSum
From YourTable
Group By Department;

If you wanted to limit the data to the current year:
Select Department, Sum(Amt) As AmtSum
From YourTable
Where Year(TranDate) = Year(Date())
Group By Department;

Jump right in, the water's fine

 

[pmarques]

Thanks Pat Hartman