Perform a calculation in a query

[shar164]

I can't figure out how to perform a calcuation in a query and have the result output for use in a chart within a report. Forgive me I'm a newbie

[cpopham]

Okay, first things first. What type of calculation are you attempting to do in the query? Are you calculating across multiple fields? Please provide a little more information about the calculation and the table structure if any the calculated fields are based upon.

Chester

[jbennet]

i just did this for my coursework

in the Field: section under the queries design view you can tyupe things like this.

GPPerCentre: [num_candidates]*[payment_eachscript]

(where gppercentre is what the field will be called, and num_candidates and payment_eachscript are existing firlds in the query)

[cpopham]

You can also use queries to calculate fields from several tables, perform regular calculations, etc. You can also perform calculations inside a report based on grouping and that may be easier, but I need to know a little more about what the calculation is and the underlying table structure.

Hey this helps me too - thanks.

Except a little difference for me: I'm wanting a calculation using a decimal constant as follows:
HomePremium: [HomeSum]*[0.00119371]

What I'm trying to do is auto calculate a house insurance premium based on the user input of a Sum Insured called HouseSum.

Eg: Enter $300,000 for HouseSum and the autocalculation in the query multiplies this by a constant (which I have chosen/invented for simplicity) giving the answer: $358.13

But MS Access wont accept my constant. It will multiply two fields easy enough, but not [field] * my [constant].

Where am I going wrong?
Cheers

[HI2Japan]

Is your field called HomeSum or HouseSum? You have it written both ways in your question. Try it without putting [ or ] around your constant.

Is your field called HomeSum or HouseSum? You have it written both ways in your question. Try it without putting [ or ] around your constant.

Thanks for your reply. Yes it should be HomeSum not HouseSum. But that wasnt my problem. I tried it without [] around my constant, but I still got a "syntax error in query expression 'tblHomeProp.0.011937*[HomeSum]' "

I have chosen (in the "table" field located under the expression) the table that contains the field "HomeSum". I assume I need this. This causes the syntax error. When I leave the "table" field blank then there is no syntax error. Whats the story?

[HI2Japan]

Check out this little sample I just made. Is this what you are trying to do?

Also, if you are creating the expression, then the table field should be left blank. The query will not add this data to your table nor is the new field you are creating in your table. It is basically just a display only look at it so you do not need to put in anything in the table field.

Thanks so much for your help - spot on!
I have shifted house this week so sorry for delay in replying... See ya!