Comparison - Excel Formulas in Access Queries

[Guest]

I have been told that all calc that can be made in Excel, can be done with
Queries in Acess. Is there any particualar place where i can find a reference
of how to do something in excel and its counterpart in access?

 

[Guest]

Hi dcozzi--I don't agree with that statement since I'm not sure if you can
calculate NPV, IRR, or statistical functions using queries but there is some
crossovers, such as SUM, Count, etc. You can, however, use VBA to create an
Excel instance and use the functions this way. Another option is write your
own functions to make the calculations you need.

What type of functions are you thinking of using?

 

[Guest]

Thanks for getting back to me.

The real things that ineed are text, logcial and lookup functions. All of
which i belive can be done in thru queries. I am rather schooled in excel and
am looking to transfer that info over to access. The data i use and
manipulate has become to much to do in excel.

 

[Guest]

When moving over to Access from Excel the first thing you need to realize is
that Access is designed for databases--tables, relationships, normalization,
etc.--while Excel is for spreadsheets and displaying data. The design,
construction, maintenance, etc. is totally different. Once you grasp that
concept and learn more about DBs, then it's rather easy to transition.

Where people usually trip themselves up is they want Access to look, feel,
and act like Excel. For example, newbies will want to edit tables directly,
instead of forms, using things like auto-fill which does not work in Access.

The functions you mentioned are available in Access and the easiest
reference is using the Access help file; however, don't expect too much from
the help though you will find mostly adequate. Also, use google or any of
the great sites of the MVPs or others who post here regularly. Last, try
searching the forums for specific function questions as someone else has more
than likely asked your question before.

Sorry for the long reply but I hope it helps...

 

[Rick Brandt]

I have been told that all calc that can be made in Excel, can be done with
Queries in Acess. Is there any particualar place where i can find a reference
of how to do something in excel and its counterpart in access?

The biggest difference between "spreadsheet" and "database" is that spreadsheets
are x-y grids of untyped cells that can perform calculations based on the
relative position of cells in the grid. Database tables looks like grids but
they are in fact collections of rows with identical structure and one row really
doesn't "know" anything about any other rows.

So while an Excel sheet can have a number in one cell, a text entry under that,
and a date value under that, a database table would have that entire column
defined as a particular data type and that is all that can go in it. Also
Excel can have formulas combining cells that are in the same row, in the same
column, or even something like "take this cell and multiply it by the cell that
is two rows up and four columns over". Database queries cannot do anything like
that.

Query expressions either aggregate an entire column or they can have expressions
that include field values all found in the same row. Anything beyond that is
either complicated, really slow, or impossible. Running totals for example are
easy as pie for a spreadsheet, but are rather difficult to obtain in a query and
even when accomplished are not very efficient.

Now...once you decide that you want to perform an aggregation on a column or
create a calculation that uses field values all on the same row then the actual
computations you can perform should be pretty much anything you want. Certain
mathematical and business functions might not be built in as they would be in a
spreadsheet, but the functions that are provided should be enough to get you
there. When stuck you can always create custom functions to make complex tasks
easy to accomplish in a query.

 

[Guest]

ok. Thanks for your help. I have just discoverd excels query capability and
will use that and excel for complicated calcs.