Gevonden waarde uit Query gebruiken in andere Query

Ik ben bezig om een order systeem op zetten voor ons eigen bedrijf.
We hebben een bedrijf in de metaalbewerking. Het systeem doet in hoofdlijnen al wat ik wil, maar loop tegen het volgende aan:


Ik wil aan de hand van de invoer van een artikelnummer graag alle voorgaande orderregels van dat artikel zien. Zo zie ik dan direct ook het aantal en de destijds gevraagde prijs zien. Dit is voor mij prima te doen in Access.

Echter hebben wij vaak ook "instelkosten", "opnamekosten" en soms "extra werk ivm..." Sommige klanten willen echter niet apart de instelkosten weten, maar gewoon een prijs per stuk voor die ene orderregel.
Ik was van plan deze kosten op een order toe te voegen door voor deze "extra kosten" een extra orderregel toe te voegen en deze hetzelfde regelnummer te geven als het bestelde artikel.
Een order kan uit meerdere orderregels bestaan. Door extra regels toe te voegen, maar deze hetzelfde regelnummer te geven, is het voor ons en de klant (orderbevestigingen, facturen) duidelijk dat die kosten bij het artikel op die positie horen.

Waar ben ik naar op zoek:

• Ik vul op een formulier het gewenste artikelnummer in
• Deze is gekoppeld aan het artikelID
• Het artikelID moet in de orderregels tabel worden gezocht en alle records met dat artikelID moeten worden getoond
• Maar ook
• In de gevonden records staat het veld "orderregel"
• Alle records met hetzelfde orderregelnummer moeten ook getoond worden

Helaas krijg ik het niet voor elkaar om dit in één query te vangen. Ik heb wat zitten stoeien met subqueries, maar volgens mij lukt het daarmee niet.
Misschien wel met een wat complexere SQL code of heb ik dan VBA nodig?

Ter info:
Mijn database heeft o.a. de volgende tabellen:
Orders, Orderregels, Artikelen

• Een eventuele volgende stap zou zijn om de kosten van de diverse regels, per order, op te tellen. Dan heb ik als resultaat één regel, die de som van alle kosten weergeeft. Maar misschien is dat juist minder overzichtelijk. De praktijk zou uit moeten wijzen waar we het liefst mee gaan werken.

Ik snap het nog niet helemaal.
Zijn de bijkomende kosten (instelkosten", "opnamekosten", "extra werk ivm...") nu order niveau of op orderregel niveau? Of sommige op ene en sommige op het andere niveau?

Voor extra werk lijkt het in ieder geval op orderregel niveau te zijn. Als het zo is dat er per orderregel meerdere extra werkzaamheden verricht kunnen worden lijkt het me logischer een extra tabel te maken gerelateerd aan orderregel. Als het altijd hooguit één post is, kan je net zo goed twee extra velden in je orderregel opnemen (bedrag en omschrijving werkzaamheden). De oplossing met extra regels is sowieso gekunsteld en maakt de zaken zoals je ziet onnodig complex.

Ik heb mijn openingsbericht iets aangepast zodat het misschien wat duidelijker is.

xps351 zei:

Zijn de bijkomende kosten (instelkosten", "opnamekosten", "extra werk ivm...") nu order niveau of op orderregel niveau? Of sommige op ene en sommige op het andere niveau?

Klik om te vergroten...

Die kosten zijn extra orderregels die aan een order worden toegevoegd.

xps351 zei:

Voor extra werk lijkt het in ieder geval op orderregel niveau te zijn. Als het zo is dat er per orderregel meerdere extra werkzaamheden verricht kunnen worden lijkt het me logischer een extra tabel te maken gerelateerd aan orderregel. Als het altijd hooguit één post is, kan je net zo goed twee extra velden in je orderregel opnemen (bedrag en omschrijving werkzaamheden). De oplossing met extra regels is sowieso gekunsteld en maakt de zaken zoals je ziet onnodig complex.

Klik om te vergroten...

Een extra tabel; daar heb ik ook aan zitten denken, maar dan krijg je een soort stuklijst structuur.
Dat is lastig weer te geven op een overzichtelijk formulier of rapport.

Ook extra velden zijn een optie, maar ook lastiger om dat overzichtelijk weer te geven.

Wat ik ook kan doen is een "kostenveld" toevoegen aan de orderregel. In dat veld plaats ik dan het artikelnummer waar die kosten bij horen.
Ik wilde eigenlijk voorkomen dat ik, in mijn ogen, onnodig informatie in moet voeren voor die relatie. Die relatie wilde ik leggen door de diverse orderregels al hetzelfde orderregelnummer te geven.

Naar mijn mening mag het weergeven op een rapport of formulier niet leidend zijn voor je databaseontwerp. Daarvoor zijn alleen de manier waarop je business werkt en algemene ontwerpregels bepalend.

In dit geval maakt het ook niet uit. Toen ik je vraag voor het eerst las, dacht ik meteen aan een subquery. Als je het over weergegeven hebt kom je hoe dan ook uit op 3 niveaus. Het is of order - (artikel)orderregel - kosten (sub-query) of order - orderregel - kosten (tabel). Of de klant nu 1 bedrag wil zien of de uitsplitsing, maakt niet uit. Je zal het in detail vast moeten leggen om het bedrag te kunnen verantwoorden.

Ik weet niet hoe je de "kosten-orderregels" vast gaat leggen, maar naar mijn idee zijn daar andere gegevens van toepassing dan bij een "echte artikel-orderregel". Zo heb je geen artikel en waarschijnlijk ook geen aantal. Als je het echt netjes doet zou je ook een tabel met mogelijke kostensoorten moeten maken. Je zou een orderregel dan aan een artikel of kostensoort moeten koppelen.
Om je database een beetje betrouwbaar te houden zou je ook allerlei validaties in moeten bouwen. Bijvoorbeeld artikel of kostensoort moet ingevuld zijn, als er een kostensoort is ingevuld moet er een orderregel zijn met hetzelfde regelnummer waarin een artikel is ingevuld et cetera.

RaoelS zei:

Die kosten zijn extra orderregels die aan een order worden toegevoegd.

Klik om te vergroten...

Ik volg dit draadje met enige verbazing, die vooral ligt aan de kant van TS, want de oplossingen die Peter voorstelt zijn gewoon correct. Ofwel je voegt extra velden toe aan je tabel Orderregels (niet mijn voorkeur) ofwel je maakt een extra tabel aan die je koppelt aan Orderregels (OrderRegelDetails bijvoorbeeld) waarin je extra records maakt voor de aanvullende bedragen die je moet invullen. Of dat verplichte waarden zijn, die je aan een Kostenplaats moet koppelen, is dan weer een ander verhaal. Lijkt mij niet nodig bij Orders. Maar de structuur van je database zou toch moeten zijn: Orders --> OrderRegels --> OrderDetails.

Maak in je artikelenbestand een artikel "Kosten" aan. In je orderlijnen tabel zorg je dat je een veld hebt om een vrije omschrijving toe te voegen, die je kan invullen als je een kosten artikel toevoegt. Je kan dan zo veel kostenregels invoegen als nodig. Ik zie niet echt nood aan een extra detail tabel.

NoellaG zei:

Ik zie niet echt nood aan een extra detail tabel.

Klik om te vergroten...

Misschien omdat je zo je artikeltabel vervuilt en omdat de kosten aan een orderregel koppeld moeten worden en je dan uitkomt op de gekunstelde oplossing met dubbele regelnummers.

Huh? Hoe zou je zo aan dubbele regelnummers komen??? Elke kost is een aparte orderlijn met zijn eigen nummer/hoeveelheid en zijn eigen bedrag. Een kost is ook een artikel? Het verschil met een fysiek artikel is dat de omschrijving in de orderlijn van het artikel default wordt ingevuld door de omschrijving uit de artikeltabel en je bij een kostenlijn de omschrijving zelf helemaal moet invullen.
Ik werk al jaren zo voor mijn eigen zaak, en ook de software op het werk (SAP-Hannah) werkt zo. Nooit problemen mee gehad.

En, lijkt mij toch écht veel belangrijker: je kunt er niet mee rekenen! Wat voor nut heeft dat nu? Ik vind dat dus een heel slecht idee... En nee, 'kost' is géén artikel. Dat voldoet hoegenaamd niet aan de beschrijving van het tupel 'artikel'. Dat noella al jaren zo werkt, mag natuurlijk, maar hou dit soort tips verre van andere gebruikers .

NoellaG zei:

Hoe zou je zo aan dubbele regelnummers komen???

Klik om te vergroten...

TS schreef dat de kosten aan een orderregel gerelateerd moeten worden en dat hij daarom orderregels met hetzelfde regelnummer wil toevoegen aan de order. Dat is een soort van een oplossing waarop je uitkomt als je geen tabel wilt toevoegen.

OctaFish zei:

maar hou dit soort tips verre van andere gebruikers .

Klik om te vergroten...

Dat gaat moeilijk worden, ik wordt al jaren (niet slecht) betaald om dat soort regels professioneel door te voeren. Dat is nu eenmaal de core business van een data architect/DBA.

Een goede orderlijn heeft minimum de volgende velden: een ID, een OrderID om het te koppelen aan de hoofdtabel, een artikelID om te koppelen aan de tabel artikels, een hoeveelheid veld, een omschrijving veld en een prijsveld. De omschrijving en prijs wordt standaard opgehaald uit de artikelentabel, maar kan overschreven worden. Je wil niet dat als de artikel omschrijving of prijs verandert dat deze in de orderregels ook wordt aangepast. Zelfs als je met historiektabellen voor de produkten werkt (wat ik in de meeste gevallen afraad, beter om eventueel met archieftabellen te werken). Stel er wordt een prijs aangepast omdat er een fout in zat. Als je reeds een order hebt aangemaakt en aan de klant gestuurd aan de foute prijs, dan moet deze orderlijn zo blijven.
Ik weet het, een database toepassing heeft al gauw 100 of meer tabellen, maar dat wil niet zeggen dat je zomaar in het wilde weg tabellen moet bijmaken.

NoellaG zei:

maar dat wil niet zeggen dat je zomaar in het wilde weg tabellen moet bijmaken.

Klik om te vergroten...

Goed lezen is een vaardigheid die ook een DBA zou moeten beheersen. Had je dat gedaan, dan had je gezien dat we afdoende beredeneerd hebben waarom een extra tabel wel nodig is en we dus niet in het wilde weg tabellen aan het fokken zijn.

Ik ben ondertussen wel benieuwd wat TS ervan vindt; dat is tenslotte het enige dat echt belangrijk is.

Allereerst bedankt dat behulpzame mensen meedenken voor mensen die er wat minder verstand van hebben.
Stoor me wel wat aan het "haantjesgedrag", waar is dat voor nodig als iedereen hier vanuit goede bedoelingen komt? Je kunt verschillende inzichten hebben, laat elkaar gewoon in waarde.

Ik dacht in eerste instantie het gewoon simpel te houden.
Eigenlijk zoals NoellaG in zijn eerst bericht aangeeft, maar hij gaat voorbij aan mijn vraag; hoe krijg je die kosten bij elkaaar?

• Een order vul je door de te maken/bewerken artikelen op orderregels te plaatsen. Voor de extra kosten maak je extra orderregels. Als artikel vul je dan bijvoorbeeld "instelkosten" in. De samenhang dacht ik te vormen door die kosten hetzelfde orderregelnummer (een vrij veld) te geven.

Dat gaat perfect om mijn orders op formulieren en rapporten weer te geven.
Het is (voor mij) echter een probleem om dan diverse "samenhangende kosten" uit de orderregels te filteren als je een overzicht van kosten van een artikel wilt krijgen.
Tenzij NoellaG aangeeft dat dit toch te doen is met een iets moeilijkere query of VBA?

De input van xps351 en OctaFish begrijp ik en is misschien logischer. Jullie hebben echter niet aangegeven of mijn idee met VBA wel of niet mogelijk is.
Of jullie zijn simpelweg van mening dat het database technisch gewoon beter is om met een extra tabel te werken?

Gisteravond ben ik al even aan het stoeien geweest om die extra tabel toe te voegen.
Dus als volgt:
Orders - OrderRegels - OrderRegelDetails

Eerst dacht ik dan bij orderregels o.a. de volgende velden te hebben:
OrderregelID - OrderID - Orderregel - Leverdatum

En dan bij OrderRegelsDetails:
OrderRegelsDetailsID - OrderRegelID - ArtikelID - Aantal - Prijs

Dan krijg je alleen eigenlijk min of meer hetzelfde probleem. Ik vul het artikel in bij de details. De koppeling van de kosten gaat dan eventueel via het OrderRegelID, maar toen dacht ik meteen, hoe krijg ik die kosten samengevoegd?

Is dit dan via een query wel te doen?
Of moet ik het op onderstaande manier doen?


Tabel OrderRegels
OrderregelID - OrderID - Orderregel - ArtikelID - Leverdatum

Tabel OrderRegelsDetails:
OrderRegelsDetailsID - OrderRegelID - Aantal - Prijs - KostenSoort

In het veld KostenSoort vul je dan bijvoorbeeld in: "Maakkosten", "Instelkosten", "Malkosten", "BlaBlakosten".

Dan verwacht ik direct commentaar/disccusie op de plek waar ik het aantal en prijs invul.
Mij lijkt dit de logische indeling. Wij hebben namelijk wel eens een order waarbij we een deel gewoon kunnen bewerken. Maar voor een aantal "extra werk" hebben.
Dat zetten we dan ook op zo op de factuur. Hoewel dat een apart systeem is (Twinfield) zou ik dat toch ook graag zo in ons ordersysteem vast leggen.

Dan wordt het als volgt:



Query om alle kosten van een artikel "per orderregel" te vinden is dan prima te doen toch?
Via het ArtikelID zoek ik alle OrderRegelID's waar dat ArtikelID voorkomt. Via het OrderRegelID zoek ik alle bijbehorende kosten.

als sommige kosten aan een specifieke orderregel moeten hangen heb je een self join nodig van orderlijnen naar orderlijnen. Een beetje zoals in een personeelstabel waarin je de baas van iemand moet aangeven. Die baas is zelf ook een personeelslid. Dan maak je een extra veld in de personeelstabel waarin je de waarde van het primaire sleutelveld - het ID van de baas - noteert.
Hetzelfde doe je met de orderregels tabel: je maakt een nieuw veld waarin je het ID-nr van de orderregel noteert waarnaar de kost refereert. Als je het met een extra tabel gaat doen krijg je moeilijkheden met algemene kosten zoals verplaatsings onkosten of administratieve kosten. In het geval dat je dat met een self-join doet dan laat je dat extra veld gewoon leeg.

Met onderstaande ben je mijns inziens op de goede weg.

Tabel OrderRegels
OrderregelID - OrderID - Orderregel - ArtikelID - Leverdatum

Tabel OrderRegelsDetails:
OrderRegelsDetailsID - OrderRegelID - Aantal - Prijs - KostenSoort

In het veld KostenSoort vul je dan bijvoorbeeld in: "Maakkosten", "Instelkosten", "Malkosten", "BlaBlakosten".

Op een aantal punten is denk ik wel aanpassing nodig. Ook heb ik wat vragen.

• In Tabel OrderRegels hoort volgens mij het aantal en de prijs (van het artikel) thuis.
• Wat je in die tabel met "orderregel" bedoelt snap ik niet.
• Als de verschillende artikelen van een order op verschillende momenten afgeleverd kunnen worden staat de afleverdatum op de juiste plek. Als je alles in 1 keer aflevert hoort die bij de order.
• In Tabel OrderRegelsDetails horen aantal en prijs juist niet thuis.
• Naast de kostensoort moet je daar volgens mij ook een bedrag opnemen. Ik neem aan dat je per kostensoort 1 bedrag opneemt en dat een aantal niet nodig is.
• KostenSoort zou misschien KostenSoortID moeten zijn. Je zou een tabel kunnen maken met alle mogelijke kostensoorten.

Ik ben het met Peter eens dat je de opzet van de tabellen niet helemaal logisch hebt neergezet, bijvoorbeeld met de Leverdatum. Die leg je doorgaans vast op Orderniveau, niet op artikelniveau. Tenzij dat bij jullie usance is, dat zelden orders in hun geheel worden geleverd. Je haalt je m.i. dan wel een hoop extra werk op de hals. Wél zou je, i.v.m. naleveringen, een veld Geleverd kunnen gebruiken op artikelniveau waarin je dan per artikel kan bijhouden wat nog in backorder staat.

RaoelS zei:

De input van xps351 en OctaFish begrijp ik en is misschien logischer. Jullie hebben echter niet aangegeven of mijn idee met VBA wel of niet mogelijk is. Of jullie zijn simpelweg van mening dat het database technisch gewoon beter is om met een extra tabel te werken?

Klik om te vergroten...

Wat is jouw obsessie om het met VBA op te lossen? Natuurlijk kan dat wel, maar waarom zou je? Om fouten in je database ontwerp (zoals voorgesteld door noella) te maskeren/op te lossen? Begin met een goed ontwerp, dan komt de rest vanzelf wel.

Zou je een voorbeeldje hebben meegepost, dan was het hele probleem overigens al lang en breed opgelost, want ik denk dat ieder van ons het wel in een half uurtje kan fixen.

OctaFish zei:

Om fouten in je database ontwerp (zoals voorgesteld door noella) te maskeren/op te lossen?

Klik om te vergroten...

Een database ontwerp met een self-join is een gezond ontwerp. Het ontwerp zoals voorgestel, met een extra tabel, zal voor veel moeilijkheden zorgen in de toekomst die ik in de praktijk maar al te vaak ben tegengekomen.

Ik had het niet over een self-join (die ik zelf ook regelmatig gebruik, zie mijn Access cursus), maar over jouw voorstel om alle bedragen in een memo veld te zetten. Of was je dat al weer vergeten? (Zou ik ook doen ).

Heh? Een memo veld? Iets dat ik nooit gebruik?? Ik ken een varchar(max) veld, maar dat bestaat zelfs niet in access dacht ik. Ik heb het in #6# gehad over een vrij omschrijving veld voor een kosten regel/artikel, maar ik denk dat in access de max voor een tekstveld 255 chars of zo is en daar kan je heel goed een omschrijving in plaatsen, maar geen getal om mee te rekenen. In mijn bericht #11# wordt het meer in detail uitgelegd.
Zoals Peter in #12# zegt: even goed lezen

NoellaG zei:

In je orderlijnen tabel zorg je dat je een veld hebt om een vrije omschrijving toe te voegen, die je kan invullen als je een kosten artikel toevoegt. Je kan dan zo veel kostenregels invoegen als nodig. Ik zie niet echt nood aan een extra detail tabel.

Klik om te vergroten...

I rest my case. En wat mij betreft mag je stoppen met dat getheoretiseer en je beperken tot antwoorden waar TS wat aan heeft. De draad is al lang genoeg zo.