Op zoek naar groepering van gegevens

Status
Niet open voor verdere reacties.
R

RadboudAKF

Gebruiker
Lid geworden
3 nov 2010
Berichten
219
Gegevens samenvoegen/groeperen.

Ik heb een ACCESS-toepassing waarin gegevens staan over onze eigen Bereidingen in de Apotheek. In de tabel staan: Stofnaam, Bereidingsdatum, Bereidingstijdstip.

We willen bereidingen met dezelfde stofnaam én bereidingsdatum gaan groeperen. (we noemen dat een BATCH) Per groepering worden dan een aantal basiskosten vastgesteld.

Als een bereiding (met dezelfde stofnaam en dezelfde bereidingsdatum) ook binnen 60 minuten bereidingstijd vallen dan willen we die groeperen en betitelen als bv BATCH Nr1 (in zo'n BATCH worden dan bepaalde kosten maar één keer berekend)

Ik wil graag een query (queries) bouwen die op zoek gaat naar die batches. De moeilijkheid hierbij is dat:

1. Ik moet eerst op zoek moet naar de allereerste bereiding op een dag (in ons voorbeeld neem ik maar even Carboplatine op 5-1-2016 ... eerste bereidingstijd is 8:18) -

2. Alle bereidingen die dan binnen een uur van 8:18uur vallen worden dan "BATCH nr. 1)

3.Vervolgens ga ik op zoek naar de eerstvolgende kleinste bereidingstijd die buiten BATCH nr. 1 valt ...

4. en ga vervolgens weer op zoek naar alle bereidingen die binnen 60minuten van die bereidingstijd vallen en noem die BATCH nr.2

5. enz, enz (ik wil ga tot 5 batches op een dag.

Ik heb in mijn voorbeeld (in een EXCEL-bestand) vast het resultaat gezet dat ik (zonder queries) vaststelde voor Carboplatine - bereid - 4januari 2016) Daar rolden 5 batches uit.

Het lukt niet goed om de stap naar de opvolgende batches te maken ...

Ik hoop dat ik mijn probleem helder voor het voetlicht breng ... want ik zou het geweldig vinden als iemand mij hier een stukje op weg kan helpen.

Groet,

Jan
AKA Radboudakf
 

Bijlagen

  • BereidingsBatch.xlsx
    15,6 KB · Weergaven: 44
Ik denk dat je een aparte tabel nodig hebt om de groepen aan te maken, want met één query lukt het (voorlopig) nog niet. Omdat ik niet bij mijn voorbeeldbestanden kan (OneDrive lijkt er uit te liggen) kan ik vanavond pas een echt voorbeeldje maken.
 
Ik heb al wel vast een query voor je waarmee je de vorige tijd kunt opzoeken in de tabel; die tijd heb je nodig om a) te bepalen wanneer de eerste batch begint (Vorige Starttijd is leeg) en waarmee je vervolgens de maximale tijd van de batch berekent. Op basis van deze gegevens kun je dan een recordset maken die controleert of een tijd binnen de batchtijd valt, of dat er een nieuwe batchtijd moet worden uitgerekend en gebruikt.

PHP: 
SELECT Recepten.Stofnaam, Recepten.Bereidingsdatum, (SELECT TOP 1 Bereidingstijd FROM Recepten AS T1 WHERE 
     T1.Bereidingsdatum = Recepten.Bereidingsdatum AND T1.Bereidingstijd < Recepten.Bereidingstijd AND T1.Stofnaam =Recepten.Stofnaam 
     ORDER BY T1.Bereidingsdatum DESC, T1.Bereidingstijd DESC ) AS VorigeTijd, 
Recepten.Bereidingstijd, IIf([VorigeTijd] Is Null,TimeSerial(Hour([Bereidingstijd]),Minute([Bereidingstijd])+60,0),Null) AS Batchtijd
FROM Recepten;
 
Beste OctaFish,

Ik denk dat het met één query uiteraard niet lukt. Ben nu iets aan het proberen maar ik merk dat ik tekortschiet in kennis.

Ik vind (uiteraard) wel per stofnaam de eerste tijd (in een eerste query) en wil dan daarbij 60 minuten optellen en (met een query) zien welke bereidingen dan binnen dat uur vallen, vervolgens met een volgende query weer op zoek naar de eerstvolgende tijd en dan weer binnen dat uur kijken.

Dat doe ik waarschijnlijk veel te omslachtig.

Ik zou het zeer op prijs stellen als je hier nog op terug wil komen of een voorbeeldje zou kunnen maken ... ik kom hier zelf niet helemaal uit.

Groet,

RadboudAKF
 
Laatst bewerkt:
Die query had ik ook als eerste, maar daar heb je inderdaad niet veel aan omdat je dan altijd de eerste tijd van de dag terugkrijgt. Daar kun je dan nog wel een uur bij optellen, maar daar heb je niks aan. Met mijn query zou het wel mogelijk moeten zijn, maar dan nog vraag ik me af of hij echt te gebruiken is voor wat je wilt. Ik zou dus met een tijdelijke tabel werken die op basis van mijn query uit #3 in een recordset wordt gemaakt. De recordset vult dan op basis van de query een aantal variabelen zoals de maxEindtijd, en genereert een batchnummer. Zolang de tijd in de range ligt, kopieer je dan de records naar de tabel, en zodra de tijd overschreden wordt maak je een nieuw batchnummer en een nieuwe MaxTijd. En dat is dus iets dat in een query niet te doen is. Alrthans: niet door mij :).
 
Octafish, (en overige meelezers),

Zeer bedankt voor jouw inbreng. Ik ga proberen of ik hier uitkom. Overigens, als ik jouw bovenstaande SQL (daar ben ik nog steeds niet handig mee) in een query plak dan krijg ik wat poblemen met het WHERE statement. Betekent jouw sql dat je een tabel gaat maken - een tabelmaak-query dus? Ik krijg die query, zoals die daar staat, niet aan de praat, helaas.

Ik weet ook niet wat je met 'een tijdelijke tabel' bedoelt ... kennelijk heb ik nog weer een hoop te leren.

Mochten er nog andere leden meelezen en ideeën hebben hoe ik dit oplos, ik houd mij zeer aanbevolen.

Voorlopig ga ik met de opmerkingen hierboven aan de slag ... en hoop daar uit te komen.

RadboudAKF
 
Ik heb jouw Excelletje in een nieuwe tabel geplakt en die Recepten hernoemd. Verder is het gewoon een Select query, geen tabelmaak. Dus zou wel aan de praat te krijgen moeten zijn. Er zitten ook geen gekke functies in. Wel dus een subquery in een veld; wellicht zie je die over het hoofd. Een 'tijdelijke' tabel zou kunnen (een virtuele tabel) maar dat lijkt me niet zo handig; in dit geval bedoel ik een tabel met de naam 'Temp'. Ik beschouw hem als tijdelijk, omdat hij altijd overnieuw kan worden gemaakt op basis van de gegevens uit andere tabellen, die dan uiteraard niet tijdelijk zijn. Tijdelijke tabellen kan je ook probleemloos weggooien.
 
Ik heb de uitwerking volgens mij wel gekregen zoals je hem hebben wilt. M.b.v. een extra tabel die je dus vult a.d.h.v. de door mij gemaakte query. Om het je makkelijk te maken, heb ik alles in bijgaande db gezet. De functie zit in de module modBatch. Met de knop kun je de tabel vullen. Dat had ik uiteraard al gedaan, maar ik gun je het plezier om hem zelf te vullen :).
 

Bijlagen

  • Meterstanden.zip
    42,8 KB · Weergaven: 41
Octafish,

Dank voor jouw inspanningen.

Ik hoop dat ik dit 'aan de praat' krijg zoals ik wil ... als ik opmerkingen zie over modules dan weet ik vaak (nog) niet hoe ik die moet toepassen. Hoop dat het voor mij allemaal 'snapbaar' is.

Ik ga er meteen naar kijken en laat je weten wat mijn bevindingen zijn. Voor nu ... nogmaals dank.

RadboudAKF
 
Octafish,

Geweldig ... het doet precies wat ik wil. Ik moet nu nog wat werk verrichten om één en ander te doorgronden. Dat lukt niet één, twee, drie. Maar ik zie dat de uitwerking doet wat ik in gedachten had.

Ik zie nog niet of er een 'limiet' zit aan het aantal batches in één etmaal. Zouden dat er in principe 24 kunnen zijn? Ik denk dat ik meteen moet schaven aan het 'uur-principe' ... dat wordt ipv 60min. -59minuten.

RadboudAKF.
 
Laatst bewerkt:
Er is geen limiet aan het aantal batches; hij lust en nummert gewoon door op basis van de tijdslimiet die je ingeeft. Omdat de procedure óók kijkt naar de datum, zit er en zekere logica in dat je nooit voorbij de 24 batches kan komen :). Daarnaast kun je de batchtijd natuurlijk simpel aanpassen; kwestie van in de query en de code de TimeSerial functie aanpassen. Als je het helemaal goed wilt maken, dan pas je óók de SQL van de query aan m.b.v. de VBA procedure. In dat geval zou ik een variabele gebruiken die de eindtijd instelt. Dan hoef je maar op één plek die variabele aan te passen om andere batches te maken.
 
De module trouwens, kun je gewoon importeren in je eigen db. Dus eerst exporteren (<Ctrl>+<e>) en in je eigen db importeren (<Ctrl>+<m>). Uiteraard mag je ook sleuren en pleuren :). Het maakt niet zoveel uit hoe je de code er in zet.
Hier de aangepaste versie, waarbij je de query dus vanuit VBA opmaakt.

Code: 
Function BatchRecepten()
Dim qTmp As QueryDef
Dim rs As DAO.Recordset, rsB As DAO.Recordset
Dim iV As Integer, i As Integer
Dim strSQL As String, sBatch As String
Dim dtStart As Date, dtEind As Date, dtDatum As Date
Const iT As Integer = 59

Set rs = CurrentDb.OpenRecordset("qRecepten")
Set rsB = CurrentDb.OpenRecordset("Recepten_Batch")
Set qTmp = CurrentDb.QueryDefs("qRecepten")

    strSQL = "SELECT Stofnaam, Bereidingsdatum, (SELECT TOP 1 Bereidingstijd FROM Recepten AS T1 WHERE " _
        & "T1.Bereidingsdatum = Recepten.Bereidingsdatum AND T1.Bereidingstijd < Recepten.Bereidingstijd " _
        & "AND T1.Stofnaam =Recepten.Stofnaam ORDER BY T1.Bereidingsdatum DESC, T1.Bereidingstijd DESC ) " _
        & "AS VorigeTijd, Bereidingstijd, IIf([VorigeTijd] Is Null," _
        & "TimeSerial(Hour([Bereidingstijd]),Minute([Bereidingstijd])+" & iT & ",0),Null) AS Batchtijd " _
        & "FROM Recepten;"
    qTmp.SQL = strSQL
    With rs
        Do While Not .EOF
            If IsNull(.Fields(2)) Then
                If .Fields(1) <> dtDatum Then
                    dtDatum = .Fields(1).Value
                    iV = 1
                Else
                    iV = iV + 1
                End If
                dtStart = .Fields(3)
                dtEind = .Fields(4)
                sBatch = Format(.Fields(1), "mmdd_") & Format(.Fields(3), "HHmm_") & UCase(Left(.Fields(0), 3)) & iV
                GoSub AddRecord
            ElseIf .Fields(3) > dtEind Then
                dtEind = TimeSerial(Hour(.Fields(3)), Minute(.Fields(3)) + iT, 0)
                dtStart = .Fields(3)
                iV = iV + 1
                sBatch = Format(.Fields(1), "mmdd_") & Format(.Fields(3), "HHmm_") & UCase(Left(.Fields(0), 3)) & iV
                GoSub AddRecord
            Else
                dtStart = .Fields(3)
                GoSub AddRecord
            End If
            .MoveNext
        Loop
    End With
''    MsgBox "Klaar!"
    Exit Function

AddRecord:
    rsB.AddNew
    rsB.Fields(0).Value = rs.Fields(0).Value
    rsB.Fields(1).Value = rs.Fields(1).Value
    rsB.Fields(2).Value = rs.Fields(3).Value
    rsB.Fields(3).Value = sBatch
    rsB.Update
    Return
    
End Function
 
Octafish,

Je verwent me ...

Zal wel een 'dingetje' worden om dit allemaal te begrijpen ... ben nog steeds druk met jouw cursus ... deze uitwerking is m.i. twee hoofdstukken te ver voor mij ... :thumb:

RadboudAKF
 
Het proces is redelijk autonoom; je hebt dus een query nodig die nu 'qRecepten' heet. Wil je een andere naam, dan kun je die in de code veranderen. Daarnaast haalt de query data op uit een tabel, die bij mij dus 'Recepten' heet. Ook dat kun je zelf wel veranderen met Zoeken en Vervangen. En er wordt dus uitgegaan van een nieuwe tabel die 'Recepten_Batch' heet. Ook die naam kun je uiteraard veranderen in de code. Het enige waar je op moet letten, is of de velden in de batchtabel overeenkomen met mijn voorbeeld. Gebruik jij andere velden, of een andere volgorde, dan worden de gegevens verkeerd weggeschreven, als dat al lukt natuurlijk. Als de veldnamen kloppen, dan hoef je daar niks mee te doen.
 
Octafish,

Nu heb ik (van één maand) wat 'real' data toegevoegd aan de tabel RECEPTEN en dan krijg ik een foutmelding (zoals in de bijlage).

Ik heb wat dingen uitgezocht, maar ik begrijp de foutmelding niet goed. In het eerder voorbeeld had ik geen 'dubbele' records opgenomen. In de real-data-tabel zitten wel 'dubbele records' (Bereidingen (met één en dezelfde stofnaam) die op hetzelfde tijdstip zijn gemaakt.) Moet ik die er eerst uitslopen?

Moet ik de tabel RECEPTEN van een sleutel voorzien, denk je?

(ik heb nu die tabel van een sleutel voorzien (op 3 velden tegelijk) en zie dan in de uitwerking veel meer fouten. (De naam van de Batch klopt ook niet meer, bij sommige batch-namen zie ik de eerste 3letters van een andere stofnaam)

Zou ik jou een voorbeeld database mogen toesturen zodat je een blik kunt werpen? (als ik zo brutaal mag zijn) (via SendIt of zoiets?)

RadboudAKF
 

Bijlagen

  • Foutmelding.png
    Foutmelding.png
    74,2 KB · Weergaven: 71
Laatst bewerkt:
Octafish,

Hoop dat mijn bijlage niet te groot is ...

In de dB zit een tabel (Cato_VTGM_1) waaruit de tabel "Recepten" wordt gevuld. Je ziet dan ook dezelfde stofnamen terug die op hetzelfde tijdstip zijn bereid. Wellicht is dat het probleem?

Ik heb de tabel vast gevuld met een deel van de data uit december2016. De gegevens over Patienten zijn 'fake'.

J
 
Laatst bewerkt:
Ik heb 'm opgehaald, dus je kan hem verwijderen als je dat wilt.
 
Goedemiddag Octafish (en anderen),

Ik heb hier inmiddels een (andere) oplossing voor verzonnen die veel minder geavanceerd is dan de oplossing die OctaFish mij bood. (onverwachts leverde die oplossing toch wat 'merkwaardigheden' op).

In plaats van tijdelijke tabellen heb ik nu 'gewone' tabellen gemaakt die worden gevuld met begin- en eindtijden van elke gevonden 'batch'.

Vanuit die tabellen ga ik dan op zoek in de Receptentabel naar gegevens die 'voldoen' aan het 'uurs-criterium' van de betreffende batch. Dit werkt perfect en snel. Alleen heb ik nu een flink aantal queries moeten bouwen in plaats van de oplossing die OctaFish mij bood. Omdat ik niet hebben kunnen vinden waarom het fout ging in de geboden oplossing heb ik dit bedacht. Volgende week moet ik hiermee al in produktie. Ik merk dat ik met een aantal tussenstappen uiteindelijk kom tot het gewenste resultaat. Jammer, niet heel fraai, maar m.i. wel 'valide' .

Nogmaals dank aan Octafish voor de geboden hulp ...

Groet,

RadboudAKF
 
Ik zat nog te dubben wat eigenlijk het probleem is.... Als je dubbele records hebt, lijkt mij dat sowieso niet goed. Daarnaast leg je de tijd nu heel grof vast, maar als je de tijd met NOW() laat vullen, is elke tijd uniek en bestaat het probleem dus niet. En dan zou mijn oplossing weer werken. Ik snap eerlijk gezegd jouw oplossing niet, maar ach, dat hoeft ook niet :).
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan