Updateable Max Query

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

Charles D Clayton Jr

A2K
I have two tables. One stores the names of Drawings and the other
stores the revisions of those drawings. The PK of Drawings is the FK
of Revisions. While I can join the two tables together to show the
latest revision of the drawings, I am unable to do it in a way that I
can update the query. I looked on Dev Ashish's site
(http://www.mvps.org/access/) for an answer and found one that works
for one table but I am unable to make it work for two tables. Can
someone help? Here is Dev's answer:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q
WHERE DateOut = (SELECT Max(T.DateOut)
FROM TableName As T
WHERE T.BookID = Q.BookID)

I need to use two fields from "Drawings" ([Iso], [Sheet]) and 1 field
from "Revisions" ([Rev]).

I keep getting messed up when I do the INNER JOIN between the two
tables

Thanks for your time,

Charles D Clayton Jr
 
Hi,

Make a first query, something like:

SELECT q.*
FROM Revisions As q
WHERE revisionNumber=(SELECT MAX(t.revisionNumber)
FROM Revisions as t
WHERE t.drawingNumber=q.drawingNumber )


save it as q1. Next, make an inner join between q1 and Drawings through
their common field drawingNumber.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks for the help,

Charles D Clayton Jr

Michel Walsh said:
Hi,

Make a first query, something like:

SELECT q.*
FROM Revisions As q
WHERE revisionNumber=(SELECT MAX(t.revisionNumber)
FROM Revisions as t
WHERE t.drawingNumber=q.drawingNumber )


save it as q1. Next, make an inner join between q1 and Drawings through
their common field drawingNumber.


Hoping it may help,
Vanderghast, Access MVP



Charles D Clayton Jr said:
A2K
I have two tables. One stores the names of Drawings and the other
stores the revisions of those drawings. The PK of Drawings is the FK
of Revisions. While I can join the two tables together to show the
latest revision of the drawings, I am unable to do it in a way that I
can update the query. I looked on Dev Ashish's site
(http://www.mvps.org/access/) for an answer and found one that works
for one table but I am unable to make it work for two tables. Can
someone help? Here is Dev's answer:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q
WHERE DateOut = (SELECT Max(T.DateOut)
FROM TableName As T
WHERE T.BookID = Q.BookID)

I need to use two fields from "Drawings" ([Iso], [Sheet]) and 1 field
from "Revisions" ([Rev]).

I keep getting messed up when I do the INNER JOIN between the two
tables

Thanks for your time,

Charles D Clayton Jr
Click to expand...
Click to expand...
 
Back
Top