Converting to access from Excel

[Alexpi]

Hello,



I am trying to figure out a way to convert my excel database to access. It is a sales revenue model that is just getting too big for excel. I import accounting data using MS query to excel and use vlookup/array formulas to manipulate the data in such a form that I can use pivot tables/pivot charts for presentations.

The raw data is easy enough for me to query in access correctly but I am having trouble with a few fields that I need in my query. In excel I have different tabs that are all combined onto one worksheet. The combined result give me all the data I need for a pivot table. I use fields such as invoice date, customer name, invoice number, item name, qty, amount, region, channel, market, and submarket. Once these fields are brought in to the combined worksheet I have calculated fields for Target, sum of target, expected, and sum of expected. The target and expected nubmers are calculated by a vlookup. It looks at the date of the invoice and looks for the item name and returns the target for that particular date. Similar for expected number.

The reason for the 'sum of...' field is for the pivot table. If I just have the target or expected when I sum this field it is incorrect(it is summing the target number x the qty of item sold) so the 'sum of...' field totals the qty for the item and divides it by the total targets so when this number is summed it is equal to the target for that particular month. The sum of expected is a similar calculation using the expected number.

SO...with all that being said I would like my query to pull this data correctly but I am not too familiar with getting a calculated field in an access query to work this way. Is there a way to convert excel formula's to work in excel? I mainly use if/then, sumif, countif, sumproduct, and similar formula's.

Pleas let me know if you have any ideas or can point me in the right direction.

Thanks for your help!

[alansidman]

There are some similar formulae in Access that work the same as in Excel, however, they may have slightly different names. Here is a good resource of the Access formulae and descriptions of the syntax.

http://www.techonthenet.com/access/f...ndex_alpha.php

This may also be helpful

http://www.techonthenet.com/access/queries/index.php

Alan

[Alexpi]

Thanks for the reply, Alan. Those are definitely very useful links for me!

I think the biggest roadblock I have hit is figuring out how to use a range in a query. For example, in excel my sum of target number is calculated by counting the qty of units sold in a month and dividing that by the target number for that month. Is it possible in a query to total an item by month? If I could do this I think I could figure out how to get the rest of the calculations I need.

Thanks for your help!

Alex

[alansidman]

The simple answer is yes. You would build an aggregate query and group on the months and sum on the quantities.

Here is a simple tutorial on Aggregate queries.

http://www.youtube.com/watch?v=60o0hRUfBHs

Alan