Productie totalen

Status
Niet open voor verdere reacties.
R

rvdsch

Gebruiker
Lid geworden
18 jun 2009
Berichten
170
Hey allemaal,

Ik ben bezig een formulier te maken waarin staat welke leverancier wat heeft geleverd aan welke locatie over een bepaalde periode, en daar het totaal van.

Bijvoorbeeld: tussen 01-01-2008 en 01-02-2008 heeft Leverancier Henk 500 ton zand in totaal geleverd (laten we zeggen dat er 5 leveringen van 100 zijn geweest) aan Rotterdam.
Het moet een totaal overzicht worden van alle leveringen. Een aantal leveranciers mogen er niet in komen te staan en als er een factuur ontvangen is van een bepaalde levering mag die er ook niet in komen te staan.
Mijn probleem lijkt erg op dingen die ik in eerdere topics heb aangekaart en ben dus al daarmee aan de slag gegaan.
Ik heb eerst een query gebouwd die alle informatie bevat die ik nodig heb. Dat is deze geworden:
Code: 
SELECT Bedrijfsonderdelen.Bedrijfsonderdeel, Leverancier.Leverancier, Producten.Product, Sum(AlleOrders.Hoeveelheid) AS SomVanHoeveelheid, AlleOrders.LeverancierID, AlleOrders.[Factuur ontvangen], AlleOrders.Datum
FROM Leverancier INNER JOIN (Bedrijfsonderdelen INNER JOIN (Producten INNER JOIN AlleOrders ON Producten.ProductID = AlleOrders.ProductID) ON Bedrijfsonderdelen.LocatieID = AlleOrders.LocatieID) ON Leverancier.LeverancierID = AlleOrders.LeverancierID
GROUP BY Bedrijfsonderdelen.Bedrijfsonderdeel, Leverancier.Leverancier, Producten.Product, AlleOrders.LeverancierID, AlleOrders.[Factuur ontvangen], AlleOrders.Datum;

Vervolgens stel ik mijn condities via een knop op het formulier in. De VBA hiervan is deze:
Code: 
Dim strTabel As String
strTabel = "[Totalen op Datum]"
strSQL = "Select * From " & strTabel & vbCrLf _
& "WHERE (([Datum])>=#" & Me.Startdatum.Value & "# And ([Datum])<=#" & Me.Einddatum.Value & "#)" _
& "And ([Factuur ontvangen]=False" _
& "And ([LeverancierID]<>5 Or [LeverancierID]<>6 Or [LeverancierID]<>7 Or [LeverancierID]<>8 Or [LeverancierID]<>9)"

Me.RecordSource = strSQL
Me.Requery

De code loopt helaas niet.
Ik vraag mij af of het niet handiger is om de hele SQL te baseren op de tabel AlleOrders waar de informatie uiteindelijk vandaan gehaald moet worden. Ik heb dat nu niet gedaan om dan meteen met namen ipv codes te gaan werken maar goed dat kan ik ook op het formulier zelf instellen.
Ook vraag ik mij af hoe en waar ik de sommatie moet instellen. Is dat handig om dat in de query te doen of in de VBA? Het is mij opgevallen dat als ik de datum functie aan heb staan de som niet goed over de hele periode sumt maar over iedere dag apart wat niet de bedoeling is. Enige tips, suggesties
 
Volgens mij moeten alle criteria in te stellen zijn in de query en hoeft op het formulier niet zulke ingewikkelde VBA

Ik heb eigenlijk twee problemen met de SQL. In de eerste plaats krijg ik niet voor mekaar dat hij meerdere leveranciers accepteert. Ik heb al wat varianten geprobeerd met name

<>5 or 6 or 7 or 8 or 9 in het criteria veld maar ik krijg het niet voor elkaar.

Ook blijf ik problemen houden met de datum. Wellicht dat dat wel in VBA moet. Het probleem is echter dat zodra ik de datum op Group By zet mijn SQL mis gaat.

Mijn eerste idee is dus om de datum op Where te zetten en dan de volgende lijn te gebruiken in de criteria (Totalen op Datum is een formulier).

Between [Totalen op Datum].[Startdatum] And [Totalen op Datum].[Einddatum]

Dit leek echter niet zo goed te werken
 
Hoi Rob,

Ik heb in ieder geval alvast je code opgepoetst, want daar zaten wat missertjes in:

Code: 
Dim strTabel As String, strSQL As String
Dim sBeginDatum As Date, sEindDatum As Date
Dim iBegin As Double, iEind As Double
Dim tmp As String

    sBeginDatum = CDate(Me.Startdatum.Value)
    sEindDatum = CDate(Me.Einddatum.Value)
    iBegin = CDbl(sBeginDatum)
    iEind = CDbl(sEindDatum)

    strTabel = "[qTotalen op Datum]"
    strSQL = "Select * From " & strTabel & vbCrLf _
        & "WHERE(([Datum] Between cDate(" & iBegin & ") And cDate(" & iEind & ")) " & vbCrLf _
        & "And ([Factuur ontvangen]=False) " & vbCrLf _
        & "And ([LeverancierID] Not Between 5 And 9))"

''tmp = InputBox("", "", strSQL)

Me.RecordSource = strSQL
Me.Requery

Foutje 1: je kunt niet zomaar op datums filteren, omdat VBA werkt met amerikaanse datumnotatie. Daarom moet je eigenlijk altijd de omrekenslag gebruiken, zoals ook bij het andere topic.
Foutje 2: als je bepaalde leveranciers wilt uitsluiten, dan lukt dat niet met Or als operator. De ene Or variant staat namelijk een andere variant weer toe. Als je zegt: leverancier <>6, dan betekent dat: niet 6. Da's duidelijk. Maar je er van: leverancier <>6 or leverancier <>7, dan sluit <>6 wel 6 uit, maar <>7 staat 6 weer toe! En omgekeerd: <>7 sluit 7 weliswaar uit, maar <>6 staat 7 weer toe. En zo door met de overige varianten. Dit kan dus wel, als je AND gebruikt: leverancier <>6 AND leverancier <>7.
Ik heb dat vervangen door Not Between 5 and 9, wat een stuk korter is.
Stel nu, dat leverancier 7 wel is toegestaan: dan krijg je deze variant: leverancier Not Between 5 and 9 Or leverancier =7.

Michel
 
Hey Michel,

Bedankt voor je reactie:thumb:. Het probleem met de leverancier is idd verholpen.
Is het nou werkelijk slimmer om de hele query in VBA te zetten? Ik heb hem nu gewoon in SQL staan wat het ook wel makkelijk maakt.

Wat het datum probleem betreft, ik heb nu ook in het andere topic gemerkt dat ik dan het probleem heb dat de query ook op datum grouped en dan telt Access dus niet die totalen bij elkaar op. Kortom het totaal van eergisteren en gisteren wordt niet bij elkaar opgeteld.
Ik dacht altijd dat je alleen Amerikaanse datums had als je met die bibliotheken (DAO, ADO) zat te spelen.
 
Hé Rob,

Zodra je datums op een formulier zet, is omzetten via VBA de meest veilige methode. Weliswaar kun je in een query verwijzen naar formuliervelden, waar een datum in staat, maar zelf hou ik daar eigenlijk niet zo van, omdat je de query dan niet los kunt draaien; je hebt altijd gegevens uit een formulier nodig.
Vandaar dat ik selecties via een formulier altijd aanstuur met VBA.
En dan heb je dus wel het probleem met de datumomzetting.
 
Hey Michel,

Dat is duidelijk. Ik laat het nu ook via VBA draaien.
Heb je enig idee hoe het kan dat hij weigert leveringen op verschillende data bij elkaar op te tellen? Ik denk persoonlijk dat het komt omdat in de query Datum op Zichtbaar en Group By staat. Als ik datum weghaal uit de query is dat probleem er niet meer
Is daar een manier om heen? Zou een DSum iets kunnen zijn??
 
Ik heb het met deze code weten op te lossen:

Code: 
SELECT AlleOrders.LocatieID, AlleOrders.LeverancierID, AlleOrders.ProductID, Sum(AlleOrders.Hoeveelheid) AS SomVanHoeveelheid, AlleOrders.[Factuur ontvangen]
FROM Leverancier INNER JOIN (Bedrijfsonderdelen INNER JOIN (Producten INNER JOIN AlleOrders ON Producten.ProductID = AlleOrders.ProductID) ON Bedrijfsonderdelen.LocatieID = AlleOrders.LocatieID) ON Leverancier.LeverancierID = AlleOrders.LeverancierID
WHERE (((AlleOrders.Datum) Between [forms]![Totalen op Datum].[Startdatum] And [forms]![Totalen op Datum].[Einddatum]))
GROUP BY AlleOrders.LocatieID, AlleOrders.LeverancierID, AlleOrders.ProductID, AlleOrders.[Factuur ontvangen]
HAVING (((AlleOrders.LeverancierID) Not Between 5 And 9) AND ((AlleOrders.[Factuur ontvangen])=False));

En de volgende VBA:
Code: 
Dim strTabel As String
strTabel = "[Totalen op Datum]"
strSQL = "Select * From " & strTabel & vbCrLf
Me.RecordSource = strSQL
Me.Requery

Michel ik laat dus de VBA niet zo heel veel doen. Weet je zeker dat het beter is de query in VBA te draaien. De database wordt straks multi user en front end back end. In principe hoeven deze queries nooit zonder formulier te worden geopenend. Maar als mijn methodes in de toekomst problemen zouden kunnen geven dan doe ik het liever anders.
 
Laatst bewerkt:
Toch wil ik het nog is proberen in VBA. Ik gebruik nu deze code:
Code: 
Dim strTabel As String, strSQL As String
Dim sBeginDatum As Date, sEindDatum As Date
Dim iBegin As Double, iEind As Double
Dim tmp As String

    sBeginDatum = CDate(Me.Startdatum.Value)
    sEindDatum = CDate(Me.Einddatum.Value)
    iBegin = CDbl(sBeginDatum)
    iEind = CDbl(sEindDatum)

    strTabel = "[Totalen op Datum]"
    strSQL = "Select Sum(SomvanHoeveelheid) As Totaal And LocatieID And LeverancierID And ProductID From " & strTabel & vbCrLf _
        & "WHERE(([Datum] Between cDate(" & iBegin & ") And cDate(" & iEind & ")) " & vbCrLf _
        & "And ([Factuur ontvangen]=False) " & vbCrLf _
        & "And ([LeverancierID] Not Between 5 And 9))"

''tmp = InputBox("", "", strSQL)

Me.RecordSource = strSQL
Me.Requery

Ik wil hem graag combineren met deze code want ik blijf het datum probleem houden:
Code: 
Dim strSQL1 As String
Dim strSQL2 As String
Dim sBeginDatum As Date, sEindDatum As Date
Dim iBegin As Double, eEind As Double
Dim rst As DAO.Recordset
    sBeginDatum = CDate(Me.Startdatum.Value)
    sEindDatum = CDate(Me.Einddatum.Value)
    iBegin = CDbl(sBeginDatum)
    eEind = CDbl(sEindDatum)
    strSQL1 = "SELECT Sum(AlleOrders.Hoeveelheid) AS Aantal, Leverancier.Leverancier, Producten.Product, AlleOrders.LocatieID, AlleOrders.LeverancierID, AlleOrders.ProductID " & _
              "FROM Leverancier INNER JOIN (Bedrijfsonderdelen INNER JOIN (Producten INNER JOIN AlleOrders ON Producten.ProductID = AlleOrders.ProductID) ON Bedrijfsonderdelen.LocatieID = AlleOrders.LocatieID) ON Leverancier.LeverancierID = AlleOrders.LeverancierID " & _
              "WHERE ((AlleOrders.Datum Between cDate(" & iBegin & ") And cDate(" & eEind & ")) And (AlleOrders.ProductID=" & Me.ProductID_NW & ") And (AlleOrders.LocatieID<>2) And (AlleOrders.LeverancierID=7)) " & _
              "GROUP BY Leverancier.Leverancier, Producten.Product, AlleOrders.LocatieID, AlleOrders.LeverancierID, AlleOrders.ProductID;"
   
    Set rst = CurrentDb.OpenRecordset(strSQL1)
    If rst.RecordCount > 0 Then
        Me.InternLev.Value = rst.Fields("Aantal").Value
    Else
        Me.InternLev.Value = 0
    End If

mijn probleem met de tweede code is dat die voor een tekstvak bedoeld is en ik hem nodig heb als Record source. Ook lukt het me niet om het strSQL definitie goed te krijgen.
hoe stel ik hem in als RecordSource
 
Ik zal er weer een blik op werpen. Moet eerst de formulieren gelijk trekken, want jij hebt (uiteraard) een ander formulier voor de totalen gemaakt als ik...

De tweede VBA code komt zo te zien uit het formulier van de andere topic? Wat bedoel je precies met
die voor een tekstvak bedoeld is en ik hem nodig heb als Record source
Klik om te vergroten...

Je wilt de code als Recordsource gebruiken? Voor een formulier, of een tekstvak?

Michel
 
Michel bedankt voor je hulp. In mijn andere topic loopt de code voor een tekstfak(ik sit op een hele slechte pc) :rolleyes:
Voor dit formulier moet de code weer als Record source lopen van het formulier. Ik weet dus niet of ik bijv een DAO recordset moet declareren enso. Want volgens mij hoeft de regel Set rst etc niet maar dan weet ik niet of dat met die data nog loopt. Ik ga morgen nog even een uitgebreider antwoord schrijven mijn pc loopt niet lekker
 
De code moet idd als RecordSource dienen. Is het trouwens mogelijk om ervoor te zorgen dat als je het formulier opend je nog niet meteen te zien krijgt wat er allemaal mogelijk is. De RecordSource moet beginnen met lopen zodra je op de knop drukt.
Wat ik bedoel is nu krijg je eerst alle mogelijkheden te zien en dan gaat de query in je VBA erover heen. Is het mogelijk om pas gegevens te zien te krijgen nadat de VBA heeft gelopen.

Super bedankt voor alle hulp
 
Dat laatste kan zowiezo. Je kunt gebeurtenissen op elk moment laten starten dat je wilt. Meestal zet je een recordset op een formulier als je het opent, maar je kunt dat ook verplaatsen naar een formulier. Kun je eventueel het formulier waarin je specifiek aan het werken bent uploaden? Aangezien ik de rest toch al heb, kan ik hem dan importeren, en dan zou ik hem wel werkend moeten krijgen.
 
Hey Michel,

Het probleem loopt eigenlijk over een aantal formulieren. Om een voorbeeld te noemen het formulier 'Facturen'. Ik denk zelf dat het met een simpele handeling te verhelpen is maar ik heb geen idee hoe dat moet.
 
En hiermee bedoel je dan dat je eigenlijk pas gegevens wilt zien na bijvoorbeeld een druk op een knop?
 
Ik ben nog steeds bezig met dit probleem. Op zich loopt het formulier maar nog niet zoals ik wil. Ik gebruik nog steeds deze VBA:

Private Sub Knop12_Click()
Dim strTabel As String, strSQL As String
Dim sBeginDatum As Date, sEindDatum As Date
Dim iBegin As Double, iEind As Double
Dim tmp As String

sBeginDatum = CDate(Me.Startdatum.Value)
sEindDatum = CDate(Me.Einddatum.Value)
iBegin = CDbl(sBeginDatum)
iEind = CDbl(sEindDatum)

strTabel = "[Totalen op Datum]"
strSQL = "Select Sum(SomvanHoeveelheid) As Totaal And LocatieID And LeverancierID And ProductID From " & strTabel & vbCrLf _
& "WHERE (([Datum] Between cDate(" & iBegin & ") And cDate(" & iEind & ")) " & vbCrLf _
& "And ([Factuur ontvangen]=False) " & vbCrLf _
& "And ([LeverancierID] Not Between 5 And 9))"

''tmp = InputBox("", "", strSQL)

Me.RecordSource = strSQL
Me.Requery
End Sub

De code werkt niet maar ik kan de fout niet ontdekken
 
Dit stukje code

Code: 
strSQL = "Select Sum(SomvanHoeveelheid) As Totaal And LocatieID And LeverancierID And ProductID From " & strTabel & vbCrLf _
& "WHERE (([Datum] Between cDate(" & iBegin & ") And cDate(" & iEind & ")) " & vbCrLf _
& "And ([Factuur ontvangen]=False) " & vbCrLf _
& "And ([LeverancierID] Not Between 5 And 9))"

klopt zo wie zo niet

Bedenk even dat de opbouw van een basis SQL statement als volgt is

SELECT kolom1, kolom2, ...., kolomx
FROM tabel

Als je een zogenaamde aggregatiefunktie gebruikt (Sum, Avg, Min, Max) binnen je statement en je naast het resultaat van, in jouw geval, Sum nog meer gegevens wilt ophalen, dan moet je groeperen op alle andere kolommen.

Dus, even je VBA code aanpassen.

Tardis
 
Oftewel: het moet dit zijn:

Code: 
strSQL = "Select Sum(SomvanHoeveelheid) As Totaal, LocatieID, LeverancierID, ProductID From " & strTabel & vbCrLf _
& "WHERE (([Datum] Between cDate(" & iBegin & ") And cDate(" & iEind & ")) " & vbCrLf _
& "And ([Factuur ontvangen]=False) " & vbCrLf _
& "And ([LeverancierID] Not Between 5 And 9))"
 
Bedankt voor de reacties. Ik had met komma's ipv And moeten werken. Ik heb jouw code gekopieerd Michel. Ik krijg nu echter de vreemde foutmelding Error 2580 De recordbron Onwaar die is opgegeven voor dit formulier bestaat niet. Ik heb verder gezocht en kwam deze definitie tegen

The record source '|' specified on this form or report does not exist. You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.@In the Form or Report's Design view, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query.
Klik om te vergroten...

Maar dat kan volgens mij helemaal niet... Iemand hier enige ervaring mee. De query heet "Totalen op Datum" en ik heb er [] omheen gezet.
 
Waar Tardis volgens mij op doelde is dat je dit in je statement hebt staan:
Code: 
Sum(SomvanHoeveelheid) As Totaal

Dit is een totaal (Group By, Som, Aantal), kortom je wilt van 1 veld een totaal, maar van de andere velden niet. Dat gaat je niet lukken. Dan mis je namelijk een GROUP BY in je SQL statement.

De volledige query zou er dan ongeveer als volgt uit moeten zien:
Code: 
SELECT Sum(Hoeveelheid) AS SomVanHoeveelheid, LocatieId, LeverancierID, ProductId
FROM Tabel1
WHERE (((Datum) Between #1/1/2009# And #1/1/2010#) AND (([LeverancierID]) Not Between 5 And 9) AND ((Factuurontvangen)=False))
GROUP BY LocatieId, LeverancierID, ProductId;

Of misschien is de Sum een foutje (ik vermoed copy-paste):
Code: 
strSQL = "Select SomvanHoeveelheid, LocatieID, LeverancierID, ProductID From " & strTabel & vbCrLf _
& "WHERE (([Datum] Between cDate(" & iBegin & ") And cDate(" & iEind & ")) " & vbCrLf _
& "And ([Factuur ontvangen]=False) " & vbCrLf _
& "And ([LeverancierID] Not Between 5 And 9))"
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan