Max query with missing numbers in between

[Hugh self taught]

Hi code experts,

I use the following query

SELECT Left([Nat_Reg],2) AS PrvAbr, Max(Right([Nat_Reg],3)) AS MaxNum
FROM Competitors
GROUP BY Left([Nat_Reg],2);

to determine the last number part of the register number. However as time
goes by people change locale & their register number has to be changed so I
end up with gaps in the number sequence.

Is it possible to write a query that will give me those numbers? Then I can
run a query on that to determine the smallest number to re-assign

 

[KARL DEWEY]

Does your field Nat_Reg have gaps or are the numbers flagged as not in service?
Are your numbers 5 digits?

 

[Hugh self taught]

Hi Karl,

The field is alphameric made up of 2 alpha & 3 numeric characters. What I do
is split the field & look for max of the numeric value based on the alpha
value then +1 for the next available number, put the alpha & numeral back
together again as the new number to allocate.

Over time people move locale so I have to change their Nat_Reg number (the
alpha represents their locale) which means I create gaps in the sequence.
What I want to achieve is to pick those numbers up as being the next number
to allocate & only do the max +1 routine if there isn't a missing number in
the sequence to re-assign.

Does your field Nat_Reg have gaps or are the numbers flagged as not in service?
Are your numbers 5 digits?

--
Build a little, test a little.

Hi code experts,

I use the following query

SELECT Left([Nat_Reg],2) AS PrvAbr, Max(Right([Nat_Reg],3)) AS MaxNum
FROM Competitors
GROUP BY Left([Nat_Reg],2);

to determine the last number part of the register number. However as time
goes by people change locale & their register number has to be changed so I
end up with gaps in the number sequence.

Is it possible to write a query that will give me those numbers? Then I can
run a query on that to determine the smallest number to re-assign

 

[KARL DEWEY]

UNTESTED UNTESTED
Create a query with the alpha and numeric as separate optput fields along
with any other fields that may be needed.

In a second query design view put the first query twice and left join on the
alpha.

FIELDS Alpha.1 Number.1 Number.2 Next_Num: (Number.1) + 2
CRITERIA (Number.1) +1
GROUP BY Max WHERE EXPRESSION

This would return ABA 495 496 497


--
Build a little, test a little.

Hi Karl,

The field is alphameric made up of 2 alpha & 3 numeric characters. What I do
is split the field & look for max of the numeric value based on the alpha
value then +1 for the next available number, put the alpha & numeral back
together again as the new number to allocate.

Over time people move locale so I have to change their Nat_Reg number (the
alpha represents their locale) which means I create gaps in the sequence.
What I want to achieve is to pick those numbers up as being the next number
to allocate & only do the max +1 routine if there isn't a missing number in
the sequence to re-assign.

Does your field Nat_Reg have gaps or are the numbers flagged as not in service?
Are your numbers 5 digits?

--
Build a little, test a little.

Hi code experts,

I use the following query

SELECT Left([Nat_Reg],2) AS PrvAbr, Max(Right([Nat_Reg],3)) AS MaxNum
FROM Competitors
GROUP BY Left([Nat_Reg],2);

to determine the last number part of the register number. However as time
goes by people change locale & their register number has to be changed so I
end up with gaps in the number sequence.

Is it possible to write a query that will give me those numbers? Then I can
run a query on that to determine the smallest number to re-assign

 

[KARL DEWEY]

You might need to add Ascending for Number.1 and set TOP 1 to make it
work.

--
Build a little, test a little.

Hi Karl,

The field is alphameric made up of 2 alpha & 3 numeric characters. What I do
is split the field & look for max of the numeric value based on the alpha
value then +1 for the next available number, put the alpha & numeral back
together again as the new number to allocate.

Over time people move locale so I have to change their Nat_Reg number (the
alpha represents their locale) which means I create gaps in the sequence.
What I want to achieve is to pick those numbers up as being the next number
to allocate & only do the max +1 routine if there isn't a missing number in
the sequence to re-assign.

Does your field Nat_Reg have gaps or are the numbers flagged as not in service?
Are your numbers 5 digits?

--
Build a little, test a little.

Hi code experts,

I use the following query

SELECT Left([Nat_Reg],2) AS PrvAbr, Max(Right([Nat_Reg],3)) AS MaxNum
FROM Competitors
GROUP BY Left([Nat_Reg],2);

to determine the last number part of the register number. However as time
goes by people change locale & their register number has to be changed so I
end up with gaps in the number sequence.

Is it possible to write a query that will give me those numbers? Then I can
run a query on that to determine the smallest number to re-assign

 

[Hugh self taught]

Hi Karl,

I've gotten a little lost in formatting the 2nd query. Below is the 1st then
2nd query as I have them so far but as you will no doubt see I get a "Cannot
have aggregate function in Group by clause" error

SELECT Left([Nat_Reg],2) AS PrvAbr, Right([Nat_Reg],3) AS [Number]
FROM Competitors;

SELECT tstqrySplitReg.PrvAbr, tstqrySplitReg.Number,
tstqrySplitReg_1.Number, [tstqrySplitReg.Number]+2 AS Nextnum
FROM tstqrySplitReg AS tstqrySplitReg_1 LEFT JOIN tstqrySplitReg ON
tstqrySplitReg_1.PrvAbr = tstqrySplitReg.PrvAbr
GROUP BY Max(((tstqrySplitReg.Number)=([tstqrySpliReg].[Number]+1)))
HAVING ((((([tstqrySplitReg].[Number])=([tstqrySpliReg].[Number]+1)))));

Can you point out where I've gone wrong?

UNTESTED UNTESTED
Create a query with the alpha and numeric as separate optput fields along
with any other fields that may be needed.

In a second query design view put the first query twice and left join on the
alpha.

FIELDS Alpha.1 Number.1 Number.2 Next_Num: (Number.1) + 2
CRITERIA (Number.1) +1
GROUP BY Max WHERE EXPRESSION

This would return ABA 495 496 497


--
Build a little, test a little.

Hi Karl,

The field is alphameric made up of 2 alpha & 3 numeric characters. What I do
is split the field & look for max of the numeric value based on the alpha
value then +1 for the next available number, put the alpha & numeral back
together again as the new number to allocate.

Over time people move locale so I have to change their Nat_Reg number (the
alpha represents their locale) which means I create gaps in the sequence.
What I want to achieve is to pick those numbers up as being the next number
to allocate & only do the max +1 routine if there isn't a missing number in
the sequence to re-assign.

Does your field Nat_Reg have gaps or are the numbers flagged as not in service?
Are your numbers 5 digits?

--
Build a little, test a little.


:

Hi code experts,

I use the following query

SELECT Left([Nat_Reg],2) AS PrvAbr, Max(Right([Nat_Reg],3)) AS MaxNum
FROM Competitors
GROUP BY Left([Nat_Reg],2);

to determine the last number part of the register number. However as time
goes by people change locale & their register number has to be changed so I
end up with gaps in the number sequence.

Is it possible to write a query that will give me those numbers? Then I can
run a query on that to determine the smallest number to re-assign

 

[Hugh self taught]

Hi Karl,

Scrap my other response. I came across the following code from Tom Ellison
which fits my need exactly. However my question is such:

There is only just over 600 records currently in the database & the locale
with the most records is approx 400. In that 400 records there are 3 gaps
with 1 or 2 missing numbers When the query runs for that locale it takes
quite a while to finish.

Is there a way to speed this up? Below is the 3 queries in sequence ....

'qrySplitRegNo
SELECT Left([Nat_Reg],2) AS PrvAbr, Int(Right([Nat_Reg],3)) AS RegNumber
FROM Competitors
ORDER BY Left([Nat_Reg],2), Right([Nat_Reg],3);


'qryMissingRegNo
SELECT T.PrvAbr, [RegNumber]+1 AS Missing
FROM qrySplitRegNo AS T
WHERE (((Exists (SELECT *
FROM qrySplitRegNo T1
WHERE T1.PrvAbr = T.PrvAbr
AND T1.RegNumber = T.RegNumber + 1))=False) AND ((T.RegNumber)<(SELECT
MAX(RegNumber)
FROM qrySplitRegNo T1
WHERE T1.PrvAbr = T.PrvAbr)))
ORDER BY T.PrvAbr, [RegNumber]+1;


'qryGetNextRegNo
SELECT TOP 1 [qryMissingRegNo].PrvAbr, [qryMissingRegNo].[Missing]
FROM qryMissingRegNo
WHERE ((([qryMissingRegNo].PrvAbr)=Forms!frmNewMemProv!Provcode));

UNTESTED UNTESTED
Create a query with the alpha and numeric as separate optput fields along
with any other fields that may be needed.

In a second query design view put the first query twice and left join on the
alpha.

FIELDS Alpha.1 Number.1 Number.2 Next_Num: (Number.1) + 2
CRITERIA (Number.1) +1
GROUP BY Max WHERE EXPRESSION

This would return ABA 495 496 497


--
Build a little, test a little.

Hi Karl,

The field is alphameric made up of 2 alpha & 3 numeric characters. What I do
is split the field & look for max of the numeric value based on the alpha
value then +1 for the next available number, put the alpha & numeral back
together again as the new number to allocate.

Over time people move locale so I have to change their Nat_Reg number (the
alpha represents their locale) which means I create gaps in the sequence.
What I want to achieve is to pick those numbers up as being the next number
to allocate & only do the max +1 routine if there isn't a missing number in
the sequence to re-assign.

Does your field Nat_Reg have gaps or are the numbers flagged as not in service?
Are your numbers 5 digits?

--
Build a little, test a little.


:

Hi code experts,

I use the following query

SELECT Left([Nat_Reg],2) AS PrvAbr, Max(Right([Nat_Reg],3)) AS MaxNum
FROM Competitors
GROUP BY Left([Nat_Reg],2);

to determine the last number part of the register number. However as time
goes by people change locale & their register number has to be changed so I
end up with gaps in the number sequence.

Is it possible to write a query that will give me those numbers? Then I can
run a query on that to determine the smallest number to re-assign