Finne første ledige nummer

Sådan ville jeg gøre :

Opret en tabel, indeholdende samtlige numre, altså LT0001, LT0002, LT0003, LT0004, LT0005 ....

Benyt derefter en forespørgsel ( Find ikke-relaterede poster ) til at identificere hvilke numre der ikke er benyttet endnu :

SELECT Min(tblMaskinID.MaskinID) AS MinOfMaskinID
FROM tblMaskinID LEFT JOIN tblData ON tblMaskinID.MaskinID = tblData.MaskinID
GROUP BY tblData.MaskinID
HAVING (((tblData.MaskinID) Is Null));

Denne kan så benyttes i en opdateringsforesspørgsel i forbindelse med oprettelse af nye poster i din database

Har ikke jobbet så mye med databaser så jeg skjønner ikke helt hvordan du kobler sammen de to tabellene i forespørselen. Eks. min tabell med de inlagte maskinene heter tblMineMaskiner og den nye tabellen med samtlige maskinnummer heter tblAlleMaskiner. Hvordan ville da forespørselen se ut?

SELECT Min(tblAlleMaskiner.MaskinID) AS MinOfMaskinID
FROM tblAlleMaskiner LEFT JOIN tblData ON tblAlleMaskiner.MaskinID = tblMineMaskiner.MaskinID
GROUP BY tblMineMaskiner.MaskinID
HAVING (((tblMineMaskiner.MaskinID) Is Null));

Hvis du har en tabel som hedder Maskiner, burde denne forespørgsel give dig det næste ledige navn:

SELECT MIN('LT' & Format(Cint(Right(m1.MaskinNavn,4))+1,"0000")) AS ForSlag
FROM Maskiner AS m1, Maskiner AS m2
WHERE m1.MaskinNavn = (SELECT MAX(MaskinNavn) FROM Maskiner WHERE MaskinNavn < m2.MaskinNavn) AND m2.MaskinNavn = (SELECT MIN(MaskinNavn) FROM Maskiner WHERE MaskinNavn > m1.MaskinNavn) AND CInt(Right(m1.MaskinNavn, 4)) - Cint(Right(m2.MaskinNavn, 4)) < -1;

Bare for at gøre det helt klart, med min løsning er det ikke nødvendigt med en tabel med alle de mulige navne. :-)

Hmmm... Der er dog et problem, opdager jeg. Hvis der ikke er huller, returneres Null.

For at rette op på det faktum, skal der lige tilføjes endnu en subselect:

SELECT Nz(MIN('LT' & Format(Cint(Right(m1.MaskinNavn,4))+1,"0000")), (SELECT MAX('LT' & Format(Cint(Right(MaskinNavn,4))+1,"0000")) FROM Maskiner)) AS ForSlag
FROM Maskiner AS m1, Maskiner AS m2
WHERE m1.MaskinNavn = (SELECT MAX(MaskinNavn) FROM Maskiner WHERE MaskinNavn < m2.MaskinNavn) AND m2.MaskinNavn = (SELECT MIN(MaskinNavn) FROM Maskiner WHERE MaskinNavn > m1.MaskinNavn) AND CInt(Right(m1.MaskinNavn, 4)) - Cint(Right(m2.MaskinNavn, 4)) < -1;

Nå, nu bliver den næsten for "god"!! :-) Det viste sig, at også overnstående modificerede forespørgsel havde en alvorlig mangel. Hvis det første registrerede navn f.eks. var LT0003, så burde den jo foreslå LT0001, men det gjorde den ikke. Det kræver endnu en subselect, så resultatet bliver:

SELECT IIF((SELECT MIN(MaskinNavn) FROM Maskiner) > 'LT0001', 'LT0001', Nz(MIN('LT' & Format(Nz(Cint(Right(m1.MaskinNavn,4)),0)+1,"0000")), (SELECT MAX('LT' & Format(Cint(Right(MaskinNavn,4))+1,"0000")) FROM Maskiner))) AS ForSlag
FROM Maskiner AS m1, Maskiner AS m2
WHERE (m1.MaskinNavn Is Null Or m1.MaskinNavn = (SELECT MAX(MaskinNavn) FROM Maskiner WHERE MaskinNavn < m2.MaskinNavn or m2.MaskinNavn is null)) AND m2.MaskinNavn = (SELECT MIN(MaskinNavn) FROM Maskiner WHERE MaskinNavn > m1.MaskinNavn or m1.MaskinNavn is null) AND Nz(CInt(Right(m1.MaskinNavn, 4)),0) - Nz(Cint(Right(m2.MaskinNavn, 4)),0) < -1;


Som du kan se, blev det efterhånden en kraftig karl, men den virker.

Lettere revideret, håber nu at have afprøvet alle kombinationerne:

SELECT IIf((SELECT Nz(MIN(MaskinNavn), 'LT9999')  FROM Maskiner) > 'LT0001', 'LT0001', Nz(MIN('LT' & Format(Nz(Cint(Right(m1.MaskinNavn,4)),0)+1,"0000")), (SELECT MAX('LT' & Format(Cint(Right(MaskinNavn,4))+1,"0000")) FROM Maskiner))) AS ForSlag
FROM Maskiner AS m1, Maskiner AS m2
WHERE m1.MaskinNavn = (SELECT MAX(MaskinNavn) FROM Maskiner WHERE MaskinNavn < m2.MaskinNavn) AND m2.MaskinNavn = (SELECT MIN(MaskinNavn) FROM Maskiner WHERE MaskinNavn > m1.MaskinNavn) AND CInt(Right(m1.MaskinNavn, 4)) - Cint(Right(m2.MaskinNavn, 4)) < -1;

Når jeg prøver å kjøre denne spørringen får jeg følgende feil:
"Uttrykket inneholder en ugyldig delspørring. Kontroller syntaksen i delspørringen og sett delspørringen i parentes"

kjulius, se bort fra mitt siste svar feil av meg, det fungerte aldeles utmerket. Jeg har en ny utfordring, hvordan søke igjennom 3 felter i samme tabellen, eks. Maskinnavn1, MaskinNavn2 og MaskinNavn3??

Enda en utfordring:Søket du beskriver finner første ledige etter nummer, men maskinnavnet kan i tillegg til LTxxxx også være TCxxxx og DTxxxx. Således kan det finnes en LT0005,en TC0005 og en DT0005, hvordan løse det samtidig som det må søkes i 3 felter.

Hmmm... Selv om min forespørgsel virker, og jeg har haft det sjovt med at komme op med den, er den nok ikke så god til det du ønsker, her er det nok bedre at arbejde videre med jensen363' forslag, altså en "mastertabel" over alle de kombinationer der er mulige. Det er meget nemmere at adaptere denne til dine nye krav end min temmelig indviklede forespørgsel med dens virvar af subselects.

Jeg har dog et par kommentarer. Selv om jeg ikke kender formålet med din tabelstruktur (der kan være gode grundet til at den ser ud som den gør), så er det sjældent godt at have skulle søge efter grundlæggende det samme i tre felter. Det vil være meget bedre og  mere fleksibelt at placere maskinnavnene i en selvstændig tabel, som så refereres til fra hovedtabellen. Det kan lyde umiddelbart forkert, men hvis du tænker lidt over det, tror jeg du vil forstå, hvad jeg mener. Prøv at overveje følgende situation:

En fabrik har 3 afdelinger med hver op til 5 maskiner af 20 typer, som hver kan være af forskellige typer. Her kunne man jo vælge en struktur, hvor man havde alle en række for hver afdeling med hver mulig maskine i et felt:

tblAfdeling:
Id, FabriksId, AfdelingsNavn, Maskine1type, Maskine2type, Maskine3type, Maskine4type, Maskine5type

Det ville måske nok virke, hvis ikke alle maskiner var sat op, ville Maskine5type bare være null, ikke sandt. Men rent logisk er det langt fra ideelt. For det første skal alle forespørgsler tage hensyn til 5 felter; det er i sig selv problematisk. Men hvad hvis kravene ændrer sig over tid. Hvad hvis maskinerne bliver mindre (teknologi tenderer som regel i den retning)? Så er det pludseligt muligt at opstille måske 8 maskiner på det samme areal. Det ville kræve, at man tilføjede yderligere 3 felter, men endnu mere problematisk: alle forespørgsler ville skulle ændres til at tage højde for de nye felter.

Det vil være meget bedre at flytte maskinerne over i en selvstændig tabel:

tblAfdelinger:
Id, FabriksId, AfdelingsNavn

tblAfdelingsmaskiner:
Id, AfdelingsId, Maskinetype

På den måde ville man uden problemer kunne lave en forespørgsel, som finder bestemte maskintyper, og hvor de står, for der er kun ét felt at søge i. Og lige så vigtigt: der er ikke sat begrænsninger i antallet af maskiner.

Maskinerne i en afdeling kan nu vises meget let:

SELECT a.AfdelingsNavn, m.Maskintype
FROM tblAfdelinger a
INNER JOIN tblAfdelingsMaskiner m ON m.AfdelingsId = a.Id


Hvis du på et tidspunkt har brug for at vise maskinerne sidestillet, gøres det lettest i dit applikationsprogram, hvor de indlæses i et array (her er SQL ikke så velegnet). Hvis der ikke er flere typer end kolonner, og hver type skal placeres i en bestemt kolonne, kan det dog gøres i en forespørgsel ved f.eks. at bruge Access' PIVOT instruktion til at "vende" en forespørgsel. Det kan også gøres ved at bruge en IIf konstruktion i SELECT sætningen.

Tja, det var mine 5 øres input.... :-)