Historie vastleggen via toevoegquery op datum

Status
Niet open voor verdere reacties.
R

rista62

Gebruiker
Lid geworden
25 nov 2009
Berichten
73
Hallo,
Ik heb een database opgezet (bijlage). Nu wil ik maandelijks historie gaan vastleggen vanuit de toevoegquery "qry002HistorieLeden" naar de tabel "tblHistorieLeden". In die tabel wil ik het aantal leden per jaar per maand bijhouden door er een periode aan te koppelen, het veld "JaarMaandNr".
Wat ik ook graag wil vastleggen in de historie is, op het moment van vastleggen in de historie hoelang (aantal mnd) iemand lid is. Dus aantal maanden dat iemand lid is op de periode 201004 en zo ook in de volgende maand 201005 enz. Dit wil ik graag vastleggen in de tblHistorieLeden
Weet iemand hoe ik dit vanuit de toevoegquery "qry002HistorieLeden" kan krijgen en vervolgens in de tabel "tblHistorieLeden" maandelijks kan vastleggen?

Gr. R.
 

Bijlagen

Met deze query:

INSERT INTO tblHistorieLeden ( pkLidNummer, Naam, DatumIn, DatumUit, Klasse, JaarMaandNr, AantalMaandenLid )
SELECT tblLedenDetail.pkLidNummer, tblLedenDetail.Naam, tblLedenDetail.DatumIn, tblLedenDetail.DatumUit, IIf(tblLedenDetail!DatumUit Is Null,"",Leeftijdsgroep(CDate([DatumIn]),CDate([DatumUit]))) AS Klasse, Year(Date())*100+Month(Date()) AS Periode, DateDiff("m",tblLedenDetail!DatumIn,tblLedenDetail!DatumUit) AS [#mnd]
FROM tblLedenDetail;
Klik om te vergroten...
 
Hoi Michel,
Ik zie even niet wat ik verkeerd doe en/of wat jij bedoelt?
Grag hoor ik van je.

Gr.
Rista
 
Hoi Rista,

Ik heb je query [qry002HistorieLeden] omgebouwd naar een Toevoegquery, die de gegevens toevoegt aan de Historietabel. Ik dacht dat je dat wilde?
Je kunt de SQL dus als een nieuwe query opslaan, of de oude SQL van je query vervangen.
 
Hoi Michel,

Ik ben niet duidelijk geweest, denk ik.
Wat ik graag wil vastleggen in de historie is, op het moment van vastleggen in de historie hoelang (aantal mnd) iemand lid is. Dus aantal maanden dat iemand lid is op de periode 201004 en zo ook in de volgende maand 201005 enz. Dit wil ik graag vastleggen in de tblHistorieLeden. Hoe doe ik dat met een formule in de query?

Gr.
Rista
 
Nu snap ik het vrees ik niet meer... Waarom zou je het aantal maanden willen vastleggen in een historietabel? Tenzij je met verschillende tijdschalen werkt, loopt de tijd toch voor iedereen synchroon? Ergo, als je de aanmelddatum weet, kun je toch altijd berekenen hoeveel maanden iemand lid is? Dat hoef je dan toch niet in een historietabel op te slaan?
 
Hoi Michel,
Ik wil dit vastleggen omdat het een centrale opslag moet worden waar meerdere mensen gebruik van gaan maken. Ik wil straks ook een klasse aan de tijd gaan hangen. Dus iemand < 12 mnd lid = beginneling enz. Hierdoor hebben we één definitie.
Dus ik wil per maand vastleggen dat bijv lid 1 in de maand april 2010 10 mnd lid is en in de mnd mei 2010 11 mnd enz.
Ik hoop dat e.e.a. duidelijker is.
Gr.
Rista
 
Die klasse-indeling kan in mijn ogen veel beter zonder dat je de maanden vastlegt in een historie-tabel; de periode wordt immers altijd herberekend. Maar het kan uiteraard wel, volgens mij kun je de query die ik heb gemaakt gewoon daarvoor gebruiken. Wat doet hij volgens jou niet, wat hij wel zou moeten doen?
 
Hoi Michel,

Allereerst fijn dat je zo mee denkt.
Hetgeen ik je laat zien is een stukje van een data, totale data heeft een paar 100 records per maand als historie.
Ik stuur je bij deze een exceldocument mee waarin je kunt zien wat ik uiteindelijk per maand als historie wil opslaan. Dit geldt alleen voor de leden die er nog zijn, dus niet de uitgestroomden.
Ik hoop dat dit alles iets duidelijker wordt, kun je me hiermee helpen?
Alvast weer bedankt.

Gr.
Rista
 

Bijlagen

Dan krijg je denk ik zoiets?

INSERT INTO tblHistorieLeden ( pkLidNummer, Naam, DatumIn, DatumUit, Klasse, JaarMaandNr, AantalMaandenLid )
SELECT tblLedenDetail.pkLidNummer, tblLedenDetail.Naam, tblLedenDetail.DatumIn, tblLedenDetail.DatumUit, IIf([DatumUit] Is Null,Leeftijdsgroep(CDate([DatumIn]),Date()),Leeftijdsgroep(CDate([DatumIn]),CDate([DatumUit]))) AS Klasse, Year(Date())*100+Month(Date()) AS Periode, DateDiff("m",[DatumIn],IIf([DatumUit] Is Null,Date(),[DatumUit])) AS [#mnd]
FROM tblLedenDetail;
Klik om te vergroten...
Overigens blijf ik het nut van zo'n historietabel niet zien; de gegevens worden automatisch gegenereerd op elke datum, dus je kunt de ontwikkeling van de Klasse en het aantal maanden op elk moment van de dag laten uitrekenen en oproepen....
Ik gebruik historietabellen om bijvoorbeeld vast te leggen wanneer iemand inlogt in een db, of een bepaalde handeling uitvoert. Maar je zal er een reden voor hebben ;)
 
Hoi Michel,

Mee eens, het is een afgeleide die je eigenlijk niet maandelijks wil vastleggen. Maar de historietabel wordt met name opgezet omdat er één item in zit die maandelijks kan veranderen en dat is Afdeling(hierin nog niet opgenomen). Vandaar de historietabel. Voor de gebruiker is het wellicht handiger dat Klasse en # maanden er in zitten.
Wat ik me nog afvraag is het volgende. Ik wil maandelijks de gegevens vastleggen van die maand. Dus in de Historietabel staan straks ook de gegevens van mei onder periode 201005. Gaat dit dan goed met #maanden. Ik bedoel de historie komt er dan zo uit te zien voor persoon Kamp, L. Ik wil dit doen met een toevoegquery.
pkLidNummer Naam DatumIn Klasse Periode #mnd
2 Kamp, L. 1-5-2007 Beginneling 201004 35
2 Kamp, L. 1-5-2007 Beginneling 201005 36

Of is er een alternatief om dit handiger te doen?
gr,
Rista
 
Hoi Michel,
Mocht je het antwoord weten op de vorige vraag, dan hoor ik graag van je.
Een ander punt waar ik tegen aan loop is hoe kan ik voorkomen dat ik 2 keer de historie van dezelfde maand opsla?
Groet,
Rista
 
Om te voorkomen dat je een record 2 keer toevoegt, moet je een criterium inbouwen in de toevoegquery. De constructie is ongeveer zoals deze:
Not in(SELECT id FROM Historietabel).
Alleen is de constructie bij jou iets ingewikkelder, omdat je niet op één veld kunt filteren.
Om te voorkomen dat ik een record dubbel toevoeg stuur ik de toevoegquery daarom meestal via een knop op een formulier aan, waarbij ik eerst controleer of een bepaalde veldencombinatie al bestaat. Als dat zo is, dan moet er uiteraard niks worden toegevoegd.
En dat ziet er dan ongeveer zo uit:

Code: 
iOpslaan = MsgBox("Wil je de huidige selectie opslaan?", vbYesNo + vbDefaultButton1, "Selectie opslaan")
If iOpslaan = 6 Then
    sQueryNaam = InputBox("Typ de naam voor de query", "Query opslaan...", "Export")
    With CurrentDb.OpenRecordset("Select UserID, QueryNaam FROM tQueries WHERE UserID='" _
        & sUserID & "' AND QueryNaam = '" & sQueryNaam & "'")
        If .RecordCount > 0 Then
            iOpslaan2 = MsgBox("Wil je de huidige query vervangen?", vbYesNo + vbDefaultButton2, "Query vervangen")
            If iOpslaan2 = 6 Then
                sQuery = "UPDATE tQueries SET QueryString = '" & strSQL & "', Datum= CDate(" & CDbl(Date) & "), " _
                    & "Tijd =#" & Format(Now, "HH:mm") & "#" & vbCrLf
                sQuery = sQuery & " WHERE (QueryNaam='" & sQueryNaam & "' AND UserID='" & sUserID & "');"
            End If
        Else
            sQuery = "INSERT INTO tQueries(UserID,Datum,Tijd,QueryNaam,QueryString)" & vbCrLf
            sQuery = sQuery & "VALUES('" & sUserID & "', CDate(" & CDbl(Date) & "), #" & Format(Now, "HH:mm") & "#, '" _
                & Nz(sQueryNaam) & "', '" & strSQL & "')"
            Me.cboQueries.Requery
        End If
        .Close
    End With
    DoCmd.SetWarnings False
    CurrentDb.Execute sQuery, dbFailOnError
    DoCmd.SetWarnings True
End If
In dit voorbeeld plaats ik eerst een Msgbox met de vraag of de query moet worden opgeslagen.
Vervolgens kijk ik of het record al bestaat (If .RecordCount > 0). Als dat zo is, dan wordt het record bijgewerkt. Bestaat het niet, dan wordt het record toegevoegd.
Als je de code snapt, kun je hem denk ik wel zelf verbouwen tot iets waar je wat aan hebt?
 
Hoi Michel,
Ik ben helaas nog een groentje, dus dit gaat me boven mijn pet.
Inhoudelijk begrijp ik niet wat de code regel voor regel doet. Klopt het dat de code regel voor regel checkt op dubbelen en elke keer de vraag stelt of die verder mag gaan? Dit hoop ik niet want bij een paar honderd records wordt dit wel lastig, jou kennende zal dit wel niet.
Wat ik verder niet snap is of je iets in de query doet om te verwijzen naar de code, of ga je in het formulier een knop maken met verwijzing naar de code en dan naar de query? Ik weet dus niet hoe je de code draaiende krijgt op de query?
Sorry, maar ik ben nog lerende dus vandaar deze beginnersvragen!

Gr.
Rista
 
Ik snap dat je het allemaal niet zo snapt ;) 't Is ook niet het makkelijkste voorbeeld... Omdat ik vanochtend eigenlijk geen tijd had, heb ik de volledige code geplakt die ik gebruik. Het bruikbare deel voor jou is dit:

Code: 
With CurrentDb.OpenRecordset("Select UserID, QueryNaam FROM tQueries WHERE UserID='" _
    & sUserID & "' AND QueryNaam = '" & sQueryNaam & "'")
    If .RecordCount = 0 Then
        sQuery = "INSERT INTO tQueries(UserID,Datum,Tijd,QueryNaam,QueryString)" & vbCrLf
        sQuery = sQuery & "VALUES('" & sUserID & "', CDate(" & CDbl(Date) & "), #" & Format(Now, "HH:mm") & "#, '" _
            & Nz(sQueryNaam) & "', '" & strSQL & "')"
    .Close
    DoCmd.SetWarnings False
    CurrentDb.Execute sQuery, dbFailOnError
    DoCmd.SetWarnings True
    End If
End With
Ik zal de onderdeeltjes even uitleggen:
Met With CurrentDb.OpenRecordset("SELECT [Velden] FROM [Tabel] WHERE [Veld1]= etc") open je een query die is gebaseerd op de velden die je wilt toevoegen. Je vergelijkt daarin de velden in de tabel met de waarden die je net hebt ingevuld, en dat doe je door te verwijzen naar de betreffende velden op je formulier; in mijn voorbeeldcode heb ik de formuliervelden overigens toegewezen aan Variabelen. In mijn voorbeeld is dat een tabel tQueries, in jouw geval uiteraard de Historietabel.
Vervolgens kijk je met de regel If .RecordCount = 0 Then of het specifieke record al voorkomt. Als de waarde RecordCount Nul is, bestaat het record niet, en kun je het gaan toevoegen.
Ik maak vervolgens eerst een Variabele aan met de juiste SQL-opdracht, dus een INSERT INTO query.
Als laatste stap wordt de query uitgevoerd met het commando CurrentDb.Execute sQuery, dbFailOnError
Daarboven en onder zie je nog twee opdrachten staan die de dialoogvensters onderdrukken, maar die zijn niet noodzakelijk.
Is-tie wat duidelijker?
 
Laatst bewerkt:
Hoi Michel,

2 probleemvraagjes:
Ik loop tegen een probleem aan. Ik wil van de selectiequery "qry002HistorieLeden" een toevoegquery maken, met als doeltabel "tblHistorieLeden". Als ik in de ontwerpweergave van de query "qry002HistorieLeden" kies voor querytype toevoegquery dan komt bij de tabelnaam "tblHistorieLeden" niet voor terwijl deze tabel er wel moet staan. Hoe kan dat zie bijlage?

Ander punt is het stukje Code, ik ben er mee aan het stoeien geweest, maar na een tijd zoeken geef ik de moed op. Ik raak al in de knoop bij With CurrentDb.OpenRecordset("SELECT [Velden] FROM [Tabel] WHERE [Veld1]= etc"). Ik kan niet achterhalen wat ik hier moet opgeven.
Ik hoop dat je me kunt helpen met deze 2 punten.
Alvast dank weer.

Gr.
Rista
 

Bijlagen

Zal er vandaag naar proberen te kijken, maar ik heb een drukke dag, dus vandaag zal er niet veel van komen.
 
OK,
Ik ben al lang blij dat je me wilt helpen, ik zie het tegemoet.
Gr.
Rista
 
Hoi Michel,
Heb je nog tijd gehad om naar mijn probleem te kijken of ...?

Gr.
Rista
 
Tis een tikkie druk geweest... Maar ik probeer nog een gaatje vrij te maken dit weekend ;)
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan