Subselect Query Problem

[robert d via AccessMonster.com]

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 data
in TableA and TableB.

TableA
Project Status Date
ProjectA Approved 1/21/2005
ProjectA Awarded 12/21/2005
ProjectB Pending 11/04/2004
ProjectB Study 6/03/2003
ProjectC Approved 1/21/2005
ProjectD Approved 1/21/2005

TableB
Project Status Date SubProjectInd
ProjectA Approved 1/21/2005 Y
ProjectA Awarded 12/21/2005 N
ProjectB Pending 11/04/2004 N
ProjectB Study 6/03/2003
ProjectC Approved 1/21/2005 Y
ProjectD Approved 1/21/2005 N

The three fields shown in TableA make up the primary key. So I want to
select the rows in Table A where the SubProjectInd in TableB is not equal to
"Y" and also I want the Project selected in TableA to be the one with the Max
(Date).


So the results of the query should be Rows 2, 3, and 6 from TableA.

If it makes it easier to make sure that each row in TableB has a SubStatusInd
of either 'Y" or "N" (but with no Nulls or spaces), then this can be arranged.

 

[Michel Walsh]

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

 

[robert d via AccessMonster.com]

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.

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.

 

[Michel Walsh]

Hi,


A left join, say

tableA LEFT JOIN tableB


is like an inner join, except that if one record of tableA would not be in
the result (of the inner join), it is logically re-introduced into the
result, but since nothing match in tableB, whatever comes from tableB is
filled with NULL, in that row.

tableA
f1
1
2
3

tableB
g1
1
3


SELECT f1, g1 FROM tableA LEFT JOIN tableB ON tableA.f1=tableB.g1

will be:

f1 g1
1 1
2 null
3 3


note the row f1=2. An inner join would have not include it, in the result.
The LEFT join reintroduces it, but since there is no g1 that corresponds to
it, a value still has to be put under this column, a null is supplied.

The WHERE clause checks for the presence of this NULL to determine that f1
was without match in g1. Indeed, the WHERE clause examine the result of the
JOIN, not the initial rows of the tables!


Hoping it may help,
Vanderghast, Access MVP

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.

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.

 

[robert d via AccessMonster.com]

Ok. That explanation has helped me to understand left joins better
especially the part about what the WHERE clause does. I'm on the road, but
will be playing around with this in the next day or so. I'll post back if I
need more help.

Thanks!

Hi,

A left join, say

tableA LEFT JOIN tableB

is like an inner join, except that if one record of tableA would not be in
the result (of the inner join), it is logically re-introduced into the
result, but since nothing match in tableB, whatever comes from tableB is
filled with NULL, in that row.

tableA
f1
1
2
3

tableB
g1
1
3

SELECT f1, g1 FROM tableA LEFT JOIN tableB ON tableA.f1=tableB.g1

will be:

f1 g1
1 1
2 null
3 3

note the row f1=2. An inner join would have not include it, in the result.
The LEFT join reintroduces it, but since there is no g1 that corresponds to
it, a value still has to be put under this column, a null is supplied.

The WHERE clause checks for the presence of this NULL to determine that f1
was without match in g1. Indeed, the WHERE clause examine the result of the
JOIN, not the initial rows of the tables!

Hoping it may help,
Vanderghast, Access MVP

Michael:

[quoted text clipped - 72 lines]