Return Maximum Value When Field is Duplicate

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
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?
 
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];
 
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?
Click to expand...
Click to expand...
 
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?
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top