Expression Error

[Timothy Andrepont]

I cannot figure out why I get "query does not include specified expression" See Attached CalculationError.PDF and attached zip database Query: Qry01BSTo#.

This is just the first step in a multi calculation endeavor.

Any suggest about modify this data base to true relational data base will also be welcomed. Working data is found only in tblSite, tblTotal, tblWeight, TCoating. Remainder of tables are first effort toward relational conversion.

Look forward to hearing back.

Timothy

 

[CJ_London]

You cannot just 'sum' without grouping.

I can't see the full query but if you group by that should solve the problem

 

[CJ_London]

just opened your db

As I said in the previous post, you need to group by (the totals button on the ribbon) and remove the sum() from BS_Count and change the group by to sum

You also appear to have some typos in your field names

 

[Timothy Andrepont]

CJ,

Thanks for looking into may issue.

I have accomplished what I understood you suggested. I guess I went wrong somewhere. I am still getting the same error.

I have attached a snap shoot showing the error and the revised database. Would let me know what I am still doing wrong?

Timothy

 

[CJ_London]

What you are doing wrong is to calculate a value and then trying to use the name of the calculated value in another calculation.

This works if you are not grouping but not if you are grouping, so you need to replace the calculated value name with the actual calculation - see attached.

I've done this as an example, but you will need to look for all instances and replace accordingly. But before doing so, read the rest of this post.

I also note you have fields with brackets and commas in their name - this is a bad idea and can result in unexpected results. I recommend you rename.

Note also I have changed all but the last column to group by - access may be changing it to expression because of the above error(s).

I'm also not sure why you are using this anyway

BS_OverAllCondToNum: IIf([BS_OverAllCond]>0,[BS_OverAllCond],0)

Since the default value is 0 and I understand the table is filled using sql (rather than manual input) there will always be a value.

If you are trying to protect against nulls you should use

BS_OverAllCondToNum: nz([BS_OverAllCond])

I note you have both BS_OverAllCondToNum and [BS_OverAllCond] reported in your query - I can't see why you need to do this unless it is just to check your calculation is working.

 

[Timothy Andrepont]

CJ,

Thanks.

It is most helpful to understand that I need to use the actual calculation in the query as apposed to its name. Since the first calculation was only the first part of the actual calculation I thought I needed and has not other use, I could delete these calculations. With being the case, does grouping seem to be the best way accomplish the overall calculations?

I did not realize what NZ can do. Thanks. I will try to make this process simpler using NZ.

I do my best to only use alpha, numbers, and _ in my naming. Access placed brackets around the names, and the only commas I thought I had placed were in calculations. I plan to be more on my guard.

I will work on your suggestions and look forward to hearing back from you.

Thanks.

Timothy

 

[CJ_London]

Grouping is probably the easiest way to go, the other alternative is to use what is called a subquery.

This works along the following lines (you should just be able to paste this into the sql screen and then go back to the designer to see what it 'looks like'.
The Subquery is the bit in red and the bit in red italics is the criteria - but I'm not sure what your criteria actually is requried to be so this may need to be amended.

SELECT DISTINCT Site_ID, [I][COLOR=black]MS_PS_ID[/COLOR][/I], [COLOR=red](SELECT Count(*) FROM tblTotal AS Tmp WHERE [I]MS_PS_ID=tblTotal.MS_PS_ID[/I][/COLOR][COLOR=red]) AS BS_Count[/COLOR] 
FROM tblSite INNER JOIN tblTotal ON tblSite.SIte_PS_ID = tblTotal.MS_PS_ID

The important bit to remember is the 'AS Tmp' and in the WHERE part, the first MS_PS_ID comes from the 'tmp' table and the second part refers to the value in the main query.

If you want to go down this route, give it a try and adapt as required.

I've just realised that you are bringing through Site_PS_ID and it is a 1 to 1 relationship with tblTotal so your count is not a count, it is an addition so it may be you do not need to group or use select queries!

it would simply be BS_Count: nz([BS_OverAllCond])>0+nz([BS_Safety])>0

 

[Timothy Andrepont]

JC,
Thanks so much for your continued help with my issues.
I realize I should have given more of the whole picture. While the scope of this current effort is simply to obtain an average of the BS values >0. Zero/null fields are not to influence the outcome. This calculation will then be carried over to remaining 10 categories. Once this is accomplished the next series of calculation take place. Here each result will be multiplied by a weight factor shown in tblWeight. And more -- I have attached an excel spread sheet showing the complete calculations.
I have tried to the Nz function BS_Count: nz([BS_OverAllCond])>0+nz([BS_Safety])>0 but all I get is zero.
I have gotten BS_Count: IIf([BS_OverAllCondToNum]>0,1,0)+IIf([BS_SafetyToNum]>0,1,0)+IIf([BS_LayoutToNum]>0,1,0). This is shown in Qry01BS_To#. Do you see any reason not to use it?
My next issue is bring into the calculation the category weigh listed in tblWeight. tblWeight is in a 1 to 1 relationship with each Category such as BS, but is not related to PS_ID. Actually, each PS_ID will most of the weights.
As additional questions come up should I start a new Thread?
Again thanks for your help.
Timothy