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!