X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Error 3070 In Aggregate Functions When A Union Query Is Involved., Access 2016    
post Jan 10 2017, 02:59 PM

Posts: 59
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 AssayID.....


(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)?
Go to the top of the page
post Jan 10 2017, 03:07 PM

UtterAccess VIP
Posts: 9,522
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.

Go to the top of the page
post Jan 12 2017, 02:04 PM

Posts: 59
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".
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th January 2018 - 06:47 AM