How can I get the next unused number in a query?

[abrothers106]

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!

 

[Anakardian]

If you have already found the highest number used, why not just add 1 to it and display that?

 

[abrothers106]

That would work, however, we only have a certain amount of codes that can be used... and in order to not run out, we have to fill in the gaps. The gaps created are not under our control. We have to put in these records sometimes as they come in. When most of the time we get to choose the cost code we're going to use. Am I making sense?

 

[Anakardian]

That is a problem someone with more knowledge will need to look at as I can't quite see a solution I could make.

 

[John Big Booty]

Have a look at the various Domain Aggregation functions. You should be able to use one of those to find the record you are after.

 

[abrothers106]

SELECT [tbl MAIN].[ICI Job #], Min([tbl MAIN].[Cost Code]+1) 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));

The above SQL is what I have now, and it will return the lowest number used, plus 1. However, it doesn't test to see if the number it is about to spit out is already being used. I need to make the criteria something like "Not In [Cost Code]"... I have tried this several ways, and I'm not having ANY luck.

Any help?

 

[jdraw]

How do you identify an unused CostCode?
What does it mean in your context to have an unused CostCode within a table of CostCodes?

Sounds like you're looking for .... Min(CostCode) where CostCodeStatus = "unused"....

[whatever it is that identifies an unused CostCode].

Instead of SQL, perhaps you could tell us in plain English what a CostCode is; how the numbering is done; what does "fill in the gaps'' mean; why would you add 1 to a value within a series of values? Why would that next value be unused?

 

[abrothers106]

Hi, yes! That is what I'm trying to acheive... CostCodeStatus = "unused"... but how can I do this??? That is my problem. I've been working in SQL AND in the query design... and everything I am trying has some sort of error.

A CostCode is used with every record.. but is unique to a Job#. There are gaps in the CostCodes.. For instance... 1702 then 1704. And I would want it to spit me out 1703 as the next cost code to be used. After that is used, then it would spit me out 1705 and so on. The gapping is not under our control. It will always be that way. Things come in with Cost Codes already on them, and that is just how we have to enter the records. But we have a limited amount of cost codes that we can use.... and if we don't fill in the gaps, we will run out. See? I just need the actual SQL or syntax or something on how to make the criteria set so that it doesn't give me a number that has already been used.

 

[jdraw]

I'm still as confused as before. You're going to have to describe
-what a cost code is
-what exactly is the process for assigning a cost code
-why are you in danger of running out of cost codes
-what is the rationalization of having a limited number of cost codes
-how does cost code relate to Job#, and to the "records coming in"
-how do you know that 1703 is available for use?
if it was used before, why is it unused now?