Calculations within a Query - Struggling!!!!

[Milkstr]

I have 2 tables that hold similair information, i have a query running off then to total up the sum of a couple of colums, i want to run another query that will create a colum that holds the 2 of the first query colums divided by each other if that makes sense, I thought it was as simple as e.g.
colum1: query1.SumOfGoa ls / query1.SumOfApp erances
but this doesn't work, can anyone help!!!!

[PEB]

Hi,

Try:
colum1: [query1].[SumOfGoals] / [query1].[SumOfApperances]

And use your query with sums in this new query!

:)

I have 2 tables that hold similair information, i have a query running off then to total up the sum of a couple of colums, i want to run another query that will create a colum that holds the 2 of the first query colums divided by each other if that makes sense, I thought it was as simple as e.g.
colum1: query1.SumOfGoa ls / query1.SumOfApp erances
but this doesn't work, can anyone help!!!!

[Milkstr]

Hi,

Try:
colum1: [query1].[SumOfGoals] / [query1].[SumOfApperances]

And use your query with sums in this new query!

:)

Hi
I cant seem to get it to work, it changes the springs some what too

colum1: query1.SumOfGoa ls/query1.SumOfApp erances

can you explain a little further, please

[PEB]

So can you pass in SQL mode and do Copy and Paste into the forum your SQL on which is based your query?

This is the manner to do operations with data but not aggregate data!

If your data is aggregate so use Sum([Field1])/sum([Field2])

Pls when there is a pb give the error that is displayed or the message!

And the sql to see what is the pb!

:)

[Milkstr]

sorry am a total novice with this. I thought what i was trying to do would be really easy. I dont understand the SQL stuff.

is there anychance you could talk me through a step by step way of doing this?

[PEB]

Ok
tell me:

1) The names of your tables that are in your query
2) The columns in the tables and what is their type date, number text or Yes/No
3) The columns that you want to do division
4) Have you activated any aggregate functions like Sum, Coutn or other /activation of Sigma bouton /sum for those operations/

Don't stress u this is the needed information!

:)

[Milkstr]

I've had a break through and go it working, well almost!!
just one last think to make it work as i want, i have got the calculation working
e.g.
mins_per_goal_c on: [SumOfTotal_mins _Played]/[SumOfGoals_conc eded]

however is some case the maths is wrong i.e total_mins_play ed = 90 goals_conceded = 0 the calculation brings an #error cos you can't divide by 0!
what can i do so that it the formula produces an error it just leaves the field at zero???
Thanks

[PEB]

So

[SumOfTotal_mins _Played]/IIF([SumOfGoals_conc eded]=0,1,[SumOfGoals_conc eded])

Best regards
:)

[Milkstr]

works perfectly!!! Thanks very much!!!
one last question tho, i want to have the final query work out the age of a player according to the current date, is there a simple expression i can place in the colum??? a simple expression that takes a date of birth off the cuttent date, but place it in a colum of the query? i know in excel its easy and i would just to (TODAY() - 30/07/74)/365.25 Thanks

[PEB]

So

Something like this have to work :

Datediff("yyyy" , [Birthdate], Now())

:)

[Milkstr]

Hi i have got it to work using
age: (0-(DateDiff("m",N ow(),"30/7/1974"))/12)
but this pulls up 32.121252 i wan to format it to just 32.12, how do i apply a format to the result???
Thanks

[PEB]

Try this!

Mid(Format(Now( )-Cvdate("01/01/1980")+1,"yyyy/mm/dd"),3,len(Form at(Now()-Cvdate("01/01/1980")+1,"yyyy/mm/dd")))

[Milkstr]

Thanks will give that a go, is there an easy way just to format raw numbers to a number of decimal places? where i'm dividing the sum of one colum by the sum of another??

[PEB]

Thanks will give that a go, is there an easy way just to format raw numbers to a number of decimal places? where i'm dividing the sum of one colum by the sum of another??

You do right click in the respective field in query design and choose properties

There is a way sure!

[Milkstr]

You do right click in the respective field in query design and choose properties

There is a way sure!

I'v looked at that and there is general number, but it still gives me a number with lots of decimal pionts how do i format it so i get ##.## ?