[Access/Excel] Dataset voor pivottable bepalen

[_VpS_]

Ik heb de volgende situatie:
Het betreft een registratiesysteem voor uitval van onderdelen bij een zekere behandelingsproces.
In een Kast bevinden zich nul of meer Laden. In elke Lade bevinden zich nul of meer onderdelen. Nul of meer onderdelen per lade kunnen fouten bevatten (Uitval).

De tabelstructuur in Access is simpel en hoef ik - denk ik - niet neer te pennen. (Kast, Lade, Uitval)

(Deze situatie is overigens zwaar vereenvoudigd).

Een simpele select met een Sum op AantFouten levert de volgende recordset:

code:

KastNr LadeNr AantOnderdelen Foutcode AantFouten
KAST1  LADE1  100           F1     10
KAST1  LADE1  100           F2     10
KAST1  LADE2  101           F1     5
KAST2  LADE1  50             F1    5
KAST2  LADE2  51             F2    10
KAST2  LADE2  51             F1    10

Er zijn dus 2 Kasten, elke met 2 laden. Lade1 van Kast1 Bevat 100 onderdelen en Lade2 van KAst1 bevat 101 Onderdelen. Hier zit ook de kern van het probleem: Dat het aantal onderdelen voor Lade1 van KAst1 dubbel wordt genoemd. Idem voor Kast2.

Deze recordset exporteer ik via OLE naar Excel, die er een pivottable van maakt:
(De datafield is Sum of AantOnderdelen)

code:

[AantO] LadeNr      
KastNr  LADE1   LADE2   Grand Total
KAST1    200      101     301
KAST2    50    102   152
Total    250      203     453

Dit is natuurlijk niet goed. De tabel zou er zo uit moeten zien:

code:

[AantO] LadeNr      
KastNr  LADE1   LADE2   Grand Total
KAST1    100      101     201
KAST2    50    51    101
Total    150      152     302

De telling vna het aantal onderdelen is van cruciaal belang bij het bepalen van uitvalpercentages. Een crosstab query in Access zelf levert dezelfde tabel op.

Dit kan i kvermijden door dmv wat toverwerk het aantal onderdelen per lade in de recordset slechts 1 keer te noemen, en de rest blanco te laten (dus:het aantal onderdelen wordt niet meer dubbel geteld). Maar dan kom ik in de problemen als de gebruiker KastNr wegsleept en Foutcode daarvoor in de plaats zet. In dat geval is bovenstaande recordset WEL juist :

code:

[AantO] LadeNr      
KastNr  LADE1   LADE2   Grand Total
F1      150   152     302
F2      100    51    151
Total      250        203    453

Fout F1 trad in Lade1 op bij een totaal van 150 onderdelen. En in Lade 2 bij 152 onderdelen. Dit is helemaal correct.

Het probleem is dus dat de pivottable bij de ene combinatie van fields wel klopt en bij de andere niet.
Mijn eerste gedachte was om dan 2 recordsets aan te houden: 1 voor het geval dat er geen foutcode bij betrokken is: dan delete ik de dubbele vermeldingen van het aantal onderdelen. En als er wel foutcodes in de tabel voorkomen de volledige recordset aanhouden. Probleem is dat ik dan Excel pivottable events in Access moet opvangen (voor het geval de gebruiker velden versleept) en dan afhankelijk van de versleepte velden de recordset aanpassen. Ik zou niet weten hoe dat te doen Bovendien kan de recordset van een pivottable achteraf niet veranderd worden. Daarnaast spelen veel meer variabelen een rol in de echte situatie.

Verder heb ik geprobeerd van de data-field in de pivottable een calculated field te maken: Bijvoorbeeld:

code:

= AantOnderdelen / Count(AantOnderdelen)

Het idee is dat je dan het eigenlijke aantal onderdelen terug krijgt. Maar dit werkt niet omdat Count(AantOnderdelen) altijd 1 oplevert. Dit komt doordat Excel eerst de crosstab query uitvoert en dan de count op elke datafield doet.

Ook probeerde ik een 2de pivottable te maken met als datafield Count of AantOnderdelen. Daarin krijg ik WEL de juiste aantal laden. En dan in de eerste tabel de datafield te delen dooor de overeenkomstige waarde in de 2de tabel. Dat werkt ook niet, want in een pivottable kun je geen referenties opnemen naar andere cellen/tabellen. En al had dit gewerkt, bedenk ik me nu, iedere keer als de gebruiker een veld versleept in de eerste tabel, moet dezelfde field in de 2de tabel automatisch naar dezelfde plek versleept worden. Nog complexer dus.

Nu weet ik het niet meer. Als dit niet lukt dan moet ik terugvallen op wat eenvoudige statische grafiekjes. Maar de bedoeling was juist het toevoegen van slice & dice functionaliteit om de gebruiker in staat te stellen verschillende variabelen snel en makkelijk naast elkaar te kunne leggen om zo relaties tussen de verschillende variabelen te ontdekken.

Een hoop getyp, maar het heeft me wel helpen nadenken Hoop op suggesties van jullie kant.

[Verwijderd]

maak er in plaats van een 2 weg tabel een 3 weg tabel van, met als derde richting de foutcode. Dan is je probleem opgelost.

[_VpS_]

Het vervelende is dat ik het aantal richtingen in de tabel niet zelf bepaal. Dat doet de gebruiker zelf door door fields her en der te verslepen. Kan dus voorkomen dat ie een 2-weg tabel van maakt.
Óf ik instrueer de gebruikers dat ze nooit een 2-weg tabel moeten maken

Zeg het maar als ik je verkeerd begrijp.

[Verwijderd]

sja...ik weet niet hoe jij je gebruikers die tabellen wil aanbieden. De structuur van de data is een 3 dimensionale:

Kast bij Lade bij foutcode.

Misschien kun je er omheen door eerst het % foutcodes te berekenen, en uit het aantal fouten en dat % het aantal onderdelen terug te berekenen.