Struggling with SubQuery

[BJZeak]

using 2 tables:
Table 1 is a list of unique ID's as CID
Table 2 is a list of Home and Mailing Addresses linked via CID and aType = HOME | MAIL

All CID's must have a Home Address but can optionally have a Mail Address ... records aren't allowed to be deleted or changed ... any changes will result in new records being generated ... assigned ANID (Autonumber Primary Index) to table 2 and HANID which should always match the CID's current home address

Table 1
CID 
 01
 02
 03

Table 2
ANID  HANID CID aType Address
   1      1  01 Home  123 Abc St Somewhere Land
   2      2  02 Mail  PO Box 124 Somewhere Land
   3      3  03 Home  452 Xyz Ave Somewhere Else Land
   4      3  03 Mail  PO Box 125 Somewhere Else Land
   5      5  01 Home  245 Jkl Ave Some Other Land

The subQuery below has an issue with the value of [highlight #EF2929]T1.cid[/highlight] not being available for the where clauses in subqueries TMX1 and TMX2 ... the query will run if I remove these subqueries but results in too many rows.

Select T1.cid, Home.aType, Home,Address, Mail.aType, Mail.Address
From ((Table1 As T1
Left Join [
   Select Table2.cid, Table2.aType, Table2.Address 
   From Table2
   Where Table2.aType = "Home" 
         And 
         Table2.hanid = (Select Max(Table2.hanid)
                         From Table2 As TMX1
                         Where TMX1.cid = [highlight #EF2929]T1.cid[/highlight])]. As Home
ON T1.cid = Home.cid)
Left Join [
   Select Table2.cid, Table2.aType, Table2.Address 
   From Table2
   Where Table2.aType = "Mail" 
         And 
         Table2.hanid = (Select Max(Table2.hanid)
                         From Table2 As TMX2
                         Where TMX2.cid = [highlight #EF2929]T1.cid[/highlight])]. As Mail
ON T1.cid = Mail.cid)

 

[BJZeak]

As I am tired of beating my head against the wall in attempts to resolve why these MAX sub-queries fail to work in ACCESS, I basically just replaced them with a function that returns the required MAX value ... the function uses the same query statement just isolated from both the ACCESS Pre Parsing and Runtime Logic ... This method works but is probably not as efficient as it might be if the Sub-queries actually worked.

Public Function fnMaxHANID(sCid As String) As Variant
   Dim dbo As DAO.Database
   Dim qso As DAO.Recordset
   Dim sSQL As String
 
   sSQL = "Select Max([qx].[hanid]) AS hanid " _
        & "From [TABLE2] As Q " _
        & "Where [q].[cid] = '" & sCid & "';"
 
   Set dbo = CurrentDb
   Set qso = dbo.OpenRecordset(sSQL)
   
   If qso.RecordCount > 0 Then
      qso.MoveFirst
      fnMaxHANID = qso![hanid]
   Else
      fnMaxHANID = ""
   End If

   Set qso = Nothing
   Set dbo = Nothing
   
End Function

The final query then becomes:

SELECT 
   T1.cid, Home.aType, Home.Address, Mail.aType, Mail.Address
FROM (Table1 AS T1 
LEFT JOIN [
   Select 
      Table2.cid, table2.hanid, Table2.aType, Table2.Address 
   From Table2
   Where Table2.aType = "Home"
    and table2.hanid = fnMaxHANID(table2.cid)
   ]. AS Home 
ON T1.cid = Home.cid) 
LEFT JOIN [
   Select 
      Table2.cid, table2.hanid, Table2.aType, Table2.Address 
   From Table2
   Where Table2.aType = "Mail" 
     and table2.hanid = fnMaxHANID(table2.cid)
   ]. AS Mail 
ON T1.cid = Mail.cid;

 

[dhookom]

It would have helped if you have provided the desired output from your query. Can we assume if there are duplicate home addresses you want to return only the one with the highest ANID value? Would the same be true with Work?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BJZeak]

The Test Data from the first post should provide only 3 rows (aType is in the test query but isn't required as type is in the heading) ... there are no other Address Types at this time so "Work" would not be a valid type ... the data is currently in transition, with the expectation that Home Addresses will eventually be filled in ... meaning this query has to work even if the Home Address is MIA which is why both HOME and MAIL sub-queries are using LEFT JOINs ... when the MAX sub-query is used in the Main Query with this minimal test data, it produces 4 rows (showing both the previous and current address for CID = 01) ... when used with the actual data, Access refuses to run the query stating there are potentially too many results for the sub-query ... using the function fnGetMaxHANID in place of the MAX sub-query works for both the test and real data.

the 3 expected rows for the data given are:

CID  Home.Address                    Mail.Address
 01  245 Jkl Ave Some Other Land 
 02                                  PO Box 124 Somewhere Land 
 03  452 Xyz Ave Somewhere Else Land PO Box 125 Somewhere Else Land

The new rules for this address table are:
- HANID is expected to be the HOME ANID (if the HOME record is missing, HANID is just the MAIL ANID only for existing data)
- The expectation going forward is that no Address records can be altered or deleted
- Any new or change of address will NOW be required to provide a HOME ADDRESS plus a MAIL ADDRESS where applicable
- if a change of address is required, NEW record(s) will be created ... because ANID is autonumber the NEW record will always be greater then the previous address entry for any CID ... the MAX function ensures that for any CID, the current ADDRESS(ES) are those matching the largest HANID for that CID ... this minor change requires no updates to older data and ensures historic reports will line up with historic data

 

[dhookom]

This worked for me:

TRANSFORM Min(Table2.Address) AS MinOfAddress
SELECT Table2.CID
FROM Table2
WHERE (((Table2.ANID) In (SELECT Max(Table2.ANID) AS MaxOfANID
FROM Table2 RIGHT JOIN Table1 ON Table2.CID = Table1.CID
WHERE table2.aType="Home"
GROUP BY Table1.CID, Table2.aType) Or (Table2.ANID) In (SELECT Max(Table2.ANID) AS MaxOfANID
FROM Table2 RIGHT JOIN Table1 ON Table2.CID = Table1.CID
WHERE table2.aType="Mail"
GROUP BY Table1.CID, Table2.aType)))
GROUP BY Table2.CID
ORDER BY Table2.CID
PIVOT Table2.aType;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BJZeak]

Thank-you for the response ... While this query provides the expected results, it is going to take me a while to wrap my head around what exactly is happening, specifically with the Transform, Pivot and In statements

I highly suspect that the max sub-query doesn't work because it isn't being executed/processed in the order I intended and or assumed. With your query, I expect the Transform, Pivot, and In statements may be providing a better definition of that order. I have searched for an easy to follow description of SQL statement order of execution which has only clouded my vision even more.

To me, understanding order of execution is important, especially with these more complex queries. In the case of using a User Function, the SQL processor obviously equates the function first, where as it isn't as clear what order the sub-sub-query would be executed in. More specifically what the shared/link values would be.

Based on 40+ years of top down programming, I could well imagine that an SQL processor would need to do at least 2 passes; first pass would parse and organize each statement in to an ordered item list THEN the second pass would execute each ordered item in top down fashion. Undoubtedly this is an oversimplification, as there is potential for processors to do optimizations (more passes) and in an event driven environment, there is the potential for doing some of the execution in separate threads (parallel processing).

Anyway interesting solution thanks again.

 

[dhookom]

I wouldn't worry about order of execution. Remove the Crosstab and change to a totals or select query to see the results. Then understand the crosstab is just a pivot.

The In () simply return the ANIDs of the most recent Home and Mail. The "First" is just a convenient aggregation since one is required. You could probably substitute Last or Min or Max.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016