Creating A Query Expression Help

[topthebookie]

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.

[Khriskin]

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! ^_^

[topthebookie]

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! ^_^

[Scott Price]

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

[jaxjagfan]

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.

[topthebookie]

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

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];

[Khriskin]

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! ^_^

[topthebookie]

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:

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! ^_^

[Khriskin]

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:

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? :)

[topthebookie]

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:

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? :)