Selecting last detail record

[Kevin]

I wish to calculate a "Delta" between an awarded bid and
our last construction estimate.

The Estimate table data stores information from each
estimating phase (Schematic Design (SD), Design
Development, Construction Documents (DD), etc.), appearing
below.

I would like a query to select [Amount] from the last
estimate, in this case $10,720,000 in estimate #3.

Est# Phase Amount SF Note
1 SD 12,000,000 8,000
2 CD 10,565,000 6,700 Revised Scope
3 Prebid 10,720,000 6,700

Can anyone help?

Thanks.

 

[Karl Dewey]

Last:max([Est#])

 

[John Spencer (MVP)]

Well, How do you tie all the estimates together for any one project. Assuming a
projectcode or some other value to tie them together.

SELECT [Est#], Amount
FROM TableName as M
WHERE [Est#] =
(SELECT Max(Est#)
FROM TableName As T
WHERE T.ProjectCode = M.ProjectCode)

 

[Kevin]

Thanks, John. Yes, the estimates are tied to a common
JobNumber, and the SQL below finds the records I'm
interested in. I don't, however, understand why. If you
could briefly explain it to me, I'd be grateful. You
know, "Give a man a fish...TEACH a man to fish..."

Best regards.

 

[John Spencer (MVP)]

Well, let's see.

The subquery (the part in parens) runs many times. Once for each row in the main
query. When it runs it returns the Highest Est# (MAX) for records that have the
same number as the current row. Then it matches the... and this is not working.

Try the book SQL for Mere Mortals, perhaps it has a better explanation.

Another way to accomplish this would be two queries.

QueryOne
SELECT T.ProjectCode, Max(Est#)
FROM TableName As T
GROUP BY T.ProjectCode

Then you join that to the original table

SELECT A.ProjectCode, A.[Est#], A.Amount
FROM TableName As A INNER JOIN
QueryOne as Q On A.ProjectCode = Q.ProjectCode AND
A.[Est#] = Q.[Est#]

This would not be updatable, while the subquery solution might be. However,
this one would probably be faster.

Thanks, John. Yes, the estimates are tied to a common
JobNumber, and the SQL below finds the records I'm
interested in. I don't, however, understand why. If you
could briefly explain it to me, I'd be grateful. You
know, "Give a man a fish...TEACH a man to fish..."

Best regards.

-----Original Message-----
Well, How do you tie all the estimates together for any one project. Assuming a
projectcode or some other value to tie them together.

SELECT [Est#], Amount
FROM TableName as M
WHERE [Est#] =
(SELECT Max(Est#)
FROM TableName As T
WHERE T.ProjectCode = M.ProjectCode)

 

[Kevin]

Thanks a lot, John. That helps a lot, especially since I
have a similar problem to solve with another table.

Best regards.

-----Original Message-----
Well, let's see.

The subquery (the part in parens) runs many times. Once for each row in the main
query. When it runs it returns the Highest Est# (MAX) for records that have the
same number as the current row. Then it matches the...

and this is not working.