Costbooks using Forms with Calculations

[hulaman]

Hello All,

I am new at using Access database and prefer using macros rather than VBA. That being said here is what the company wants me to do.

Figure the costs of making the products, materials only, no labor, no overhead.
There are multiple products with multiple pieces (about 5-10) and each come in multiple (25) sizes.

So far I have a form with 6 cascading combo boxes that pull data from tables based on the Forms!

![cboexample] criteria. At the moment, only one is based on the results of the previous box.

What I want to do is use calculations in conjunction with these criteria. For example, ([price]*[measurement]+ variable)*([width measurement]*[height measurement])*2/12

Is is best to separate the expressions, put them in a subform and then somehow total the subform values from those calculations?

Can you pull these values and mix them with expressions to end with a report on the cost? Or should I be using another application? I thought that Access is capable, is it not?

Where do you folks learn the advanced aspects of Access? I've read Dummies, Sams and WROX and still there is so much more. Oh yea, the help file and Access Bible. Boy, it's overwhelming...

Thanks in advance for any guidance.
Chris

 

[Ron_dK]

I would suggest to limit the use of macro's as much as possible and use VBA.

With reference to the calculations , I would use queries to calculate the
values ( e.g. Calc1: [price]*[measurement]+ variable*[width measurement]*[height measurement]*2/12 ), make a form that includes the query (calculation) fields and do a sum in a text box on that form.

Hope this helps.

 

[neileg]

I agree with rak. By doing the calculations in the query, the only VBA code you will need is the total on the form and that's easy!

On learning Access, no book I've found really helps. Usually they tell you how to do the easy bits and gloss over the bits you really want. Looking at other people's databases and reading this forum and similar ones are the best way to go.

 

[hulaman]

Thanks, I will try that and thanks for the advice on learning more about Access. It seems as an adult learning, it's On the Job Training OJT.

 

[jfgambit]

Here is a really quick version of how to calculate a running total...

I threw this together in a few minutes so don't expect anything fancy...should get you started in the direction you need.

HTH