Return Maximum Value When Field is Duplicate

S

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?
 
K

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];
 
S

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)?

KARL DEWEY said:
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


Stacey Crowhurst said:
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?
 
K

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


Stacey Crowhurst said:
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)?

KARL DEWEY said:
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


Stacey Crowhurst said:
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?
 
S

Stacey Crowhurst

Perfect! Thank you.

KARL DEWEY said:
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


Stacey Crowhurst said:
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)?

KARL DEWEY said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top