Using Excel Functions in Query (1 Viewer)

[bmcgree1]

Hello,
So I did this is Excel and now am trying to get the same result in an Access query. So in Excel I am using a math function; a set number(60)*the value in the previous row. i.e. - 60*[Balance].
So if I have 10 rows, the first row will get the number from another field and beginning at the second row it will get the number from the previous value in another field. (lets say the 2 fields are [Total] & [Balance].
The first row would = 60*[Total] and beginning at the second row = 60*[Balance](row 1 value), third row = 60*[Balance](row 2 value), etc.

In excel it looks like 60*H18, 60*H19, 60*H20, etc. How do I do this same thing in a query?
My query already breaks up into the correct number of rows and I'm trying to get this piece to work also.

Any help is GREATLY appreciated. Thank you,

Brendan

 

[statsman]

You would do it in a similar manner except that in Access it's necessary to name the field rather than give the cell number.

Your Excel cell would read 60*H18, 60*H19, 60*H20,

If you are using the Query Wizard, go to the first blank field on the right. The new field in your Access Query would read somthing like

George:Sum([Field1]*60) + ([Field2]*60) + ([Field3]*60)

You are essentially creating a new field named George within the query that holds the calculated data.
You must precede the math functions with the name of the field you are creating that will hold the data followed by a colon, followed by the math function. In this case
George:Sum

The field names must be enclosed within square brackets
[Field1]

 

[TashaSpicer]

I am trying to apply this to my database to total items and it is not cooperating. I keep getting an error message that says I have invalid syntax and that I may have entered and operand without an operator. This is exactly what I have in the field name:

Total Arrests:Sum[Felony Arrests]+[Misdemeanor Arrests]

I have tried this several times and made sure the spelling all matched and still the same error. Any ideas what I am doing wrong??

Thanks

Tasha

 

[bmcgree1]

You can't add only one field to a sum. You're telling access to try and sum one entire field up and then add a single record on top of it.
Instead of what you have, break it up. Try this:

TotalFelony:Sum([Felony Arrests])
TotalMisdemeanor: Sum([Misdemeanor Arrests])
TotalArrests: [TotalFelony] + [TotalMisdemeanor]

So now you have 3 fields. Don't worry about extra fields in a query, it won't make a difference if you don't use them or not.

 

[statsman]

I am trying to apply this to my database to total items and it is not cooperating. I keep getting an error message that says I have invalid syntax and that I may have entered and operand without an operator. This is exactly what I have in the field name:

Total Arrests:Sum[Felony Arrests]+[Misdemeanor Arrests]

I have tried this several times and made sure the spelling all matched and still the same error. Any ideas what I am doing wrong??

Thanks

Tasha

Try adding the required round brackets
TotalArrests:Sum([Felony Arrests]+[Misdemeanor Arrests])