Berekening som en verschil meerdere velden

  • Onderwerp starter Onderwerp starter astra
  • Startdatum Startdatum
Status
Niet open voor verdere reacties.
A

astra

Gebruiker
Lid geworden
28 jul 2003
Berichten
127
Goeiedag,

In de database heb ik een subform gebaseerd op een query.
In die subform wil ik een berekening maken van de som van meerdere velden en ook het verschil.

Om te verduidelijken wat voor berekening ik wens voeg ik hierbij het Excel + Access bestand in als bijlage.
Vroeger werd de opvolging in Excel gedaan maar heb nu alles overgebracht naar Access.
In Excel was het makkelijk om die berekening te maken maar heb me al suf gezocht hoe dit moet in Access en raak er maar niet uit.
Ik hoop dat jullie me verder kunnen helpen.

Alvast hartelijk bedankt bij voorbaat.
Astra

Bekijk bijlage Opvolging1.zip
 
Hallo Astra,

Probeer deze code eens als Recordbron van het subformulier "subfrmCm":

Code: 
SELECT tblCm.KlantID, tblCm.CmID, tblCm.Datum, tblCm.Uur, tblCm.Borst, tblCm.Taille, tblCm.Buikomtrek, tblCm.Heup, tblCm.ArmR, tblCm.ArmL, tblCm.DijR, tblCm.DijL, tblCm.KuitR, tblCm.KuitL, 
Sum(Nz([Borst],0)+Nz([Taille],0)+Nz([Buikomtrek],0)+Nz([Heup],0)+Nz([ArmR],0)+Nz([ArmL],0)+Nz([DijR],0)+Nz([DijL],0)+Nz([KuitR],0)+Nz([KuitL],0)) AS Totaal, 
IIf([VorigTotaal] Is Null,0,[Totaal]-[VorigTotaal]) AS Verschil, 
(SELECT TOP 1 ([Borst]+[Taille]+[Buikomtrek]+[Heup]+[ArmR]+[ArmL]+[DijR]+[DijL]+[KuitR]+[KuitL])  
FROM tblCm AS Tdl WHERE Tdl.Datum < tblCm.Datum  
ORDER BY Tdl.Datum DESC) AS VorigTotaal
FROM tblCm
GROUP BY tblCm.KlantID, tblCm.CmID, tblCm.Datum, tblCm.Uur, tblCm.Borst, tblCm.Taille, tblCm.Buikomtrek, tblCm.Heup, tblCm.ArmR, tblCm.ArmL, tblCm.DijR, tblCm.DijL, tblCm.KuitR, tblCm.KuitL;

Een vergelijkbare code heb ik ook al eens van dit forum geplukt.

Ik zou in de tabel "tblCm" het gegevenstype van het veld "uur" veranderen in een datum/tijd type. De notatie kies je dan als "Korte tijdnotatie".
Hopelijk kun je er wat mee.

Succes

Gr. Jan
 
Beste jhdw

Bedankt voor je vlugge reactie.
De berekening wordt wel gemaakt maar probleem is dat ik de subfrmCm niet meer kan aanvullen met nieuwe opvolgingen (nieuwe datum, uur,...)
M.a.w. ik zit vast.
Nog een suggestie of oplossing voor dit probleem?

mvg
Astra
 
Beste OctaFish,

Hierbij een screenshot van hoe het formulier er uitziet.
Ik kon niet de ganse database doorsturen omdat deze te groot was.
Ik ben maar een amateur maar wel liefhebber van Access en zie niet in hoe ik dit moest oplossen zonder subforms te gebruiken.

Jan, ik heb het veld met uur veranderd. Bedankt om me hier attent op te maken.

Mvg
Astra

Screen Shot 10-04-16 at 12.01 AM.jpg
 

Bijlagen

  • Screen Shot 10-03-16 at 11.30 PM.jpg
    Screen Shot 10-03-16 at 11.30 PM.jpg
    90,4 KB · Weergaven: 63
Laatst bewerkt:
Soms heb je een UDF (eigen functie) nodig. Althans: zo doe ik het. Overigens was de Group BY van de query van jhdw niet nodig. En maakt dat verder geen verschil, want ook zonder de Group BY is de query niet bij te werken. En dat was het probleem: door de extra berekening was de query op slag niet meer bij te werken.
 

Bijlagen

Beste OctaFish,

Heel hartelijk bedankt voor de oplossing. :thumb:
Ben echt onder de indruk.
Dit is het werk van een meester!

mvg
Astra
 
Niet overdrijven :o . Maar het is een leuke oplossing, vind ik zelf ook wel :) . Mag je de vraag nog op <Opgelost> zetten, als het zo goed genoeg werkt tenminste.
 
Beste OctaFish,

Je formule werkt perfect! En niks overdreven, ben ervan overtuigd dat vele Accessers hier volledig eens mee zijn.
Ik zet dit nu op "opgelost"
Nog een heel fijne dag!
Astra
 
Beste OctaFish,

Nu pas heb ik opgemerkt dat bij het aanmaken van een nieuw record (nieuwe klant), het totaal van de vorige klant wordt overgebracht naar de nieuwe klant ipv te herstarten met 0
Kan je dit nog even nazien?
Met veel dank bij voorbaat.
Astra
 
En daarom is het zo belangrijk dat je voorbeelden aanlevert met representatieve records :). Een voorbeeldje met één klant, en een aantal subrecords voor die ene klant kan nooit de werkelijkheid nabootsen. Tenzij je, wat voorkomt, maar één klant hebt natuurlijk. De kok in Buckingham Palace bijvoorbeeld heeft maar één klant. Je zult inderdaad een kleine aanpassing moeten maken. Voor de functie is dat deze:
Code: 
Function VorigeWaarde(ID As Long, k_ID) As Integer
Dim strSQL As String
Dim iW As Integer, iT As Integer
Dim rs As DAO.Recordset
On Error GoTo Hell

    strSQL = "SELECT (Nz([Borst],0)+Nz([Taille],0)+Nz([Buikomtrek],0)+Nz([Heup],0) " _
        & "+Nz([ArmR],0)+Nz([ArmL],0)+Nz([DijR],0)+Nz([DijL],0)+Nz([KuitR],0)+Nz([KuitL],0)) As Totaal, " _
        & "CInt(Nz((SELECT TOP 1 (Nz([Borst],0)+Nz([Taille],0)+Nz([Buikomtrek],0)+Nz([Heup],0) " _
        & "+Nz([ArmR],0)+Nz([ArmL],0)+Nz([DijR],0)+Nz([DijL],0)+Nz([KuitR],0)+Nz([KuitL],0)) " _
        & "FROM tblCm AS Dupe WHERE Dupe.Datum < tblCm.Datum AND Dupe.KlantID = tblCm.KlantID " _
        & "ORDER BY Dupe.Datum DESC),0)) AS VorigTotaal " _
        & "FROM tblCm WHERE tblCm.CmID = " & ID
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    With rs
        iT = rs.Fields(0).Value
        iW = rs.Fields(1).Value
        .Close
    End With
    VorigeWaarde = iW
    Exit Function

Hell:
VorigeWaarde = 0
End Function

Voor de query versie is dat deze:
Code: 
VorigTotaal: (SELECT TOP 1  ([Borst]+[Taille]+[Buikomtrek]+[Heup]+[ArmR]+[ArmL]+[DijR]+[DijL]+[KuitR]+[KuitL])  
FROM tblCm AS Tdl WHERE Tdl.Datum < tblCm.Datum AND Tdl.KlantID = tblCm.KlantID 
ORDER BY Tdl.Datum DESC)
 
Ja OctaFish, nu ondervind ik hoe belangrijk het is om een voorbeeld met representatieve records door te sturen.
Ik heb er te laat aan gedacht dat er getallen kunnen voorkomen met cijfers na de komma.
Dus heb ik nu de numeriek velden in tblCm veranderd naar Dubbele precisie ....met alle gevolgen vandien.
De berekeningen kloppen niet meer want bij "Vorige waarde" staan de getallen afgerond.
Ik heb gezocht om dit aan te passen maar als leek vind ik niet hoe of waar ik dat moet veranderen naar NIET afgeronde getallen.
Voorbeeld:
Screen Shot 10-19-16 at 12.39 AM.JPG

-11,5 moet 11,4 zijn
-10,1 moet 9,6 zijn

Verder heb ik de functie en de query veranderd zoals je had aangegeven.
Maar ik kreeg foutmeldingen.
Van alles geprobeerd zonder resultaat en uiteindelijk de query in oorspronkelijke staat teruggezet
En tot mijn grote verrassing ging het nu wel!
Een nieuw record start dus weer vanaf 0 :)
Dus voor alle duidelijkheid: enkel de functie is vervangen geweest.

Maar nu zit ik dus wel terug met een probleem ivm berekening.
Mag ik nogmaals een beroep doen op jou doen om me hierbij te helpen?

Ik kan de database niet als bijlage invoegen omdat men maar tot 100kb kan doorsturen.

Met veel dank bij voorbaat
Astra
 
De database kun je eerst comprimeren en dan zippen, dat scheelt meestal al behoorlijk. Alternatief: als de zip nog steeds te groot is, hernoem hem dan naar .xlsb, want dat type mag 1Mb zijn. Mag natuurlijk ook al gelijk bij de db, als die onder de 1MB zit. Wij hernoemen hem wel terug naar .zip of .accdb, afhankelijk van wat je meestuurt.
 
OctaFish,

In bijlage de gezipte database Opvolging2
Ik heb de 4 pagina's verwijderd omdat deze niks te maken hebben met de berekening en om de database zo klein mogelijk te houden.
Maar zelfs na comprimeren en gezipt te hebben toch nog 206kb.
Het zip bestand dus gewijzigd naar .xlsb

Best groetjes,
Astra
 

Bijlagen

En nu nog het wachtwoord :).
 
Heel eigenaardig OctaFish want ik heb geen wachtwoord ingesteld.
Wat nu???
Zal proberen om de database nog te verkleinen zodat ik deze kan doorsturen als db maar dan met xlsb

Groetjes,
Astra
 
De query qryCM kun je makkelijk aanpassen, zodat hij de juiste waarden uitrekent. Omdat je dan direct met de waarden werkt, heb je geen last van de opmaak.
Code: 
SELECT tblCm.KlantID, tblCm.CmID, tblCm.Datum, tblCm.Uur, tblCm.Borst, tblCm.Taille, tblCm.Buikomtrek, tblCm.Heup, tblCm.ArmR, tblCm.ArmL, tblCm.DijR, tblCm.DijL, tblCm.KuitR, tblCm.KuitL, 
(Nz([Borst],0)+Nz([Taille],0)+Nz([Buikomtrek],0)+Nz([Heup],0)+Nz([ArmR],0)+Nz([ArmL],0)+Nz([DijR],0)+Nz([DijL],0)+Nz([KuitR],0)+Nz([KuitL],0)) AS Totaal, 
(SELECT TOP 1 ([Borst]+[Taille]+[Buikomtrek]+[Heup]+[ArmR]+[ArmL]+[DijR]+[DijL]+[KuitR]+[KuitL])  
FROM tblCm AS Tdl WHERE (Tdl.Datum < tblCm.Datum AND Tdl.KlantID = tblCm.KlantID) 
ORDER BY Tdl.Datum DESC) AS VorigTotaal, IIf([VorigTotaal] Is Null,0,[Totaal]-[VorigTotaal]) AS Verschil
FROM tblCm;

De functie moet je aanpassen; ik heb daar nu variabelen gedeclareerd als Integer, en dat moet eigenlijk niet; een Integer pakt de decimalen namelijk niet. Je moet functie en variabelen dus declareren als Double. Dan gaat het wel goed.
 
Beste OctaFish,

Is dit nog op te lossen???

Groetjes,
Astra
 
Ik heb er door omstandigheden nog geen tijd voor gehad; ik zal proberen er vanavond een blik op te werpen.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan