nummer - MIN(nummer)+1

[Frits1969]

Hallo allemaal,

Ik heb een vraag. Ik gebruik Access 2016
In VBA heb ik een regel al deze

DoCmd.RunSQL ("UPDATE Jaarsamenvoeging SET datum = #01-01-" & Me.TmpJaar + 1 & "#;")

Dat werkt goed.
Nu wil ik een soortgelijke regel en maar dan met de MIN functie er in. Ik dacht, dat moet op deze manier.

DoCmd.RunSQL ("UPDATE Jaarsamenvoeging SET NwNummer = nummer - Min(nummer) + 1;")

Hierbij is nummer een integer die automatisch wordt gegenereerd.
NwNummer moet een volgnummer komen dat bij de eerste regel een 1 plaatst. Ik had gehoopt dat het niet nodig was, maar door de automatische nummering is het laagste nummer meestal geen 1 maar wordt het zelfs een steeds hoger nummer.
Wie kan mij hierbij helpen?

Alvast vriendelijk bedankt.

 

[OctaFish]

Ik vind de manier waarop je de datum genereert al niet helemaal geweldig (zelf gebruik ik altijd DateValue), maar de echte vraag snap ik niet: als je een 1 als resultaat wilt, voer je toch gewoon een 1 in? Automatische nummering verhoogt een volgend nummer met 1, maar doet niks met het laagste nummer. Dat blijft altijd 1.
Weet je wat ik denk? Dat je een tijdelijke tabel hebt waarin een autonummer veld zit. Die tabel maak je steeds leeg, en vul je opnieuw. Daarbij vergeet je dan om de db eerst te compileren waardoor het Autonummerveld steeds begint met het eerstvolgende (technisch) beschikbare nummer. En dat is dan nooit meer de 1, want die kan je, net als alle andere nummers, maar één keer gebruiken.
'Regelnummers' gebruik je overigens in een rapport, en daar heb je deze constructie helemaal niet nodig, want in een rapport is het een aparte functie dus dat werkt altijd.

 

[Frits1969]

Bedankt OctaFish voor je reactie. Je begrijpt het goed dat ik de tabel steeds leeg. Echter, als ik de autonummering gebruik, doe ik dat op het veld "nummer" het veld "NwNummer" is een integer. Ik wil dan alle regels daar nummeren vanaf 1. In deze tabel heb ik geen sleutels ingesteld.

Wat ik doe is naar een tijdelijke tabel het een en ander kopiëren, groeperen en optellen. Zo hou ik minder regels over. Vervolgens kopieer ik nog een regel daarnaar toe. hierdoor hebben de regels in het veld nummer de nummervolgorde die ik wil hebben. Alleen niet de juiste nummers. Dat wil ik dan met deze actie doen.

 

[OctaFish]

Je gebruikt deze opdracht:

DoCmd.RunSQL ("UPDATE Jaarsamenvoeging SET NwNummer = nummer - Min(nummer) + 1;")

Die zou dus een gewoon numeriek veld (NwNummer) moeten genereren op basis van het Autonummerveld [Nummer]. En daar gaat het, zoals ik al uitlegde, (goed) fout. Omdat autonummers nu eenmaal de onhebbelijke eigenschap hebben dat ze nooit opnieuw beginnen te nummeren.

Als ik je berichtjes zo lees, ben je in mijn ogen met volslagen overbodige handelingen (en tijdelijke tabellen) bezig. Ik zou deze werkwijze in ieder geval nooit gebruiken en/of nodig hebben. Ik denk namelijk dat alles gewoon met simpele queries kan. Extra tabellen belasten de db alleen maar, zorgen voor nodeloze overhead en maken de db dus nodeloos groter en zwaarder.
Zelf zie ik bijvoorbeeld de noodzaak van regelnummers opslaan totaal niet; waarom zou je dat in een tabel doen als je dat simpel in een query kunt maken?

 

[Frits1969]

Als ik "NwNummer" automatisch laat nummeren, dan heb ik een veld dat precies het zelfde is als "nummer". Daar schiet ik dan dus niets mee op, want dan zou ik het veld "nummer" kunnen gebruiken.

Ieder jaar wil ik tabel "Dagboek" schonen. Ik wil dan dat regels die alleen verschillen in "datum", "nummer", "omschrijving" en "bedrag" worden samengevoegd. Het bedrag wordt opgeteld en de datum en omschrijving krijgen overal het zelfde ingevuld. De nummering moet hernummerd worden. (Dat is het probleem waar ik nu mee worstel.) Alle andere velden worden gebruikt om te groeperen. Dit alles gebeurt in tabel "Jaarsamenvoeging" of in de kopieerslag van tabel "Dagboek" naar tabel "Jaarsamenvoeging" . De oude regels worden uit tabel "Dagboek" verwijderd.
Omdat ik de 1e dag van het nieuwe jaar gebruik om de samengevoegde regels een datum te geven, moet ik me afvragen of er op die dag al regels bestaan. (Dit kan voorkomen)
De volgende stap is dus om die ene dag er ook aan toe te voegen. Het enige dat daar moet gebeuren is het aanpassen van de nummering. Ik wil dat immers een hogere nummering geven dan de samengevoegde regels van het jaar er voor.
Die eerste dag van het jaar wordt dan geleegd, waarna ik de regels vanuit tabel "Jaarsamenvoeging" naar tabel "Dagboek" over zet.

Als je een voorbeeld kan geven hoe ik dit in een simpele sql query kan gooien, hou ik me aanbevolen.
Nu heb ik er de volgende code voor:

Private Sub KnopJaarafsluiting_Click()
Dim BestandsnaamBE As String
BestandsnaamBE = "K:\bestandsnaam_be.accdb"

Dim Aantal0101, Min0101 As Integer
Dim Aantalfilter As String

'tel het aantal records dat er voor 1 januari in het jaar zijn.
Aantalfilter = "[dagboek].[Bedrijfsnummer] = " & TmpBedrijfsnummer & " AND [Dagboek].[datum] < #01-01-" & Me.TmpJaar + 1 & "#"
Aantal0101 = DCount("nummer", "dagboek", Aantalfilter)

'tel het aantal record dat er op 1 januari in het nieuwe jaar zijn.
Aantalfilter = "[dagboek].[Bedrijfsnummer] = " & TmpBedrijfsnummer & " AND [Dagboek].[datum] = #01-01-" & Me.TmpJaar + 1 & "#"
Aantal0101 = DCount("nummer", "dagboek", Aantalfilter)

'***** Kopieer van tabel Dagboek naar tabel Jaarsamenvoeging *****
'Kopieer het gekozenjaar en bedrijf uit de tabel Dagboek in de tabel Jaarsamenvoeging
DoCmd.RunSQL ("INSERT INTO Jaarsamenvoeging ( Datum, Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD ) SELECT #01-01-" & Me.TmpJaar & "# As Datum, 'Jaarsamenvoeging " & Me.TmpJaar & "' As Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD FROM Dagboek GROUP BY Datum, Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD HAVING Datum < #01-01-" & Me.TmpJaar + 1 & "#;")


'Verwijder in tabel Dagboek het gekozen jaar
DoCmd.RunSQL ("Delete * FROM Dagboek WHERE Datum < #01-01-" & Me.TmpJaar + 1 & "#;")


'Kopieer uit de tabel Dagboek alles met de datum 01-01- daarop volgende jaar
DoCmd.RunSQL ("INSERT INTO Jaarsamenvoeging ( Datum, Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD ) SELECT Datum, Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD FROM Dagboek WHERE Datum = #01-01-" & Me.TmpJaar + 1 & "#;")


'Verwijder in de tabel Dagboek alles met de datum 01-01- daarop volgende jaar
DoCmd.RunSQL ("Delete * FROM Dagboek WHERE Datum = #01-01-" & Me.TmpJaar + 1 & "#;")


'tel het aantal record dat er op 1 januari in het nieuwe jaar zijn.
Aantalfilter = "[Jaarsamenvoeging].[datum] = #01-01-" & Me.TmpJaar + 1 & "#"


'Wijzig in de tabel Jaarsamenvoeging de datum in 1 januari jaar later
DoCmd.RunSQL ("UPDATE Jaarsamenvoeging SET datum = #01-01-" & Me.TmpJaar + 1 & "#;")

DoCmd.RunSQL ("UPDATE Jaarsamenvoeging SET NwNummer = nummer - (SELECT Min(nummer) FROM Jaarsamenvoeging);")

'***** Kopieer van tabel Jaarsamenvoeging naar tabel Dagboek *****
DoCmd.RunSQL ("INSERT INTO Dagboek ( Datum, Nummer, Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD ) SELECT Datum, NwNummer, Omschrijving, Bedrag, inkomsten, BTWA, BTWBC, DebetRekA, CreditRekA, DebetRekBC, CreditRekBC, DebetRekD, CreditRekD FROM Jaarsamenvoeging;")

DoCmd.RunSQL ("Delete * FROM Jaarsamenvoeging;")

End Sub

 

[Frits1969]

De oplossing heb ik zelf gevonden in de volgende regels:

Dim Min0101 As Integer
Min0101 = DMin("[nummer]", "Jaarsamenvoeging")
DoCmd.RunSQL ("UPDATE Jaarsamenvoeging SET NwNummer = Nummer - " & Min0101 & " + 1;")

Ik hoop dat ik hier iemand anders ook gelukkig mee kan maken.

 

[OctaFish]

Ik hoop dat ik hier iemand anders ook gelukkig mee kan maken.

Prima dat je het zo oplost, maar het blijft in mijn ogen een overbodige oplossing voor een overbodig probleem . Ik blijf erbij dat je beter een fatsoenlijke oplossing kunt bedenken/gebruiken. Ik hoop dan ook dat niemand deze oplossing over neemt .