Totals, Cumulative, and Break-Even Help

[oregoncrete]

Hello All,

I am fairly new to Access, but has had some success thus far in creating what I need. So far I've been able to gather Total Sales from every day for multiple products into a table that looks like this:

CATEGORY:    SALES DATE:    TOTAL SALES ON THIS DATE:
Category A 01/01/2011             $100,000
Category A 01/02/2011             $90,000

I now want to be able to create a Module (I think) that calculates when my cumulative sales = the cost of inventory for that category. I would absolutely love ANY help you all could give me. For example in this situation if my inventory cost $190,000 it would tell me I broke even on 01/02/2011. Then this date needs to be posted on a table. After it has done this it needs to continue calculated the Total Sales since it has broken even.

My knowledge of VBA is quite weak and my friend knows a little, but I believe these are the steps we would need to take are as follows (excuse terrible formatting ):

1- Define Cumulative Sales, Category Value, and Sales After Breakeven:
DIM Cum_Sales=0, category_value, point_after_break=0, breakpoint_found=N
2-Open the records and choose the category:
Open Recordset. Category_Value=Category
3- For Category=Category_Value
If breakpoint_found=N
Cum_sales=cum_Sales + Total_sales
If cum_sales >= category_cost
then breakpoint_found=Y
ELSE repeat?
if breakpoint_found=Y then update breakpoint date on TABLE


Next record
repeat for next category automatically until all categories are done?

So basically:

add sales for each day till total cumulative sales >= cost
then post which day this happens into a table
then sum all days till present - cost and post sales after breakeven to a table
then repeat this for every category.

Thank you all, I'm sorry for the quality and length of this thread, but again any help is greatly appreciated!

-George

[ajetrumpet]

George,

In this guy's opinion, you won't get any answer here unless you upload a file. And even then, it's a slim chance. The reason - this is something that really should be paid for.

But the best help that I think you can hear at this point is that looking at a file or app directly is really the only practical way that someone can help you. If you don't upload it, at the very least, chances are you'll find yourself explaining structural issues and the like to someone for quite a few posts in this thread.

just an FYI for you, from an accountant's point of view. One that certainly has plenty of experience in vba...

[oregoncrete]

George,

In this guy's opinion, you won't get any answer here unless you upload a file. And even then, it's a slim chance. The reason - this is something that really should be paid for.

But the best help that I think you can hear at this point is that looking at a file or app directly is really the only practical way that someone can help you. If you don't upload it, at the very least, chances are you'll find yourself explaining structural issues and the like to someone for quite a few posts in this thread.

just an FYI for you, from an accountant's point of view. One that certainly has plenty of experience in vba...

Well thank you for your help. Could you tell me if a macro would be able to do this, or if I really do need to learn VBA code to set up an operation as complex as this?

[ajetrumpet]

just looking at it briefly George, no, a macro has no chance of doing it. Even nested macros. I can't say that 100% of course, because 2010 has introduced recordset operations into the macro builder.

But still, I don't think it has the chance to do the kind of analysis via iterating that you need to do here.