[MS Access SQL] Recursieve query of gebruik van variabelen?

[Knakker]

Ik wil een query uitvoeren in Access, waarvan ik niet weet of dit mogelijk is. Ik hoop dat iemand van jullie er een licht over kan laten schijnen.

Ik heb een tabel (> 6 miljoen regels) met daarin: rekening, datum, beginsaldo, inflow en outflow. Simpel voorbeeldje is onderstaande tabel.

Rekening	Datum	Balans	Inflow	Outflow
A	01-Jan-09	1000	100	0
A	01-Feb-09	1100	0	-50
A	01-Mar-09	1050	0	-125
A	01-Apr-09	925	150	0
A	01-May-09	1075	25	0
A	01-Jun-09	1100	0	-200
A	01-Jul-09	900	0	-100

We zijn een model aan het bouwen waarmee we alleen de outflow gaan modelleren. Om dat zo goed mogelijk te doen moeten we balans vanaf een bepaalde datum alleen gaan corrigeren voor de outflow en niet meer voor de inflow. Dat betekent dus dat de balans op t+1 gelijk moet worden aan de balans op t minus de outflow op t. In het geval van de bovenstaande tabel zou ik dus dit willen creeren:

Rekening	Datum	Balans	Inflow	Outflow
A	01-Jan-09	1000	100	0
A	01-Feb-09	1000	0	-50
A	01-Mar-09	950	0	-125
A	01-Apr-09	825	150	0
A	01-May-09	825	25	0
A	01-Jun-09	825	0	-200
A	01-Jul-09	625	0	-100
…	…	…	…	…

Is dit mogelijk met een query in Access? Het enige dat ik kan bedenken is door een variabele te gebruiken die de balans per rekening bijhoudt -en dat kan bij mijn weten niet in Access- of anders door te joinen op het resultaat tot nu toe - waarvan ik ook niet zou weten of dat kan.

Ik heb reeds gepoogd het bovenstaande te creëren middels een stukje VBA en dat gaat prima voor kleine hoeveelheden records maar door de hoeveelheid records gaat Access helaas volledig over de zeik.

Heeft iemand een suggestie? Stel dat dit in Access niet mogelijk is, hoe zou ik het dan moeten doen als ik deze data naar MS SQL Server zou verplaatsen (niet de voorkeur, maar mogelijk)?

Ontzettend bedankt voor de hulp!

[ Voor 4% gewijzigd door Knakker op 17-02-2010 15:23 ]

[RedRose]

Als je de beginbalans weet, kan je toch in een bepaald tijdsinterval alle outflows bij elkaar optellen en dat van de beginbalans aftrekken? Of denk ik nu te simpel? Dat is in ieder geval wel hoe veel boekhoudpakketten het doen.

Hoe dan ook, mocht je de balansen toch willen opslaan, dan ontkom je volgens mij niet aan meerdere queries na elkaar: lezen (en dan optellen / aftrekken) en updaten.

Beside that kan je beter de MSDE gaan gebruiken icm VS2008 / 2010. Das allemaal niet veel moeilijker, gratis en kan wat meer aan dan Access in het geval van 6 miljoen records.

[Knakker]

RedRose schreef op woensdag 17 februari 2010 @ 15:33:
Als je de beginbalans weet, kan je toch in een bepaald tijdsinterval alle outflows bij elkaar optellen en dat van de beginbalans aftrekken? Of denk ik nu te simpel? Dat is in ieder geval wel hoe veel boekhoudpakketten het doen.

Hoe dan ook, mocht je de balansen toch willen opslaan, dan ontkom je volgens mij niet aan meerdere queries na elkaar: lezen (en dan optellen / aftrekken) en updaten.

Voor één bepaalde datum klopt dat, ja. Alleen we moeten voor elke maand waarvoor we data hebben de nieuwe balans uitrekenen en opslaan (ook omdat we vervolgens daarop weer allerlei andere berekeningen uitvoeren). Ik vermoed dat een query in de trant van

code:

UPDATE [Tabel] As T SET T.Balans = (SELECT Balans FROM [Tabel] WHERE ID = T.ID-1 AND etcetcetc) - T.Output

ook niet vooruit te branden zal zijn...

Ik zou nog kunnen proberen het stukje VBA code op te bouwen uit update queries (nu werk ik met het recordset object dan ik aanpas en uiteindelijk update): als ik binnen een transactie een update query run die een record opzoekt welke binnen dezelfde transactie eerder is bijgewerkt, krijg ik dan wel de nieuwe waarde?

Beside that kan je beter de MSDE gaan gebruiken icm VS2008 / 2010. Das allemaal niet veel moeilijker, gratis en kan wat meer aan dan Access in het geval van 6 miljoen records.

Ik zou dat wel kunnen. Probleem is alleen dat mijn collega's dan afhaken en dat is niet wenselijk - niemand kan dan mijn werk verifiëren en niemand de analyse overnieuw doen als ik er niet (meer) ben. Access blijft dus echt de voorkeur houden.

Enfin, als er nog suggesties zijn hoor ik dat graag

[ Voor 11% gewijzigd door Knakker op 17-02-2010 16:19 ]

[pasz]

RedRose schreef op woensdag 17 februari 2010 @ 15:33:
Beside that kan je beter de MSDE gaan gebruiken icm VS2008 / 2010. Das allemaal niet veel moeilijker, gratis en kan wat meer aan dan Access in het geval van 6 miljoen records.

Volledig mee eens. Access en Excel zouden verboden moeten worden.

On Topic :

Dit is jouw werk ? Met andere woorden heb jij deze tabel verzonnen ?
Of is dit nog aan te passen ?

Probleem is namelijk dat deze database niet genormaliseerd is en gegevens er in principe dubbel in staan.
Het volgende record is afhankelijk van een ander en dat is niet gewenst in een database.

[pedorus]

Knakker schreef op woensdag 17 februari 2010 @ 15:20:
Ik heb reeds gepoogd het bovenstaande te creëren middels een stukje VBA en dat gaat prima voor kleine hoeveelheden records maar door de hoeveelheid records gaat Access helaas volledig over de zeik.

6 miljoen is niet veel records, en volgens mij ook voor Access niet (of misschien heb je zeer grote velden, maar ook dan lijkt het mij geen probleem). Er zit dus waarschijnlijk iets mis in deze code. Zou je deze code kunnen posten?

[Grijze Vos]

Volgens mij is 6M best veel voor access hoor.

[pedorus]

Ok, het begint in de buurt te komen. Dit zijn zo'n 5 ints * 4 bytes=20 bytes + zeg 30 voor overhead+indices=50 bytes/record. Bij 6M records is dat dan zo'n 0.3 GB, terwijl 1 GB het maximum is voor een tabel. Het kan dus nog 3 keer zo groot.

De orde van grootte begint wel te komen, maar ik heb wel grotere databases gezien in Access. Dus eigenlijk zie ik bij goede code geen grote problemen, maar die andere grotere databases waren wel exports, en inderdaad niet op deze manier bewerkt. Desnoods zet je de geupdate tabel in een aparte database (of je pakt gewoon toch even de Express-versie van Server (Compact) en haalt daarna de boel weer terug naar Access).

[RobIII]

pedorus schreef op woensdag 17 februari 2010 @ 17:01:
terwijl 1 GB het maximum is voor een tabel

Beetje genuanceerd:

quote: http://en.wikipedia.org/wiki/Microsoft_Access

Generally accepted limits are solutions with 1 GB or less of data (Access supports up to 2 GB) and 50 or fewer simultaneous users.

quote: http://office.microsoft.com/en-us/access/HA100307391033.aspx

Table size: 2 gigabyte minus the space needed for the system objects

Maar daarmee probeer ik zeker het gebruik van Access goed te praten; ik blijf er zelf altijd graag uit de buurt zeg maar

[d00d]

Ik heb even getwijfeld of ik mijn oplossing aan de wereld kenbaar moet maken maar heb besloten om het toch maar te doen, ik hoor graag wat jullie ervan vinden. In SQL Server (2005 en hoger) kun je het volgende doen:

SQL:

USE tempdb;
GO

IF OBJECT_ID(N'#Flow', N'U') IS NOT NULL
    DROP TABLE #Flow;

CREATE TABLE #Flow
(
    Rekening CHAR(1) NOT NULL,
    Datum DATETIME NOT NULL,
    Balans MONEY NOT NULL,
    Inflow MONEY NOT NULL,
    Outflow MONEY NOT NULL
);
INSERT INTO #Flow VALUES 
('A', '1/1/2009', 1000, 100, 0),
('A', '2/1/2009', 1100, 0, -50),
('A', '3/1/2009', 1050, 0, -125),
('B', '1/1/2009', 4000, 250, 0),
('A', '4/1/2009', 925, 150, 0),
('A', '5/1/2009', 1075, 25, 0),
('B', '2/1/2009', 4250, 0, -11),
('A', '6/1/2009', 1100, 0, -200),
('B', '5/1/2009', 4239, 300, 0),
('A', '7/1/2009', 900, 0, -100);

CREATE NONCLUSTERED INDEX ci_flow ON #Flow (Rekening, Datum, Balans, Outflow);

DECLARE @Balans MONEY, @Outflow MONEY, @Rekening CHAR(1);

UPDATE #Flow
SET @Balans=Balans=(CASE WHEN @Rekening <> Rekening THEN
                        Balans
                    ELSE
                        COALESCE(@Balans, Balans)+COALESCE(@Outflow, Outflow)
                    END),
                    @Outflow=Outflow,
                    @Rekening=Rekening
FROM #Flow WITH (INDEX(ci_flow))
OPTION (MAXDOP 1)
GO
SELECT * FROM #Flow
ORDER BY Rekening, Datum;
GO

Ik weet niet of dit in Access ook werkt want daar heb ik geen verstand van.
Hierbij wordt gebruik gemaakt van multiple assignment in de update clause.
Het is bij een update statement niet mogelijk om een 'order by' op te geven, maar door het maken van een index en die als table hint mee tegen kun je er toch (redelijk) zeker van zijn dat het resultaat goed is.

De MAXDOP optie is om ervoor te zorgen dat het statement niet parallel wordt uitgevoerd.

Ranzig? Mooi? Beide?
In ieder geval razendsnel want set-based...

[d00d]

Voor de volledigheid, hier nog even het resultaat:

Rekening	Datum	Balans	Inflow	Outflow
A	2009-01-01	1000.00	100.00	0.00
A	2009-02-01	1000.00	0.00	-50.00
A	2009-03-01	950.00	0.00	-125.00
A	2009-04-01	825.00	150.00	0.00
A	2009-05-01	825.00	25.00	0.00
A	2009-06-01	825.00	0.00	-200.00
A	2009-07-01	625.00	0.00	-100.00
B	2009-01-01	4000.00	250.00	0.00
B	2009-02-01	4000.00	0.00	-11.00
B	2009-05-01	3989.00	300.00	0.00

[_js_]

Iets als

SQL:

SELECT C.*, (
  SELECT Balans FROM Tabel AS A WHERE Datum = (
    SELECT MIN(Datum) FROM Tabel AS B WHERE A.Rekening = B.Rekening)
  AND A.Rekening = C.Rekening) + COALESCE(SUM(D.Outflow),0)
FROM Tabel AS C
LEFT JOIN Tabel AS D ON C.Rekening = D.Rekening AND D.Datum < C.Datum
GROUP BY C.Rekening, C.Datum, C.Balans,C.Inflow,C.Outflow
ORDER BY C.Rekening, C.Datum;

had ook gekund. (In Access coalesce vervangen door iets met iif en isnull)

[Knakker]

pasz schreef op woensdag 17 februari 2010 @ 16:22:
[...]

Dit is jouw werk ? Met andere woorden heb jij deze tabel verzonnen ?

Niet zo snel conclusies trekken Nee, dit is niet mijn werk - ik maak (onder andere) wiskundige/financiële modellen. Je hebt absoluut gelijk voor wat betreft (het gebrek aan) tabelstructuur; ik heb hem alleen zo aangeleverd gekregen en deze komt helaas rechtstreeks uit de productieomgeving van een grote financiële instelling.

d00d schreef op woensdag 17 februari 2010 @ 21:20:
Ik heb even getwijfeld of ik mijn oplossing aan de wereld kenbaar moet maken maar heb besloten om het toch maar te doen, ik hoor graag wat jullie ervan vinden. In SQL Server (2005 en hoger) kun je het volgende doen:
[...]

Ziet er goed uit! Ik wil me sowieso wel meer verdiepen in de mogelijkheden van een fatsoenlijke database server dus ik zal me hier binnenkort eens op storten.

_js_ schreef op donderdag 18 februari 2010 @ 05:10:
Iets als

SQL:

SELECT C.*, (
  SELECT Balans FROM Tabel AS A WHERE Datum = (
    SELECT MIN(Datum) FROM Tabel AS B WHERE A.Rekening = B.Rekening)
  AND A.Rekening = C.Rekening) + COALESCE(SUM(D.Outflow),0)
FROM Tabel AS C
LEFT JOIN Tabel AS D ON C.Rekening = D.Rekening AND D.Datum < C.Datum
GROUP BY C.Rekening, C.Datum, C.Balans,C.Inflow,C.Outflow
ORDER BY C.Rekening, C.Datum;

had ook gekund. (In Access coalesce vervangen door iets met iif en isnull)

Hij werkt! En eigenlijk helemaal niet moeilijk. Ontzettend bedankt

_{Zal morgen kijken of de database dit een beetje trekt...}

[ Voor 7% gewijzigd door Knakker op 18-02-2010 13:24 ]

[d00d]

_js_ schreef op donderdag 18 februari 2010 @ 05:10:
Iets als

SQL:

SELECT C.*, (
  SELECT Balans FROM Tabel AS A WHERE Datum = (
    SELECT MIN(Datum) FROM Tabel AS B WHERE A.Rekening = B.Rekening)
  AND A.Rekening = C.Rekening) + COALESCE(SUM(D.Outflow),0)
FROM Tabel AS C
LEFT JOIN Tabel AS D ON C.Rekening = D.Rekening AND D.Datum < C.Datum
GROUP BY C.Rekening, C.Datum, C.Balans,C.Inflow,C.Outflow
ORDER BY C.Rekening, C.Datum;

had ook gekund. (In Access coalesce vervangen door iets met iif en isnull)

Dit geeft niet het juiste resultaat, namelijk:

Rekening	Datum	Balans	Inflow	Outflow	(No column name)
A	2009-01-01	1000.00	100.00	0.00	1000.00
A	2009-02-01	1100.00	0.00	-50.00	1000.00
A	2009-03-01	1050.00	0.00	-125.00	950.00
A	2009-04-01	925.00	150.00	0.00	825.00
A	2009-05-01	1075.00	25.00	0.00	825.00
A	2009-06-01	1100.00	0.00	-200.00	825.00
A	2009-07-01	900.00	0.00	-100.00	625.00

De inflow kolom wordt op deze manier bij het balans opgeteld, en dat was niet de bedoeling.
De laatste kolom geeft wel het juiste resultaat.
Mooie oplossing js...

[Knakker]

Over de Access discussie: ik snap dat ik de knuppel in het hoenderhok gooi hier op GoT maar puur vanuit functioneel oogpunt is het gebruik van Access in een aantal toepassingen wel te rechtvaardigen.

Het grootste voordeel van Access is namelijk dat het door zijn geïntegreerde gebruikersinterface een stuk toegankelijker is dan een normale database. Je kunt een met een paar keer klikken al een behoorlijke functionaliteit bewerkstelligen. Bovendien kun je de 'database' in z'n geheel op een stick zetten en ergens anders openen en het werkt meteen - zonder allerlei software te moeten installeren (dit is ook meteen een belangrijk veiligheidsprobleem, maar dat terzijde). Waar het om gaat is dat voor relatief eenvoudige zaken en analyses, die een beperkt aantal keren uitgevoerd moeten worden, de ontwikkelkosten van een volwaardige, stabiele applicatie voor het gros van de mensen gewoonweg geen optie zijn. Zo ook in mijn geval, waar Access meer een éénmalig middel is (om het juiste overzicht te creëren) dan een eindoplossing.

Ik ben altijd wel fel tegen het gebruik van Access als 'tooltje' waarmee dag in dag uit productiewerk gedaan wordt. Helaas kom ik dit in mijn werk (bij wijze van spreken) dagelijks tegen, en de meeste zijn dan bovendien ook nog opgezet door mensen die echt geen flauw benul hebben van zelfs maar de basics van databases. Gelukkig zijn dat ook ideale kandidaten om implementatietrajecten met 'echte' ITers op te zetten.

[RedRose]

Op zich ben ik dat wel met je eens, zeker als het eenmalig is. Ik heb dat vroegah zelf ook veel gedaan voor eenmalige conversies en prototyping voor het MSDE tijdperk zeg maar, ook al heb ik een hartgrondige hekel aan VBA.

Laat wel onverlet dat als je een jaar verder bent, Access kan gaan hangen . Tweede punt is dat het moelijk is om (referentiele) integriteit echt goed te waarborgen natuurlijk.

Verder vind ik d00d's oplossing best elegant, alhoewel ik denk ik een set in zou lezen in code en dan daar de berekeningen doen en dan transactioneel te updaten.

[pasz]

[...]
Knakker schreef op donderdag 18 februari 2010 @ 13:13:
[...]

Niet zo snel conclusies trekken Nee, dit is niet mijn werk - ik maak (onder andere) wiskundige/financiële modellen. Je hebt absoluut gelijk voor wat betreft (het gebrek aan) tabelstructuur; ik heb hem alleen zo aangeleverd gekregen en deze komt helaas rechtstreeks uit de productieomgeving van een grote financiële instelling.

Het was zeker niet mijn bedoeling om negatief over te komen. Ik snap het. Toch niet als CSV file he ?

Hoe ziet de toekomst van deze applicatie er uit ? Wordt het ooit nog een 'professionele' applicatie ipv een Access toepassing ?

[pedorus]

Ik heb dit net toch even uitgetest op 6 mln records:

offtopic:
moest toch iets doen tijdens het tv-kijken

Visual Basic:

Sub test()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim account As Long
    Dim balans As Long
    Dim i As Long
    account = -1
    DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select * from account order by db, dat")
    Do While Not rs.EOF
        i = i + 1 'debug info for manual breaks
        If (account = rs!db) Then
            rs.Edit
            rs!balans = balans
            rs.Update
            balans = balans + rs!outflow
        Else
            account = rs!db
            balans = rs!balans + rs!outflow
        End If
        rs.MoveNext
    Loop
End Sub

Ok, het kost misschien maximaal 10 minuten ofzo, en die tabel is 265 mb, maar ik zie het probleem niet?

Generatiecode (later nog wel even de primary key goed gezet en helaas niet-mooie naamgeving):

Visual Basic:

Sub generate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long

    Set db = CurrentDb()
    db.Execute "create table account(db int, dat date," & _
        "balans int, inflow int, outflow int)"
    Set rs = db.OpenRecordset("account")
    For i = 1 To 6000000
        rs.AddNew
        rs!db = i \ 100
        rs!dat = i Mod 100 + Now() - 200
        rs!balans = 2000
        If i Mod 2 = 1 Then
            rs!inflow = 42
            rs!outflow = 0
        Else
            rs!inflow = 0
            rs!outflow = -42
        End If
        rs.Update
    Next
End Sub