Return Maximum Value When Field is Duplicate

[Stacey Crowhurst]

Hi. I have a table for construction project budgets.

Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/01/2009
Budget Code - 101Construction
Line Amount - $50,000
Sequence - 0

For any project there are many budget codes (i.e. construction, design,
permits, etc.) for each one project. Currently they reside in the same
table. When a budget is revised I need the query to only grab the line from
the table with the highest sequence value.

Revision
Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/15/2009
Budget Code - 101Construction
Line Amount - $58,000
Sequence - 1

I want the query to return all budget codes and amounts for a project, but
only the latest entry for each. I thought adding the sequence field to
select a max value on would help work this out. However, I can't come up
with the right SQL. Any suggestions?

 

[KARL DEWEY]

qryMaxProj --
SELECT [Project No.], Max([Sequence]) AS MaxSequence
FROM YourTable
GROUP BY [Project No.];

SELECT [YourTable].*
FROM [YourTable] INNER JOIN [qryMaxProj ON [YourTable].[Project No.] =
[qryMaxProj].[MaxSequence];

 

[Stacey Crowhurst]

Hi Karl. Thanks for your help. I built the MaxProj query and then used it
for the second half of the instructions. But then I got a type mismatch
error. Is that becuase the table and query are joined on incompatible fields
(Proj No and Sequence)?

qryMaxProj --
SELECT [Project No.], Max([Sequence]) AS MaxSequence
FROM YourTable
GROUP BY [Project No.];

SELECT [YourTable].*
FROM [YourTable] INNER JOIN [qryMaxProj ON [YourTable].[Project No.] =
[qryMaxProj].[MaxSequence];

--
KARL DEWEY
Build a little - Test a little

Hi. I have a table for construction project budgets.

Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/01/2009
Budget Code - 101Construction
Line Amount - $50,000
Sequence - 0

For any project there are many budget codes (i.e. construction, design,
permits, etc.) for each one project. Currently they reside in the same
table. When a budget is revised I need the query to only grab the line from
the table with the highest sequence value.

Revision
Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/15/2009
Budget Code - 101Construction
Line Amount - $58,000
Sequence - 1

I want the query to return all budget codes and amounts for a project, but
only the latest entry for each. I thought adding the sequence field to
select a max value on would help work this out. However, I can't come up
with the right SQL. Any suggestions?

 

[KARL DEWEY]

My error, it should be this --
SELECT [YourTable].*
FROM [YourTable] INNER JOIN [qryMaxProj ON [YourTable].[Sequence] =
[qryMaxProj].[MaxSequence];

--
KARL DEWEY
Build a little - Test a little

Hi Karl. Thanks for your help. I built the MaxProj query and then used it
for the second half of the instructions. But then I got a type mismatch
error. Is that becuase the table and query are joined on incompatible fields
(Proj No and Sequence)?

qryMaxProj --
SELECT [Project No.], Max([Sequence]) AS MaxSequence
FROM YourTable
GROUP BY [Project No.];

SELECT [YourTable].*
FROM [YourTable] INNER JOIN [qryMaxProj ON [YourTable].[Project No.] =
[qryMaxProj].[MaxSequence];

--
KARL DEWEY
Build a little - Test a little

Hi. I have a table for construction project budgets.

Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/01/2009
Budget Code - 101Construction
Line Amount - $50,000
Sequence - 0

For any project there are many budget codes (i.e. construction, design,
permits, etc.) for each one project. Currently they reside in the same
table. When a budget is revised I need the query to only grab the line from
the table with the highest sequence value.

Revision
Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/15/2009
Budget Code - 101Construction
Line Amount - $58,000
Sequence - 1

I want the query to return all budget codes and amounts for a project, but
only the latest entry for each. I thought adding the sequence field to
select a max value on would help work this out. However, I can't come up
with the right SQL. Any suggestions?

 

[Stacey Crowhurst]

Perfect! Thank you.

My error, it should be this --
SELECT [YourTable].*
FROM [YourTable] INNER JOIN [qryMaxProj ON [YourTable].[Sequence] =
[qryMaxProj].[MaxSequence];

--
KARL DEWEY
Build a little - Test a little

Hi Karl. Thanks for your help. I built the MaxProj query and then used it
for the second half of the instructions. But then I got a type mismatch
error. Is that becuase the table and query are joined on incompatible fields
(Proj No and Sequence)?

qryMaxProj --
SELECT [Project No.], Max([Sequence]) AS MaxSequence
FROM YourTable
GROUP BY [Project No.];

SELECT [YourTable].*
FROM [YourTable] INNER JOIN [qryMaxProj ON [YourTable].[Project No.] =
[qryMaxProj].[MaxSequence];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a table for construction project budgets.

Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/01/2009
Budget Code - 101Construction
Line Amount - $50,000
Sequence - 0

For any project there are many budget codes (i.e. construction, design,
permits, etc.) for each one project. Currently they reside in the same
table. When a budget is revised I need the query to only grab the line from
the table with the highest sequence value.

Revision
Budget ID - AutoNumber
Project No. - CCP04-01
Date Approved - 01/15/2009
Budget Code - 101Construction
Line Amount - $58,000
Sequence - 1

I want the query to return all budget codes and amounts for a project, but
only the latest entry for each. I thought adding the sequence field to
select a max value on would help work this out. However, I can't come up
with the right SQL. Any suggestions?