multiply a calculated field....HELP!!!!

[CHAOSinACT]

OK.
I've gotten pretty good at my database design and was already ok with my vba but my SQL was non-existant until recently, so bear with me....
i've attached screen grabs of the query design and result...

basically i have 3 totaled fields - JobNumber, TotalSpent(materials), and total JobHours... I need to create a 4th field which multiplies the TotalHours by a base salary rate... how do i do that? I've tried heaps of things, looked everywhere but nothing seems to work...

 

[Anchoress]

A couple of questions:

1. Is the number you're multiplying by static, or variable?
2. What are you doing with the result? Will you be viewing it in a form or a report, etc?

 

[CHAOSinACT]

1. Is the number you're multiplying by static, or variable?
I've been trying to get it to work with a static result - later it will look up individual pay rates from another table - but that's at a very different stage.

I'm plugging the query into a chart - i need to convert the hours total to a currency amount so it can have meaning on the chart next to materials expenditure.

2. What are you doing with the result? Will you be viewing it in a form or a report, etc?

I'm plugging the query into a chart (which is working) - i need to convert the hours total to a currency amount so it can have meaning on the chart next to materials expenditure.

thanks

 

[Anchoress]

Can't you just make a new calculated field and calculate on it? I did it to try - is there a reason why that wouldn't work for you?

 

[CHAOSinACT]

I've tried that - i've never tried to multiply the field. I would think you just add a new column called "whaterver" and put an expression like

= TotalHours *22 in the criteria? then the group by should be what? nothing works... i've seen it done for other things but never a multiply. not sure why it wouldn't work...

 

[Anchoress]

I'm using a much older version of Access: 2000. Mine looked like this:

My first calculated field was: Total: Sum([Table1]![Monthly hours])

My second was: Total Plus: ([Total]*13)

I had grouping turned on, as "Expression". Don't know if newer versions of Access even work like that.

 

[CHAOSinACT]

Got it!!!
the field had to be
TotalHoursCost: Sum(([tblCivilProjectCosts]![TotalHours]*22))

to be honest the sum seems stupid since we're multiplying, but i guess it lets you do any math? (i would have thought sum was an add only). maybe the sum is unneeded but i'm not sure. i'll check...