Alternatief voor dlookup

[Nico84]

Op het rapport Verkopen in het bijgevoegde voorbeeld wordt het aantal opgezocht doormiddel van deze formule:

=NZ(DLookup("Aantal";"QVerkocht";"[ArtikelID] =1 And [Klantid] = " & [KlantId] & " And [Mutatietype]='Eindvoorraad' And [MutatieDatum]=#" & Format([Mutatiedatum];"mm-dd-yyyy") & "#"))

Alleen bij veel mutaties is dit erg traag, is er een alternatief voor DLookup?

Of misschien een andere manier om het rapport op te bouwen.
Wil alleen wel graag de opzet/layout hoe het nu is graag zo houden.

Ik heb deze vraag ook gesteld op het office forum:

http://office.webforums.nl/viewtopic.php?t=44168

Maar kreeg weinig reactie, dus hoop dat jullie mij verder kunnen helpen.

 

[OctaFish]

Ik zal er vanavond naar kijken; op het werk werken we nog met 2003.

 

[Nico84]

Dat is snel :shocked:. Hoor het vanavond wel.

 

[OctaFish]

Ik snap niet wat je aan het doen bent, en dat maakt tips geven uiteraard behoorlijk lastig... Als ik naar je gegevens kijk, dan zie ik tabellen die prima aan elkaar gekoppeld zijn; alleen wat je aan het doen bent? Als ik bijvoorbeeld een willekeurig artikel pak (1 in dit geval) dan zie ik de volgende gegevens:

Mutatiedatum ArtikelID Artikelnaam Datumtelling Klantid Klantnaam Aantal Mutatietype
18-7-2011 1 Schoenvork 18-7-2011 1 Jansen 10 Eindvoorraad
18-7-2011 1 Schoenvork 18-7-2011 2 Pietersen 100 Eindvoorraad
18-7-2011 1 Schoenvork 18-7-2011 3 de Vries 25 Eindvoorraad
18-7-2011 1 Schoenvork 22-7-2011 1 Jansen 4 Beginvoorraad
18-7-2011 1 Schoenvork 22-7-2011 2 Pietersen 20 Beginvoorraad
18-7-2011 1 Schoenvork 22-7-2011 3 de Vries 12 Beginvoorraad

Ik zie dus voor elk record dezelfde mutatiedatum, maar verschillende Tellingdatums. Vraag 1 uiteraard: wat is een Datumtelling? Verder zie ik drie verschillende klanten, met alledrie een verschillende Eindvoorraad waarde op Datumtelling 18-7. Op 22-7 zie ik bij elk record een waarde voor Beginvoorraad; deze waarden zijn lager dan de eerdere Eindvoorraad. Is er voorraad verkocht?
Nemen we Pietersen: Die begint op 18-7 met een eindvoorraad van 100; krijgt vervolgens op 22-7 een beginvoorraad van 20 (je zou zeggen: 80 stuks gestolen..), en op dezelfde dag een eindvoorraad van 9. Heeft-ie er 11 verkocht? Op 25-7 heeft-ie ineens een beginvoorraad van 5! Zijn er weer 4 spoorloos verdwenen! Tijd voor een ander bewakingsbedrijf, zou ik zeggen!

Kortom: ik snap er niet zoveel meer van. Normaal gesproken zou ik zeggen: maak van je verkopen een kruistabel; daar staan alle gegevens gelijk in, inclusief de berekeningen. Op basis van de kruistabel maak je dan het rapport; nooit meer een DLookup nodig. Maar ik zou nu niet weten waar ik moest beginnen...

 

[Nico84]

Zal wat meer toelichten:
Wij leveren vers producten aan klanten.
De artikelen die wij neer zetten bij de klant staan voor onze rekening. Klant betaalt alleen wat hij verkocht heeft.

Het gene wat ik wil zien is het aantal wat er per artikel bij de klant staat en wat er over is.
Hier heb ik belang bij, omdat we met versproducten werken.
Bijvoorbeeld:
Maandag: -Als ik aankom tel ik de aantallen per artikel (beginvoorraad)
-Vervolgens neem ik artikelen retour en lever ik nieuwe artikelen
-Beginvoorraad - retouren + levering = Eindvoorraad

Vervolgens kom ik vrijdag weer voor een nieuwe levering. En gaat het op dezelfde manier als de maandag.
Vrijdag: -Als ik aankom tel ik de aantallen per artikel (beginvoorraad)
-Vervolgens neem ik artikelen retour en lever ik nieuwe artikelen
-Beginvoorraad - retouren + levering = Eindvoorraad

Als ik dan de eindvoorraad heb van de maandag minus de beginvoorraad van de vrijdag kan ik zien wat er verkocht is.
Dit herhaalt zich dan natuurlijk.

Datumtelling is de datum dat je daadwerkelijk bij de klant staat.
Mutatiedatum is de datum waarop de telling betrekking heb. Deze wordt opgehaald dmv DMax.
Voor de beginvoorraad is dit dan de laatste levering in dit geval maandag.
Voor de eindvoorraad is dit de datum van de telling in dit geval vrijdag.

Door het rapport wat ik gemaakt heb in het voorbeeld krijg ik de regels onder elkaar.
En heb ik een duidelijk overzicht per artikel wat er verkocht wordt en kan zo aansturen
door wat meer of minder van het ene artikel neer te zetten bij de klant.

Het nadeel van het rapport is dat het totaal niet flexibel is. Het geluk wat we hebben is dat
we ongeveer 12 artikelen voeren.
Maar doordat alle data bijna opgehaald wordt dmv van DLookup is het rapport behoorlijk traag.

Een kruistabel wat je aangaf werkt voor mij niet voldoende.
Ik wil graag de eindvoorraad en de beginvoorraad naast elkaar zien, zoals in het rapport.

Misschien dat het zo iets duidelijker wordt voor je. En misschien krijg je wel een ingeving
om het anders aan te pakken. Ik zelf kom er niet helemaal lekker uit.

 

[perpon]

Nou het was weer eens een leuk probleem, met wellicht een aardige oplossing of het begin van ...
En inderdaad heeft Octafish helemaal gelijk dat je met een kruistabel moet werken.
Alleen kan je in een kruistabel geen twee waarden opgeven, daarom via een truc de waarden begin en eind bij elkaar in een tekstveld geplaatst.

Het stappenplan is :

1 : qrapportverkopen2 : deze plaatst de gegevens in een juiste sorteervolgorde voor stap 2
2: Achter de knop Nieuw rapport (en kruistabel verkopen) zit wat vba code om de nieuwe tabel 'tblVoorRapport' te vullen met relevante gegevens.
3 M.b.v. de kruistabelquery 'qkrRapportVerkopen3' , wordt een rapport gemaakt.
het rapport is nu gemaakt voor 3 artikelen, dus bij meer moet deze opnieuw worden gemaakt.

Nou ik hoop dat de bijlage (Access 2007) duidelijk is

Jan

Bekijk bijlage Database1-mo.zip
mo=mogelijke oplossing ;-)

 

[OctaFish]

Als je niet gehecht bent aan de layout zoals je hem nu hebt, kun je eens naar deze oplossing kijken. Deze is gebaseerd op een kruistabel, en daar komt geen enkele DMax of DLookup aan te pas; hij is dus razendsnel.
Het is denk ik wel mogelijk om op deze basis een rapport te maken volgens je huidige layout, maar dat kost nog wat extra tijd.

 

[perpon]

Ik had de mogelijkheid van Octafish ook gezien, maar ik dacht dat je alles in een regel wilde hebben. Maar voor educatieve doeleinden is het toch wel leuk om de door Octafish aangedragen oplossing ook weer te geven Zie download versie 2 .

Jan


Bekijk bijlage Database1-mo2.zip

 

[OctaFish]

Zelf geef ik de voorkeur aan een oplossing zonder extra tabel; en die is ook niet echt moeilijk. Door twee Kruistablqueries (één voor Beginvooraad, één voor eindvoorraad) samen te voegen in één query, los je dat op. Zie voorbeeldje! En uiteraard nog steeds geen DLookup nodig...

 

[Nico84]

Hoi OctaFish als ik de database download krijg ik de melding onbekende database indeling.
Heb dit weekend weinig tijd om er goed naar te kijken, maar ziet er allemaal veelbelovend uit.
Alvast bedankt voor de moeite en meedenken Peron en OctaFish.

 

[OctaFish]

Ik heb 'm in 2010 gemaakt; het kan zijn dat je daar problemen mee hebt in 2007. Ik heb althans wel eens vaker gezien dat iemand 'm niet meer kan openen. Ik zal kijken of ik er een nieuwe versie van kan maken. En anders post ik de queries wel die je nodig hebt; die kun je dan zelf wel in je db verwerken, lijkt mij.

 

[OctaFish]

Ik heb 'm opnieuw opgeslagen; het blijkt dat de Sorteerinstelling in 2010 niet ondersteund wordt in oudere versies. Dus misschien wel meer succes nu! Ik zal er ook nog een 2003 export van maken. Voor de zekerheid

 

[Harry46]

Bovenstaande oplossingen werken alleen als er met een vast aantal artikelen wordt gewerkt. Is het aantal artikelen variabel dan zal het rapport dynamisch van opzet moeten zijn. Dat geldt eventueel ook voor de onderliggende queries.

 

[perpon]

Ja dat zag ik ook al in mijn oplossing, het rapport is niet dynamisch en bij meer artikelen moet het rapport worden aangepast. De onderliggende queries lijken bij mij wel prima te werken. Wanneer je een artikel toevoegt en de voorraad tabellen aanpast zie je het nieuwe artikel wel terug in de kruistabel(len) echter niet in het rapport.

Ik denk echter wel dat het maken van een dynamisch rapport, zonder te weten hoeveel artikelen maximaal aanwezig kunnen vrij ingewikkeld is. Wellicht is er iemand anders in dit forum met een oplossing voor zo'n probleem?

Een andere, alternatieve rapportoplossing, is de kruistabelresultaten naar Excel te exporteren. En daar met wat slim (macro) knip en plakwerk de opmaak te verzorgen.

Jan

 

[OctaFish]

TS geeft zelf al aan dat hij met een vast aantal (12) producten werkt; dan kan het lonen om het systeem eenmalig zo op te zetten. Bij (veel) meer artikelen, of een wisselend assortiment, zou ik het zo ook niet aanpakken

 

[OctaFish]

Een dynamisch rapport, om het daar eens over te hebben, kan naar mijn weten alleen als je dat programmeert; ik heb dat zelf in ieder geval ook wel eens gedaan. Om een indruk te geven hoe je dat doet, vind je hier een prima tutorial. Of hier...

 

[Harry46]

@Jan

Omdat ik toepassingen ontwerp die meestal door meerdere instellingen/organisaties worden gebruikt, moet ik nogal vaak gebruikmaken van dynamische rapporten.

Dynamische rapporten zijn eigenlijk tamelijk eenvoudig als je de algemene opzet begrijpt.
Het gaat dan meestal over, via VBA, van het wijzigen van de besturingselementbron en het zichtbaar/onzichtbaar maken van velden.
In de bijlage een 2-tal schermafbeeldingen van een dynamisch rapport. De basis van dit rapport bestaat uit tabellen van de structuur lijst-onderwerp-item-waarde.
Waarbij dus een lijst, verschillende onderwerpen kan hebben, en bij elk onderwerp weer verschillende items.

Hierbij ook een voorbeeldje m.b.t. de vraagstelling van TS. Die is net is afwijkender omdat het in eerste instantie niet op een reguliere kruistabelquery lijkt.

 

[perpon]

Al met al een zeer educatieve thread over kruistabellen :thumb:

Jan

 

[Nico84]

Alvast harstikke bedankt voor alle reacties. Nooit verwacht dat er zo meegedacht zou worden. :thumb:

Voor het rapport wil ik graag alles op 1 regel hebben.
Wij werken met ongeveer 12 artikelen dit kan met een vast layout voor een rapport, maar voor de toekomst is het zeker een goed idee om het rapport dynamisch op te zetten.
Ben dan ook geinteresseerd in harry46 aangedragen voorbeeld.

Het voorbeeld van Perpon ziet er goed uit en ook simpel uit te voeren. Heb zelf weinig kennis van vba.
Maar ben ook nieuwschierig naar het voorbeeld van OctaFish.

Heb alleen problemen met het openen van de database voorbeeld bestanden.
Het voorbeeld van Harry46 krijg ik de melding "onbekende database indeling".
Ook bij het nieuwe voorbeeld bestand van OctaFish krijg ik weer de melding "onbekende database indeling".

Hoop dat jullie het nog een keer willen proberen om de voorbeeld bestanden te plaatsen.
Ik zelf werk met Access 2007.

 

[Harry46]

Mijn voorbeeldje is gemaakt in Access 2010.
Zal kijken of ik vanavond nog een versie in Access XP kan uploaden.