Find highest occuring ID in database

[dawnd3]

Hi there, without going into detail, I have 7 tables in which the ID's need to be generated based on the highest number currently in the database. (basically, no tables will have duplicate ID's). Is there code I can write to produce that ID? (I think I can do it in a union query, but I guess I was thinking that VBA will be better because I will then be taking that number to feed into another proceedure.)

Thanks for any help.

Dawn

http://www.calliopeconsulting.net

 

[lupins46]

You would still use a Union query to get it; in fact a two-level union query something like:

SELECT Max(q1.M1) AS MaxOfM1
FROM [SELECT Max(myID) AS M1
FROM [table1]
Union
SELECT Max(myid) AS M1
FROM [table2]
Union
.......
SELECT Max(myid) AS M1
FROM [table7]
order by m1]. AS q1;

You could then build a recordset or use a DLookup() function in VBA to give you the answer.


x= Dlookup("M1","myqueryname")

 

[MichaelRed]

While I hesitate to advise in this matter, a long term soloution would NOT use a union query. There are several pitfalls here:

1.[tab]for a multiuser app, there is a real possability that two users could easily instantiate the process 'simultaneously', thus retrievint the same "max" value and subsquently derive the same "(duplicate) Next" number.

2.[tab]As the number of entries grows, the process will slow down (for large numbers of entries dramatically so).

3.[tab]As the app evolves, additional uses of the "Unique" key may also be noted, using the approach, the basic process for generating the key would need to be made.


A consolidate soloution would have a couple of features:

A.[tab]a "Single" source for the generation of the value to be used as the New ID / Key.

B.[tab]Lock the source process so that only a single user could access it at any one time (preventing duplicates).

C.[tab] not require changes to the source for additional uses.


A procedure incorporating these attributes is shown in faq700-184. The faq includes some additional commentary / cautions on its use. Further, it is more complex than necessary for the requirements stated here (above). It COULD, however, be useful in deriving a routine which provides the desired process.





MichaelRed