459,238 Members | 1,661 Online
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

# Creating A Query Expression Help

 P: 11 I created a query that has 2 sum fields. 1st Field is the sum of orders, 2nd field is the sum of count, I need the 3rd column for my expression which I am not sure how to write. Ultimately, I want field 1 to divide by field 2, and multiply by 2 for my percentage expression in field 3. Obviously, I am learning access and this form has already been a tremendous help to me. Feb 13 '08 #1
9 Replies

 P: 20 As I understand it, you are currently have the following query created: The first returned value in the query is a SUM of the field [Orders] The second returned value in the query is a SUM of the field [Count] So the results of the query would be: [SumOfOrders] [SumOfCount] Now you want to add in a third field: ([SumOfOrders] / [SumOfCount]) * [SumOfCount] Is that right? (The reason I ask is because the calculation you wanted for the third field of the query will always be equal to [SumOfOrders].) Regardless, you should simply be able to add the expression you wish to calculate into the third field (using the field names created by the query) and set Total to Expression. Hope that helps! ^_^ Feb 13 '08 #2

 P: 11 not sure what I am doing wrong but I keep getting does not include the specified expression. As I understand it, you are currently have the following query created: The first returned value in the query is a SUM of the field [Orders] The second returned value in the query is a SUM of the field [Count] So the results of the query would be: [SumOfOrders] [SumOfCount] Now you want to add in a third field: ([SumOfOrders] / [SumOfCount]) * [SumOfCount] Is that right? (The reason I ask is because the calculation you wanted for the third field of the query will always be equal to [SumOfOrders].) Regardless, you should simply be able to add the expression you wish to calculate into the third field (using the field names created by the query) and set Total to Expression. Hope that helps! ^_^ Feb 13 '08 #3

 Expert 100+ P: 1,384 not sure what I am doing wrong but I keep getting does not include the specified expression. Please post for us the SQL statement you are currently using. We're stabbing in the dark without knowing exactly what you attempting! Open your query in design view, right click on the window bar and choose SQL view, then copy and paste here. Once you've copied the data, select the statement in the reply window, and click the # button on the top of this reply window to enclose the SQL statement in CODE tags. As an added bonus, you can manually edit the first tag to look like this: [code=sql] Thanks! Regards, Scott Feb 13 '08 #4

 Expert 100+ P: 254 not sure what I am doing wrong but I keep getting does not include the specified expression. Make sure on the 3rd one and on you specify those as "Expressions" vice "Group", "Sum", Etc. You can also put a formula Sum(tblOrders.Orders)/Count(tblOrders.Orders) in the column. Feb 13 '08 #5

 P: 11 here is another one I need help on, Ill just post the sql view. I need another field that will divide the 2 first fields, and multiply them by 2 thank you for your help Expand|Select|Wrap|Line Numbers SELECT DISTINCTROW Sum([HTR BY RANK BY TRACK].SumOfxWIN) AS [Sum Of SumOfxWIN], Sum([HTR BY RANK BY TRACK].CountOfnDIST) AS [Sum Of CountOfnDIST] FROM [HTR BY RANK BY TRACK]; Feb 13 '08 #6

 P: 20 From what I understand you have an existing query called [HTR BY RANK BY TRACK] that has two fields [SumOfxWIN] and [CountOfnDIST]. You want to sum these two fields again and then divide the sum of [SumOfxWIN] by the sum of [CountOfnDIST], correct? If so, you could write expression for the third field as: [UnnamedField] : ([Sum Of SumOfxWIN] / [Sum Of SumOfnDIST]) Or as: [UnnamedField] : (SUM([SumOfxWIN] )/ SUM([SumOfnDIST])) In Design View, you just need to make sure that the third field has Total set to 'Expression' instead of 'SUM' and then cut and paste in the expression from above. If you are getting an error that the query 'does not include the specified expression' it is most likely because one of the new field names ([Sum Of SumOfxWIN] or [Sum Of SumOfnDIST]) was not entered correctly into the calculated field. I have also found that saving the query after it is created, then running it sometimes clears up those errors. Hope that helps! ^_^ Feb 13 '08 #7

 P: 11 When I close the query and reopen it, it asks for a parameter value, ahh very frustrating but getting there, any suggestions. Here is the sql view sql code: Expand|Select|Wrap|Line Numbers SELECT DISTINCTROW Sum([HTR BY RANK BY TRACK].SumOfxWIN) AS [Sum Of SumOfxWIN], Sum([HTR BY RANK BY TRACK].CountOfnDIST) AS [Sum Of CountOfnDIST], [HTR BY RANK BY TRACK].Result AS Expr1 FROM [HTR BY RANK BY TRACK] HAVING ((([HTR BY RANK BY TRACK].[Result])=(Sum([SumOfxWIN])/Sum([SumOfnDIST])))); ************************************************** ********************************* From what I understand you have an existing query called [HTR BY RANK BY TRACK] that has two fields [SumOfxWIN] and [CountOfnDIST]. You want to sum these two fields again and then divide the sum of [SumOfxWIN] by the sum of [CountOfnDIST], correct? If so, you could write expression for the third field as: [UnnamedField] : ([Sum Of SumOfxWIN] / [Sum Of SumOfnDIST]) Or as: [UnnamedField] : (SUM([SumOfxWIN] )/ SUM([SumOfnDIST])) In Design View, you just need to make sure that the third field has Total set to 'Expression' instead of 'SUM' and then cut and paste in the expression from above. If you are getting an error that the query 'does not include the specified expression' it is most likely because one of the new field names ([Sum Of SumOfxWIN] or [Sum Of SumOfnDIST]) was not entered correctly into the calculated field. I have also found that saving the query after it is created, then running it sometimes clears up those errors. Hope that helps! ^_^ Feb 13 '08 #8

 P: 20 Ah, I think I see the problem! ^_^ See line 3? In that line you are telling the query to look for matches between field [Result] and Sum([SumOfxWIN])/Sum([CountOfnDIST]). What you want to do is set [Result] to that value, not look for a match. Right? What you need is this added to the SELECT line: Sum([SumOfxWIN])/Sum([CountOfnDIST]) AS Result So what you would end up with is: Expand|Select|Wrap|Line Numbers SELECT DISTINCTROW Sum([HTR BY RANK BY TRACK].SumOfxWIN) AS [Sum Of SumOfxWIN], Sum([HTR BY RANK BY TRACK].CountOfnDIST) AS [Sum Of CountOfnDIST], Sum([SumOfxWIN])/Sum([CountOfnDIST]) AS Result FROM [HTR BY RANK BY TRACK]; Does that help? :) Feb 13 '08 #9

 P: 11 Ohh Thank Heaven... I now have it working. You have been very helpful being that I am new to this. :) Thank You Again!!!!! Ah, I think I see the problem! ^_^ See line 3? In that line you are telling the query to look for matches between field [Result] and Sum([SumOfxWIN])/Sum([CountOfnDIST]). What you want to do is set [Result] to that value, not look for a match. Right? What you need is this added to the SELECT line: Sum([SumOfxWIN])/Sum([CountOfnDIST]) AS Result So what you would end up with is: Expand|Select|Wrap|Line Numbers SELECT DISTINCTROW Sum([HTR BY RANK BY TRACK].SumOfxWIN) AS [Sum Of SumOfxWIN], Sum([HTR BY RANK BY TRACK].CountOfnDIST) AS [Sum Of CountOfnDIST], Sum([SumOfxWIN])/Sum([CountOfnDIST]) AS Result FROM [HTR BY RANK BY TRACK]; Does that help? :) Feb 13 '08 #10