Re: Subselect Query Problem

Michael:

Thank you for your response.  I'm testing what you posted in the Query area
of Access (although I'm intending to implement this as SQL in VBA code using
DAO).

It looks like it works, but I made a mistake in my original post.  Namely, it
is Table B that has the first three fields as the primary key, not Table A.
Table A actually uses an autonumber primary key.

I'm not sure if this would change your construct or not.

Also, I got to thinking about the Sub_Proposal_Status.  Since this value is
set in code from forms where the user makes a selection or not, there is no
reason why there shouldn't be a value.  The only reason there currently isn't
for some rows is  because this code was implemented after there were already
records in the table and before Sub_Proposal_Status was added as a field to
the table.   Would you agree it makes good programming sense for this field
to be set to either "Y" or "N" for every record in Table B.

Also, I've always shied away from Left Joins (when possible).  I've never
understood why there is the 

"WHERE b.project IS NULL" with these Left Joins.  

There are no records in Table B where Project Is Null because Project is part
of the key.  So, I don't understand what this phrase does.  Isn't it like
saying "WHERE b.project <> "Molly".  Well, for the example rows I've posted,
this is always true, so wouldn't just this criteria return all rows!!??

Thanks for any insight you can provide.

Michel Walsh wrote:
>Hi,
>
>SELECT tableA.*
>FROM tableA LEFT JOIN (SELECT project, status FROM tableB WHERE 
>SubProjectInd ='Y') As b
>    ON tableA.project = b.project AND tableA.status=b.status
>WHERE b.project IS NULL
>
>return records in tableA not in the set of records in tableB where 
>SubProjectInd='Y' (we only test the project and status value, not the date 
>value, but we can add it if it is required, in the ON clause).
>
>Save that query as q1.
>
>SELECT d.project, LAST(d.status), d.date
>FROM q1 AS d INNER JOIN q1 As c ON c.project=d.project
>GROUP BY d.project, d.date
>HAVING d.date=MAX(c.date)
>
>should return one record associated to the latest date (per project), given 
>what is kept in q1.
>
>Hoping it may help,
>Vanderghast, Access MVP
>
>> I'm having trouble constructing a query to retrieve records from a TableA
>> based on an indicator in TableB and on getting the max date.  Here's the 
>[quoted text clipped - 32 lines]
>> of either 'Y" or "N" (but with no Nulls or spaces), then this can be 
>> arranged.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200602/1
.

Relevant Pages

  • Re: IF THEN in query..Maybe IIf
    ... assuming that there is a primary key for the existing records in the data ... table, which i'll call TableA, that matches a primary key in the imported ... now turn the query into an Update query. ... if the data file has the person's home ...
    (microsoft.public.access.queries)
  • Re: How to trim a record when using DB_OPEN_TABLE
    ... and TableB contains some items that need to be updated in TableA. ... EmplID ... QtrDate (primary key) ...
    (microsoft.public.access.modulesdaovba)
  • Re: How easy is this?
    ... > I am trying to create a form based on a query that is updatable and ... > TableA with 3 Fields: ... > and TableB with 2 Fields: ... ,CONSTRAINT pk_TableA_10232005_2 PRIMARY KEY ...
    (microsoft.public.access.queries)
  • Re: Problem with Dual subselect
    ... So I've constructed a UNION QUERY as follows. ... FROM TABLEA AS A ... B.STATUS_DATE FROM [TABLEB] As B ... A Lookup property on the foreign-key field ...
    (microsoft.public.access.queries)
  • Re: use a result as a FIELD in the design grid
    ... I have a key TableA that maps each station's flow (and ... Station: FlowField: TankLevel: PumpRun: ... OK, now from the previous query, I know that the FlowField ... So in a new query grid where TableA is linked to TableB ...
    (microsoft.public.access.queries)