Reken query met meerdere tabellen

[LodewijkG]

Beste,

Ik ben een poging aan het wagen om een query te maken waar ik een verkoopprijs mee kan berekenen.

Mijn database ziet er alsvolgt uit:


Ik probeer een formulier te maken voor tblOrder. In dit formulier wil ik als subformulier tblProductOrderId toevoegen om producten te berekenen voor een order.

Nu is mij verteld dat berekeningen via query's moeten. Deze wil ik nu dus gaan maken maar na veel proberen kom ik nog steeds niet goed uit. Mijn idee was om op basis van tblProductOrderId een query te maken. In deze query wilde de voorgestelde invoervelden door acces vervangen door keuzelijst met invoervak en dan de kosten in een formulier in losse velden laten terugkomen door =[cboNaam].[column](x). Daarna in het formulier ook een veld toevoegen die deze gegevens opsomt en op basis daarvan een salesprijs op te maken.

Deze aanpak heeft voor mij niet gewerkt. Heeft iemand een suggestie voor een aanpak die hier 'normaliter' voor gebruikt wordt?

Ik hoor het graag.

Mvgr,

Lodewijk

 

[OctaFish]

Deze regel snap ik niet helemaal:

In deze query wilde de voorgestelde invoervelden door acces vervangen door keuzelijst met invoervak en dan de kosten in een formulier in losse velden laten terugkomen door =[cboNaam].[column](x).

Een veld in een query vervangen door een keuzelijst met invoervak?
Normaal gesproken zou het zo moeten zijn dat je in je tabel die velden hebt waarvan je de gegevens op wilt slaan. Dat zijn dan in de tabel [tblProductOrder] (ik zou de Id uit de naam halen) de velden die je nu ook wel zo'n beetje hebt. Jammer genoeg is nou net díe tabel in je plaatje niet volledig, en dat is (voor jouw vraag) nu net de belangrijkste tabel! Maar goed, wat ik dus mis in de tabel [tblProduct] is een veld [Prijs]. Die heb je (terecht) wél in [tblProductOrder] zitten, dus waar haal je nu de prijs vandaan?
Je formulier fOrder is dus gebaseerd op de tabel [tblOrder] en het subformulier fProductOrder op de tabel [tblProductOrder]. In het subformulier fProductOrder wil je aanvullende informatie zien uit de tabel [tblProduct], zoals de prijs en wellicht de productcode of omschrijving, afhankelijk van wat je ziet in de keuzelijst cboProduct. Want een keuzelijst maak je dus op het subformulier fProductOrder. In dit geval haal je dan uit tblProduct alle velden op die je nodig hebt, waarvan je het ProductID opslaat in de tabel [tblProductOrder] en eventueel ook de prijs. Dat laatste hoeft niet als je een historie bijhoudt van de prijswijzigingen, zoals in de cursus ook is behandeld. Maar de meeste mensen slaan de prijs dus op in de tabel [tblProductOrder]. Die prijs komt dus uit de keuzelijst.

Omdat je een tekstvak of keuzelijst maar één keer kunt koppelen, moet je een keuze maken: wil je de waarde alleen maar ter referentie zien, of wil je een waarde uit de keuzelijst opslaan? In het eerste geval kun je kiezen voor =[cboNaam].[Column](#) als Besturingselementbron. In het tweede geval moet je het tekstveld koppelen aan het tabelveld. En moet je de waarde van dat veld toewijzen vanuit de keuzelijst. Ik gebruik daar de gebeurtenis <Bij Klikken> voor, met een opdracht als: Me.Teksveld.Value = Me.cboNaam.Column(#). En dat werkt dus net zo makkelijk.
Wanneer kies je nu voor opslaan en wanneer voor referentie? Gegevens als Artikelnaam, categorie etc. verwijs je meestal, gegevens als Prijs en Korting en/of BTW kun je vast in de tabel zetten. BTW ook, want als het BTW tarief verandert, wil je niet dat je berekeningen niet meer kloppen. Eigenlijk sla je dus alles waarvan je vermoed dat het dynamisch is (prijzen weer als voorbeeld) op in een tabel, de rest niet.

Omdat je de Prijs hebt opgeslagen, en het Aantal artikelen (en eventueel de belasting) kun je altijd de (sub)totaalprijs voor een artikel uitrekenen. Dat mag in een query, maar kun je net zo goed op het formulier doen. Zelf doe ik dat doorgaans gewoon op het formulier. Totaalprijzen sla ik dus nooit op in een tabel. De Normalisatieregel luidt namelijk: gegevens die berekend kunnen worden uit andere velden sla je niet op in de tabel.

Kortom: hou het simpel en gebruik in eerste instantie tabellen voor je formulieren. Dan kun je ook geen onwerkbare formulieren krijgen (doordat je uit verschillende velden haalt waardoor, als je verkeerde velden pakt, de query niet meer bewerkt kan worden) en gebruik je keuzelijsten om aanvullende en noodzakelijke gegevens toe te voegen. Berekeningen op dat formulier trigger je dan vanuit de velden die de trigger zijn. Zoals de keuzelijst cboProduct (triggert de prijs) en het tekstveld [Aantal] (triggert het aantal). Beide velden hebben invloed op de totaalprijs (excl. en incl. BTW) die je op je formulier ziet.

 

[LodewijkG]

Dag Octafish,

Zoals altijd, bedankt voor je snelle reactie!

Dat zijn dan in de tabel [tblProductOrder] (ik zou de Id uit de naam halen)

Dat zag ik na het posten inderdaad ook, aangepast. Bedankt!

In dit geval haal je dan uit tblProduct alle velden op die je nodig hebt, waarvan je het ProductID opslaat in de tabel [tblProductOrder] en eventueel ook de prijs. Dat laatste hoeft niet als je een historie bijhoudt van de prijswijzigingen, zoals in de cursus ook is behandeld. Maar de meeste mensen slaan de prijs dus op in de tabel [tblProductOrder]. Die prijs komt dus uit de keuzelijst.

Dit begrijp ik denk ik niet goed. Ik probeer eigenlijk op basis van een keuzelijst in tblProductOrder op ProductId(gebaseerd op een query die tblProduct & tblProductPrice met elkaar koppeld) automatisch het daarbij gekozen (product)PriceId op te slaan (ProductId wordt in mijn belevenis in de hiervoor genoemde query gefilterd op geldigheid van de prijs). Dus mocht een product geen geldige prijs hebben wordt deze ook gelijk uit de keuzelijst verwijderd. Mijn issue is dat ik (product)PriceId niet automatisch uit een selectie van ProductId kan opslaan. Zou ik dat wel kunnen doen?

En moet je de waarde van dat veld toewijzen vanuit de keuzelijst. Ik gebruik daar de gebeurtenis <Bij Klikken> voor, met een opdracht als: Me.Teksveld.Value = Me.cboNaam.Column(#). En dat werkt dus net zo makkelijk.

Dat zou dus denk ik de oplossing zijn voor bovenstaand probleem, zou dit dan ook met <Na Bijwerken> van ProductId werken?

Kortom: hou het simpel en gebruik in eerste instantie tabellen voor je formulieren. Dan kun je ook geen onwerkbare formulieren krijgen (doordat je uit verschillende velden haalt waardoor, als je verkeerde velden pakt, de query niet meer bewerkt kan worden) en gebruik je keuzelijsten om aanvullende en noodzakelijke gegevens toe te voegen.

Dus ik zou beter een formulier kunnen bouwen vanuit tblProductOrder en op basis daarvan bijvoorbeeld een keuzelijst kunnen invoeren met qryProductPrice op basis van tbl(product)Price & tblProduct, waarna ik de PriceId via bovenstaande oplossing zou kunnen opslaan.

Merci!

 

[OctaFish]

Dat zou dus denk ik de oplossing zijn voor bovenstaand probleem, zou dit dan ook met <Na Bijwerken> van ProductId werken?

Wat valt er bij te werken aan een keuzelijst? Je opent een keuzelijst en klikt op een optie. Maar het mag wel, alleen wordt de actie uitgesteld tot je van de keuzelijst af bent. Klikken is dus directer. En in mijn ogen daarom beter.
Dat van die prijzen snap ik dan weer niet; ik had niet goed gekeken naar je plaatje en zag daar inderdaad een tabel met prijshistorie. Prima dus. Die levert als het goed is in je keuzelijst maar één prijs op, want de huidige datum kan maar in één datumreeks vallen. Dus voor je keuzelijst maakt het niet uit of je de prijs uit een andere tabel haalt of uit de tabel Producten. Je tekstveld is sowieso gekoppeld aan je Orderregels tabel, dus ook al heeft de keuzelijst geen prijs, dan blijft het tekstveld leeg en kun je de prijs met de hand invullen.

 

[LodewijkG]

Goedemorgen Octafish,

Wat valt er bij te werken aan een keuzelijst? Je opent een keuzelijst en klikt op een optie. Maar het mag wel, alleen wordt de actie uitgesteld tot je van de keuzelijst af bent. Klikken is dus directer. En in mijn ogen daarom beter.

Ik ga dit eens proberen, doe het zo omdat ik het tot nu toe op deze manier was tegen gekomen via een youtube filmpje.

Dat van die prijzen snap ik dan weer niet; ik had niet goed gekeken naar je plaatje en zag daar inderdaad een tabel met prijshistorie. Prima dus. Die levert als het goed is in je keuzelijst maar één prijs op, want de huidige datum kan maar in één datumreeks vallen. Dus voor je keuzelijst maakt het niet uit of je de prijs uit een andere tabel haalt of uit de tabel Producten. Je tekstveld is sowieso gekoppeld aan je Orderregels tabel, dus ook al heeft de keuzelijst geen prijs, dan blijft het tekstveld leeg en kun je de prijs met de hand invullen.

Ik liep hiermee in gedachten tegen een aantal problemen.
Wanneer er met een prijs uit tblProductPrice gerekend wordt, hoe wordt dit dan als historie opgeslagen? Want naar mijn weten wordt alleen het ProductId in tblProductOrder opgeslagen. Dus niet de prijs die in deze gebruikt is (de datum is natuurlijk wel opgeslagen in tblOrder bij OfferDate maar ik heb nog geen idee of het enigzins eenvoudig is om dat te rapporteren in een latere rapportage. Of zou dat eenvoudig kunnen via een query (tblOrder + tblProductOrder + tblProduct + tblProductPrice)?

Denk dat als ik er nu meer inkijk dat het ook duidelijke wordt dat het dubbel opslaan van gegevens redelijk useless lijkt, mits ik het zo goed begrijp.

 

[OctaFish]

Je denkt in de goede richting. Doordat je een aparte tabel hebt voor prijzen waarin je aangeeft in welke periode een prijs geldig is, kun je op basis van de Offerdate altijd de prijs terugvinden, want de besteldatum valt altijd binnen één prijstermijn. Je gebruikt dus een soort 'Verticaal zoeken' actie (werkt niet helemaal hetzelfde als in Excel, maar het gaat om het idee) om de prijs later terug te vinden. Daar gebruik je dan ofwel een functie voor, ofwel een query met een Cartesisch product. De query kan je vervolgens gewoon gebruiken als basis voor een rapport.
Dubbel opslaan van gegevens is per definitie nutteloos. Dat gezegd hebbende: ik ken weinig mensen die een dubbel salaris afwijzen of nutteloos vinden