Calculated field in a query using Where? (1 Viewer)

Jul

Registered User.
Local time
Today, 06:32
Joined
May 10, 2006
Messages
64

I have a query that I am trying to build, that joins 2 other queries together. There is one field named "Value" and the actual values in that field are populated based on a field named "Name". So in the Name field, I want to pull "Sheet Length*", "Sheet Width" and "Sheet Count" and have the Value for those fields populate in the result, which, I have it working just by setting the criteria. The problem I am running into, is I want to create 3 calculated fields in this query to separate those values, so one calculated field would be the average based on the name "Sheet Length*", one calculated field would be the average based on the name "Sheet Width" and one calculated field would be the average based on the name "Sheet Count". I am not sure what I am doing wrong, but I keep getting a syntax error. Any help would be so greatly appreciated! Thank you!
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611

Jul

Registered User.
Local time
Today, 06:32
Joined
May 10, 2006
Messages
64
I agree, I am not happy with the field names, unfortunately, we hired a company to build this database for us and those are the names the gave fields and I am not allowed to change them. So I now have to figure out how to make things work.

Here is one of the ways I was trying to calculate the Avg Sheet Count:
AvgSheetCount: Select AVG(Value) from [qryQAMeasures] WHERE [Name]="Sheet Length*"

qryQAMeasures is one of the 2 queries in this new query I am building called "qryAvgDimensionsByLineDateAndNEWESTPFS
 

Micron

AWF VIP
Local time
Today, 06:32
Joined
Oct 20, 2018
Messages
3,476
Not sure that just the field expression will help much. I don't think that's what was meant by 'post the sql'. Apologies if I'm out of line commenting on that. Mainly wanted to say that if you must use reserved words, try encapsulating them in square brackets ([Value]) as you have done elsewhere. Value would be much more problematic than some other reserved words.
Also like to suggest that if you attempt to run your query from sql view, the offending part will often get hilighted. That might help narrow down where the problem is.
 

Jul

Registered User.
Local time
Today, 06:32
Joined
May 10, 2006
Messages
64
Here is the sql for my query:
SELECT qryQAMeasures.ActualDateUTC, qryQAMeasures.LineID, qryQAMeasures.ProductFamilyCode, qryNEWESTProductFamilyStandards.SheetMin, qryNEWESTProductFamilyStandards.WidthMin, qryNEWESTProductFamilyStandards.LengthMin, qryQAMeasures.Name, qryQAMeasures.Value, qryNEWESTProductFamilyStandards.MaxOfMaxOfEffectiveDate, (Select AVG([Value]) from [qryQAMeasures] WHERE [Name]="Sheet Length*") AS AvgSheetCount
FROM qryQAMeasures INNER JOIN qryNEWESTProductFamilyStandards ON (qryQAMeasures.LineID = qryNEWESTProductFamilyStandards.LineID) AND (qryQAMeasures.ProductFamilyCode = qryNEWESTProductFamilyStandards.ProductFamilyCode)
GROUP BY qryQAMeasures.ActualDateUTC, qryQAMeasures.LineID, qryQAMeasures.ProductFamilyCode, qryNEWESTProductFamilyStandards.SheetMin, qryNEWESTProductFamilyStandards.WidthMin, qryNEWESTProductFamilyStandards.LengthMin, qryQAMeasures.Name, qryQAMeasures.Value, qryNEWESTProductFamilyStandards.MaxOfMaxOfEffectiveDate, (Select AVG([Value]) from [qryQAMeasures] WHERE [Name]="Sheet Length*")
HAVING (((qryQAMeasures.ActualDateUTC)=#1/8/2020#) AND ((qryQAMeasures.LineID)="324") AND ((qryQAMeasures.Name)="Sheet Count" Or (qryQAMeasures.Name)="Sheet Width" Or (qryQAMeasures.Name) Like "Sheet Length*"));
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
First and foremost, debugging 101--->divide and conquer. Every SELECT is a query, which means, you are able test the sub-queries by themselves to see if they are the ones causing the issue. Paste them into their own query window and see fi they run, if they do, the main query is the issue, if not you've isolated the problem.

Code:
Select AVG([Value]) from [qryQAMeasures] WHERE [Name]="Sheet Length*"


Most likely you need to change the = to LIKE. = means match exactly as I have typed it; * is a wildcard that allows for inexact matches. This is what you did in the HAVING clause.

It is also possible that [Value] is not a numeric field. You can't average text.


Lastly, HAVING is for applying criteria on aggregate data. You apply criteria to aggregate functions here (SUM(), MAX(), COUNT(), etc.). When you want to apply criteria on an individual record basis you use the WHERE clause. That means all your criteria should exist in a WHERE not a HAVING.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:32
Joined
Jan 23, 2006
Messages
15,361
Jul and others,

I have reformatted the query SQL using Poor SQL -- it didn't flag any syntax issue.

Code:
SELECT qryQAMeasures.ActualDateUTC
	,qryQAMeasures.LineID
	,qryQAMeasures.ProductFamilyCode
	,qryNEWESTProductFamilyStandards.SheetMin
	,qryNEWESTProductFamilyStandards.WidthMin
	,qryNEWESTProductFamilyStandards.LengthMin
	,qryQAMeasures.Name
	,qryQAMeasures.Value
	,qryNEWESTProductFamilyStandards.MaxOfMaxOfEffectiv eDate
	,(
		SELECT AVG([Value])
		FROM [qryQAMeasures]
		WHERE [Name] = "Sheet Length*"
		) AS AvgSheetCount
FROM qryQAMeasures
INNER JOIN qryNEWESTProductFamilyStandards
	ON (qryQAMeasures.LineID = qryNEWESTProductFamilyStandards.LineID)
		AND (qryQAMeasures.ProductFamilyCode = qryNEWESTProductFamilyStandards.ProductFamilyCode)
GROUP BY qryQAMeasures.ActualDateUTC
	,qryQAMeasures.LineID
	,qryQAMeasures.ProductFamilyCode
	,qryNEWESTProductFamilyStandards.SheetMin
	,qryNEWESTProductFamilyStandards.WidthMin
	,qryNEWESTProductFamilyStandards.LengthMin
	,qryQAMeasures.Name
	,qryQAMeasures.Value
	,qryNEWESTProductFamilyStandards.MaxOfMaxOfEffectiv eDate
	,(
		SELECT AVG([Value])
		FROM [qryQAMeasures]
		WHERE [Name] = "Sheet Length*"
		)
HAVING (
		((qryQAMeasures.ActualDateUTC) = #1 / 8 / 2020 #)
		AND ((qryQAMeasures.LineID) = "324")
		AND (
			(qryQAMeasures.Name) = "Sheet Count"
			OR (qryQAMeasures.Name) = "Sheet Width"
			OR (qryQAMeasures.Name) LIKE "Sheet Length*"
			)
		);
 

Users who are viewing this thread

Top Bottom