Error 3070 In Aggregate Functions When A Union Query Is Involved., Access 2016
Jan 10 2017, 02:59 PM
Joined: 18-January 16
I was trying to use the DMax function to get a value from a query but got the dreaded error window with the message "The Access database engine doesn't recognize 'C1' as a valid field name or expression".
C1 is a table alias used within a nested UNION query that goes like this:
(SELECT C1.AssayID, Replicate1 FROM CalibrationCurves AS C1... UNION ALL SELECT C2.AssayID, Replicate2 FROM CalibrationCurves AS C2 ... UNION ALL SELECT C3.AssayID, Replicate3 FROM CalibrationCurves AS C3) AS DataPairs INNER JOIN...
Query displays well and it is even the recordsource of a Report, it is only the use of DLookup, Dmin and other aggregate functions that cause this error. I thought that for "querying the query" purposes all it mattered were the final field names and everything under an alias or within a nested query was irrelevant, but apparently it matters. I tried to create another querydef for aggregates based on this query and again, it runs fine and the datasheet display the appropiate yet also causes the same error when trying to perform a D function.
Is there a way to get this functions going or is it better to circunvent the problem by some other mean (I can imagine creating a recordset and navigate manually)?
Jan 10 2017, 03:07 PM
Joined: 23-May 05
From: Bethesda, MD USA
So what did you write for the DMax function in the Query?
C1 is a table alias
EDIT: Javert, I've never heard of a making an alias for Table. When I try to do that in a Union Query, I cannot get the Union Query to run.
This post has been edited by RAZMaddaz: Jan 10 2017, 03:50 PM
Jan 12 2017, 02:04 PM
Joined: 18-January 16
Let's say CurveData is the name of the QueryDef. The function I was trying was:
DMax("DetectionLimit", "CurveData", "AssayID = 3")
Then I got the error 3070 message arguing about the C1 alias.
I gave up and created a Recordset with an aggregate query as the source filtered on the AssayID value so I could get those Max values. A little more cumbersome but does the works. Anyone that can give an insight on how the D functions work is welcome. For the moment, all I can advice is "Beware using D functions on UNION queries".
|Search Top Lo-Fi||28th March 2017 - 06:34 PM|