abrothers106
New member
- Local time
- Today, 06:51
- Joined
- Jul 31, 2012
- Messages
- 4
Hi,
Any help on this?
The company I work for only has so many "cost codes" they can use on any particular "job #"... and therefore, I need to be able to provide the next "unused" cost code when a job # is entered. The gaps need to be filled so that we don't run out.
For Instance, if the user enters "232512" for the job #, I want it to return the lowest, unused cost code.
My SQL statement looks like this:
SELECT [tbl MAIN].[ICI Job #], Max([tbl MAIN].[Cost Code]) AS [Last Cost Code Used]
FROM [tbl MAIN]
GROUP BY [tbl MAIN].[ICI Job #], [tbl MAIN].[EWA?]
HAVING ((([tbl MAIN].[ICI Job #])=[Enter Job #]) AND (([tbl MAIN].[EWA?])=No));
This works, but gives me the last, and highest, cost code last used.
I really want it to spit the number to be used out, not the last number used - so there isn't any confusion.
Thanks!
Any help on this?

The company I work for only has so many "cost codes" they can use on any particular "job #"... and therefore, I need to be able to provide the next "unused" cost code when a job # is entered. The gaps need to be filled so that we don't run out.
For Instance, if the user enters "232512" for the job #, I want it to return the lowest, unused cost code.
My SQL statement looks like this:
SELECT [tbl MAIN].[ICI Job #], Max([tbl MAIN].[Cost Code]) AS [Last Cost Code Used]
FROM [tbl MAIN]
GROUP BY [tbl MAIN].[ICI Job #], [tbl MAIN].[EWA?]
HAVING ((([tbl MAIN].[ICI Job #])=[Enter Job #]) AND (([tbl MAIN].[EWA?])=No));
This works, but gives me the last, and highest, cost code last used.
I really want it to spit the number to be used out, not the last number used - so there isn't any confusion.
Thanks!