By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

Calculations within a Query - Struggling!!!!

P: 49
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.SumOfGoals / query1.SumOfApperances
but this doesn't work, can anyone help!!!!
Sep 26 '06 #1
Share this Question
Share on Google+
17 Replies


PEB
Expert 100+
P: 1,418
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.SumOfGoals / query1.SumOfApperances
but this doesn't work, can anyone help!!!!
Sep 26 '06 #2

P: 49
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.SumOfGoals/query1.SumOfApperances

can you explain a little further, please
Sep 26 '06 #3

PEB
Expert 100+
P: 1,418
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!

:)
Sep 26 '06 #4

P: 49
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?
Sep 26 '06 #5

PEB
Expert 100+
P: 1,418
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!

:)
Sep 26 '06 #6

P: 49
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_con: [SumOfTotal_mins_Played]/[SumOfGoals_conceded]

however is some case the maths is wrong i.e total_mins_played = 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
Sep 26 '06 #7

PEB
Expert 100+
P: 1,418
PEB
So

[SumOfTotal_mins_Played]/IIF([SumOfGoals_conceded]=0,1,[SumOfGoals_conceded])

Best regards
:)
Sep 26 '06 #8

P: 49
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
Sep 26 '06 #9

PEB
Expert 100+
P: 1,418
PEB
So

Something like this have to work :

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

:)
Sep 26 '06 #10

P: 49
Hi i have got it to work using
age: (0-(DateDiff("m",Now(),"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
Sep 26 '06 #11

PEB
Expert 100+
P: 1,418
PEB
Try this!

Mid(Format(Now()-Cvdate("01/01/1980")+1,"yyyy/mm/dd"),3,len(Format(Now()-Cvdate("01/01/1980")+1,"yyyy/mm/dd")))
Sep 26 '06 #12

P: 49
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??
Sep 26 '06 #13

PEB
Expert 100+
P: 1,418
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!
Sep 26 '06 #14

P: 49
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 ##.## ?
Sep 26 '06 #15

PEB
Expert 100+
P: 1,418
PEB
Change it to Standart number

And set decimal places = 2
Sep 26 '06 #16

P: 49
just going back to the datediff calculation, is there any way to work out a persons age and display it in years and days???
have done this
age: Format((0-(DateDiff("m",Now(),"30/7/1974"))/12),"#.##")

and this give me a number 32.16667 anyway of getting it to say 32years 65days???
Sep 26 '06 #17

PEB
Expert 100+
P: 1,418
PEB
Have you tried
Mid(Format(Now()-Cvdate("01/01/1980")+1,"yyyy/mm/dd"),3,len(Format(Now()-Cvdate("01/01/1980")+1,"yyyy/mm/dd")))

Yeah this displaies it as 36 years/ 5 months / 4 days

Only with query expression it seems a bit difficult...
You need a function or a sub in a module to do years and days only!

:)
Sep 26 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.