Basic user of Access / Calculating Excel data set

[Galio83]

Hello everyone,

I have relatively simple problem, but I am just a basic user of Access. I need to calculate sums based on several criteria. My database is one sheet in Excel, it is divided into few columns. (year, place, partner, amount) All what I need is to calculate sum of all amounts for a specific year at one specific place for all the partners. In addition one column denotes whether it should be added to or subtracted from a final amount. So I need to place a condition, whether it is plus or minus based description in a single word (sold/bought). I will appreciate any help.
Thank you in advance.

[ajetrumpet]

have you considered just doing this in excel? from what I read, I would think that a combination of vlookup, sumif, and/or a sort of distinct values in the sheet would be fine.

you could use excel's autofill handle to your advantage as well. write a cell formula for one value on top of the sheet, and drag the handle down for as many distinct values as there are. Just don't use any static symbols ($ signs) in the formulas where you have horizontal cell references.

[Galio83]

Thank you for answer. I am doing that work also in Excel, I used some complex formulas (SUMIF, SUMIFS, etc.), but it is too awkward. The database has more than 200 000 rows, so I can get lost very easily. Moreover there are another parameters, which could be used in further analysis. So I would like to make a solid basis now, because it is probable that I will get more tasks in future. I have already passed some tutorials about Access, but did not find some practical advices.

[ajetrumpet]

All what I need is to calculate sum of all amounts for a specific year at one specific place for all the partners.

well this is a very rare statement, because it goes hand in hand with the built in rules of sql statements. you can meet this requirement completely by writing one query:

select year, place, partner, sum(amount) from table

group by year, place, partner

and it goes on from there. you can do additional calcs off of that even, if you need to.

[Galio83]

Thank you for answer. That is just what I needed. Now I am trying to do a regional analysis. I made a new table which includes all the places sorting them into some regions (I chose simply 1,2,3 etc.). I presume its 1:N relation, but I have no idea how to make it. I also want to do a new flat file, which will include a regional info. Then I will make a simple query choosing particular region.
Btw. is it possible to compute easily a turnover index? I presume it will be in similar following form [+( when bought) * bought amount - (when sold) * sold amount]/[bought amount+sold amount]