selecting fields from a subquery to be included in the main query

[Salwa]

Hi

How do I get the subquery T1.CODE value to be displayed as part of the main query?

Thanks heaps

SELECT CopyTIMEENTRY.TCT, tblPayrollOfficers.TCTNAME, CopyTIMEENTRY.EMP_ID, CopyTIMEENTRY.NAME, CopyTIMEENTRY.TRANSDATE, CopyTIMEENTRY.CODE
FROM tblPayrollOfficers INNER JOIN CopyTIMEENTRY ON tblPayrollOfficers.PO = CopyTIMEENTRY.TCT
WHERE (((CopyTIMEENTRY.CODE)="a654" or CopyTIMEENTRY.CODE ="a655") AND ((Exists (SELECT T1.CODE AS CODE1 FROM CopyTIMEENTRY as T1 WHERE CopyTIMEENTRY.EMP_ID = T1.EMP_ID AND CopyTIMEENTRY.TRANSDATE = T1.TRANSDATE AND (T1.CODE = "A630" or T1.CODE = "A636" )))=True))
ORDER BY CopyTIMEENTRY.TCT, CopyTIMEENTRY.NAME, CopyTIMEENTRY.TRANSDATE;

 

[theDBguy]

Isn't it already included as CopyTIMEENTRY.CODE?

 

[Salwa]

no, it is a different value to that of the one in the main query. the Code value in the main query is either A654 or A655 and the code value in the subquery is either A630 or A636.

 

[theDBguy]

I see. What happens if you add the subquery as calculated column in the main query? Just thinking out loud...

 

[Salwa]

I am BA not a coder and I have limited experience with MS Access. So unsure how to do what you are saying.

 

[theDBguy]

I am BA not a coder and I have limited experience with MS Access. So unsure how to do what you are saying.

How many records do you get when running that query?

 

[theDBguy]

Just as a test, what do you get with this query?

SELECT CopyTIMEENTRY.TCT, tblPayrollOfficers.TCTNAME, CopyTIMEENTRY.EMP_ID, CopyTIMEENTRY.NAME, CopyTIMEENTRY.TRANSDATE, CopyTIMEENTRY.CODE, (SELECT A.CODE FROM CopyTIMEENTRY AS A WHERE A.EMP_ID=CopyTIMEENTRY.EMP_ID AND A.TRANSDATE=CopyTIMEENTRY.TRANSDATE) AS CODE1
FROM tblPayrollOfficers INNER JOIN CopyTIMEENTRY ON tblPayrollOfficers.PO = CopyTIMEENTRY.TCT
WHERE (((CopyTIMEENTRY.CODE)="a654" or CopyTIMEENTRY.CODE ="a655") AND ((Exists (SELECT T1.CODE AS CODE1 FROM CopyTIMEENTRY as T1 WHERE CopyTIMEENTRY.EMP_ID = T1.EMP_ID AND CopyTIMEENTRY.TRANSDATE = T1.TRANSDATE AND (T1.CODE = "A630" or T1.CODE = "A636" )))=True))
ORDER BY CopyTIMEENTRY.TCT, CopyTIMEENTRY.NAME, CopyTIMEENTRY.TRANSDATE;

 

[Salwa]

at the moment 1, as I have doctored the data

 

[theDBguy]

at the moment 1, as I have doctored the data

Thanks. I am hoping you will still get one with the above modified query, but you might get more, if you don't get an error instead.

 

[Salwa]

i get the following message with your modified query.

At most one record can be returned by this subquery.​

 

[theDBguy]

i get the following message with your modified query.

At most one record can be returned by this subquery.​

Right. That tells me you sort of have a many-to-many matches between records with code of a654 or a655 and those with code a630 or a636.

Try adding DISTINCT in the calculated query I gave you.

 

[Pat Hartman]

Have you tried a left join without the subquery?

 

[ebs17]

From my point of view, the subquery of the EXISTS condition can be moved to the FROM part. But that also depends somewhat on the specific data situation. This means you can also use their fields in the main query.
It then looks something like this:

SELECT
   CT.TCT,
   tPO.TCTNAME,
   CT.EMP_ID,
   CT.NAME,
   CT.TRANSDATE,
   CT.CODE,
   T1.CODE AS XCODE
FROM
   tblPayrollOfficers AS tPO
      INNER JOIN
         (CopyTIMEENTRY AS CT
            INNER JOIN
               (
                  SELECT
                     CODE,
                     EMP_ID,
                     TRANSDATE
                  FROM
                     CopyTIMEENTRY
                  WHERE
                     CODE IN("A630", "A636")
               ) AS T1
               ON CT.EMP_ID = T1.EMP_ID
                  AND
               CT.TRANSDATE = T1.TRANSDATE
         )
      ON tPOs.PO = CT.TCT
WHERE
   CT.CODE IN("a654", "a655")
ORDER BY
   CT.TCT,
   CT.NAME,
   CT.TRANSDATE

 

[spaLOGICng]

Hi

How do I get the subquery T1.CODE value to be displayed as part of the main query?

Thanks heaps

SELECT CopyTIMEENTRY.TCT, tblPayrollOfficers.TCTNAME, CopyTIMEENTRY.EMP_ID, CopyTIMEENTRY.NAME, CopyTIMEENTRY.TRANSDATE, CopyTIMEENTRY.CODE
FROM tblPayrollOfficers INNER JOIN CopyTIMEENTRY ON tblPayrollOfficers.PO = CopyTIMEENTRY.TCT
WHERE (((CopyTIMEENTRY.CODE)="a654" or CopyTIMEENTRY.CODE ="a655") AND ((Exists (SELECT T1.CODE AS CODE1 FROM CopyTIMEENTRY as T1 WHERE CopyTIMEENTRY.EMP_ID = T1.EMP_ID AND CopyTIMEENTRY.TRANSDATE = T1.TRANSDATE AND (T1.CODE = "A630" or T1.CODE = "A636" )))=True))
ORDER BY CopyTIMEENTRY.TCT, CopyTIMEENTRY.NAME, CopyTIMEENTRY.TRANSDATE;

Do not use a Sub Query in the Where Clause. Add the [CopyTimeEntry] into your query with the proper joins and then you can refer to any Column within that table. Joins work very similar to where clauses for the purpose of limiting recordsets.