[ACCESS] In de knoop met structuur

[YellowOnline]

Zoals ik in een andere thread stelde: ik ben aangeworven voor een Excel project dat achteraf gezien niet echt werkbaar bleek in Excel. Ik mag nu in Access werken, maar op 48 uur Access-specialist worden gaat niet van een leien dakje. Het netjes opstellen van de database was het eenvoudige stuk, nu begin ik problemen te krijgen met de structuur (terwijl het ergste stuk eigenlijk de berekeningen zou moeten worden). Ik ga deze week een snelcursus SQL volgen (links zijn altijd welkom), maar hulp met de relaties zou welkom zijn:

(Edit: 20 overbodige paragrafen geknipt, het zag er echt te ingewikkeld uit en was bovendien verwarrend)

Mijn einddoel is dit:

(smaller krijg ik het echt niet of het wordt onleesbaar)

Het probleem zit 'm in die trapstructuur. Ingrediënten uit tblFormulae moet worden gekoppeld aan tblIngredients (een tabel met ingrediënteigenschappen) TENZIJ één (of enkele) der ingrediënt zelf een formule is. In dat geval moet een andere formule opgehaald worden in tblWhiteBase OF tblPremix en de subingrediënten van die formule gekoppeld worden aan tblIngredients TENZIJ één (of enkele) van die ingrediënten alweer een formule is (enkel mogelijk bij tblWhiteBase). In dat geval moet de formule opgehaald worden in tblPremix en de ingrediënten van die formule gekoppeld worden aan tblIngredients. En hier stopt het - er moet in principe geen diepere trapstructuur voorzien worden.

FNC en RMS behoren tot hetzelfde nummersysteem (industriestandaard die ik niet op eigen houtje kan wijzigen). Eigenlijk mag ik voor beide de naam RMS gebruiken, maar Access (en andere db-programma's waarschijnlijk) laat niet toe dat er twee velden met dezelfde naam in één tabelopmaak zitten (de ene keer zogezegd als primary key en de andere keer als foreign key). Dus de ene keer kan dat nummer naar een formule verwijzen, de andere keer naar een ingrediënt. Dit is eigenlijk, samengevat, mijn hele probleem. Idealiter gooi ik die twee tables (tblWhiteBase en tblPremix) bij de andere formules in de tabel tblFormulae. Alleen: die tabel naar zichzelf laten verwijzen lijkt op het eerste zicht een onmogelijke zaak. Waarschijnlijk omdat je aldus een oneindige loop zou kunnen veroorzaken. Maar misschien lukt het wel met SQL - dat heb ik nog niet op 48 uur kunnen leren.

offtopic:
Ja, ik weet het, ze hadden iemand anders moeten inhuren voor dit project... maar ik moet het echt wel zelf opgelost krijgen nu

Hulp is zeer welgekomen. Ik ben wat uitgeput door 16 uur/dag bij te leren momenteel (niet enkel Access, ook over chemie ). Tegen vrijdag moet ik dit tot een simulator ombouwen dus ik ben zwaar aan het stressen atm :x

[ Voor 71% gewijzigd door YellowOnline op 29-09-2008 00:13 . Reden: Totaal herwerkt; herleid tot een 4e van de originele tekst of zo :) ]

[pedorus]

Tsja, lastig...

Allereerst: stap van de Hongarian notation af. tblXXX is onzin, gebruik gewoon XXX. Kies daarnaast duidelijk voor Engels of Nederlands, en gebruik niet te veel onnodige afkortingen als naam. IngName lijkt me dus of gewoon name, of ingredientName.

Elke tabel heeft nu een ID gekregen, waarschijnlijk van access. Dat is niet altijd nodig als er al een andere goede primary key is, hier bijv. bij Ingredients niet, en bij Formulae ook niet zou ik zeggen. Daarnaast zorg ik er persoonlijk altijd voor dat ik tabelnaamId als naam gebruik, wat duidelijker is bij het opvragen in SQL.

Als ik het goed snap heeft Formulae een soort tree-structuur, waar SQL-databases in het algemeen niet zo goed in zijn. Een Formulae levert zelf een Substance op, maar bestaat ook uit meerdere Substance's in een bepaalde hoeveelheid. Als je dan alle 'broningrediënten' wil hebben van een substantie, dan moet je daarbij bijvoorbeeld een hulptabel gebruiken, of meerdere queries uitvoeren totdat er geen nieuwe ingredienten meer zijn.

Ik zou zelf denken dat je voor Formulae, (sub(sub(...)))Premix, en Ingredient kan volstaan met zo'n twee tabellen:

1. Substance(substanceId (Primary Key), name, propertyId, purity)
2. Recipe(substanceId (Lookup Wizard naar Sustance, deel Primary Key), ingredientId (Lookup Wizard naar Sustance, deel Primary Key), amount)

Of een Substance een 'broningrediënt' is of een mix zie je dan vanzelf: bij een mix is er een bijbehorend Recipe. Het aantal niveau's in mixen is dan ook onbeperkt, en het gaat niet mis als het er meer dan 3 zijn.
(Verder moet Volumes dan ook verwijzen naar Substance ipv Formulea.)

[YellowOnline]

Dank je voor je reactie. Wat de notatie betreft: ach, dat is maar een naam. Ik geef toe dat het in principe niet nodig is. Maar de taal is toch enkel Engels? Moet wel, ik ben de enige Nederlandstalige

Edit Oh, ik snap wat je bedoelt: dat ik Nederlands gebruik voor mijn tabelvoorbeelden met brood en bakkerijen. Dat is enkel een voorbeeld hoor, de werkelijke inhoud zijn namelijk chemische formules en ingrediënten. De werkelijke fieldnames zijn dan ook heel anders - het was maar een vereenvoudiging. tblPrices ziet er in het echt bijvoorbeeld zo uit:

code:

ID RMS      Plant    Currency Year Quarter Cost   
1  76543210 Nagasaki JPY      2008 Q2      0.7451

Maar ik heb mijn TS helemaal herwerkt: beknopter en duidelijker.

pedorus schreef op zondag 28 september 2008 @ 20:50:
Tsja, lastig...
Elke tabel heeft nu een ID gekregen, waarschijnlijk van access. Dat is niet altijd nodig als er al een andere goede primary key is, hier bijv. bij Ingredients niet, en bij Formulae ook niet zou ik zeggen.

Vandaar dat bij Ingredients en Formulae de primary key niet de ID is: in die twee tabellen kunnen er inderdaad geen dubbels zijn. In de andere tabellen zijn die echter wel aanwezig, dus daar zit ik nogal onvermijdelijk met een autoID opgescheept.

pedorus schreef op zondag 28 september 2008 @ 20:50:
Als ik het goed snap heeft Formulae een soort tree-structuur, waar SQL-databases in het algemeen niet zo goed in zijn. Een Formulae levert zelf een Substance op, maar bestaat ook uit meerdere Substance's in een bepaalde hoeveelheid. Als je dan alle 'broningrediënten' wil hebben van een substantie, dan moet je daarbij bijvoorbeeld een hulptabel gebruiken, of meerdere queries uitvoeren totdat er geen nieuwe ingredienten meer zijn.

Inderdaad. Ik heb de laatste afbeelding vervangen zodat het eigenlijke idee duidelijker is.

pedorus schreef op zondag 28 september 2008 @ 20:50:
Ik zou zelf denken dat je voor Formulae, (sub(sub(...)))Premix, en Ingredient kan volstaan met zo'n twee tabellen:

1. Substance(substanceId (Primary Key), name, propertyId, purity)
2. Recipe(substanceId (Lookup Wizard naar Sustance, deel Primary Key), ingredientId (Lookup Wizard naar Sustance, deel Primary Key), amount)

Hier begrijp ik helaas niets van. Hopelijk binnen enkele dagen wél, maar nu ben ik nog te groen in Access (uh, gisteren begonnen...).

[ Voor 11% gewijzigd door YellowOnline op 29-09-2008 00:08 . Reden: Zie "Edit" ]

[pedorus]

offtopic:
Ik tref opeens een heel nieuw draadje aan. Deze post staat los van mijn vorige

YellowOnline schreef:
Alleen: die tabel naar zichzelf laten verwijzen lijkt op het eerste zicht een onmogelijke zaak.

Even getest, en dat kan wel degelijk in de relationships view. Als je dan het dialoogje Edit Relationships hebt zit daar een knopje Create New... waarbij je dezelfde tabel 2x kan selecteren. Het is enkel niet wat hier je wilt denk ik, omdat je toch geen referential integrity kan gebruiken omdat Formulae->RMS zowel naar Formulae->FCN als Ingredients->RMS kan verwijzen. Daarnaast zie je zo'n self-join niet terug in de query design view.

Een nette oplossing zou een hulptabeltabel Substance(RMS) zijn, waar zowel Formulae->FCN als Ingredients->RMS een Foreign Key naar is. Met Validation rules kun je dan afdwingen dat een FCN niet zowel in Ingredients als in Formulae kan zitten. Voor queries maakt dit allemaal toch niet uit, die blijven hetzelfde, dus je kan dat rustig laten zitten.

Bij de query design view kun je dezelfde tabel meerdere keren toevoegen en linkjes ertussen maken. Zo kun je bijvoorbeeld een query maken als:

SQL:

SELECT Formulae_2.RMS, Formulae.PTA*Formulae_1.PTA*Formulae_2.PTA AS PTA
FROM (Formulae 
    INNER JOIN Formulae AS Formulae_1 ON Formulae.RMS = Formulae_1.FCN) 
    INNER JOIN Formulae AS Formulae_2 ON Formulae_1.RMS = Formulae_2.FCN
WHERE Formulae.FCN=123223;

waarbij je alle ingrediënten die in een subsubformule van formule no 123223 voorkomen terugkrijgt. (Om te zien wat ik bedoel kun je dit in de sql-view invoeren en op design-view klikken.)

Waarschijnlijk is een query met alle FCN's, onderliggende RMS-en en hoeveelheden (PTA's neem ik aan) wel handig. Dat kan als volgt, als er maximaal 3 niveau's zijn:

SQL:

SELECT Formulae.FCN,Formulae.RMS, Formulae.PTA AS PTA
FROM Formulae

UNION ALL

SELECT Formulae.FCN,Formulae_1.RMS, Formulae.PTA*Formulae_1.PTA AS PTA
FROM Formulae INNER JOIN Formulae AS Formulae_1 ON Formulae.RMS = Formulae_1.FCN

UNION ALL

SELECT Formulae.FCN,Formulae_2.RMS, Formulae.PTA*Formulae_1.PTA*Formulae_2.PTA AS PTA
FROM (Formulae INNER JOIN Formulae AS Formulae_1 ON Formulae.RMS = Formulae_1.FCN) 
    INNER JOIN Formulae AS Formulae_2 ON Formulae_1.RMS = Formulae_2.FCN 

(Access kan geen unions aan in de design view)

Omdat hier niet gejoint is met Ingredients staan hier de mixen en onderliggende RMS-en er beide tussen. Maar na joinen met Ingredients is dat als het goed is opgelost. (Ik ga er vanuit dat een RMS of als FCN in Formulae zit of als RMS in Ingredients, en niet beide.)

Het kan nu zijn dat dezelfde (FCN,RMS)-combinatie meerdere keren voor komt, bijvoorbeeld als een submix een RMS-code heeft die ook direct voorkomt. Je kan dat optellen met een GROUP BY, mocht dat nodig zijn. Dit kan zelfs gewoon met de design view op de query hierboven.

[YellowOnline]

Bedank Pedorus! Dit begint in de buurt te komen.
Ik ben aan het nadenken over een manier om een onderscheid te kunnen maken tussen de Ingredienten en de subingredienten - waarschijnlijk lukt dat wel met labels.

(Ik ga er vanuit dat een RMS of als FCN in Formulae zit of als RMS in Ingredients, en niet beide.)

Helaas: het onderscheid FCN en RMS heb ik zelf gemaakt omdat twee velden niet dezelfde naam kunnen hebben. Ik zit nu dus even in de knoop hiermee

Nu moet ik wel een probleem oplossen met die PTA (wat eigenlijk Parts To Add is, komt min of meer overeen met hoeveelheid inderdaad). Ik snap niet waaorm in je SQL die PTAs vermenigvuldigd worden met elkaar, maar de output krijgt gekke waardes. PTA is normaal gezien een waarde tot 5 cijfers na de komma nauwkeurig. Nu worden al die waardes 15-cijferig en de helft in wetenschappelijke notatie Ik ken de reden wel: PTA is als single gedefinieerd omdat de nauwkeurigheid anders beperkt is. Door de vermenigvuldiging in jouw code wordt dat veld echter double denk ik.

Edit. Grrrr. Het is nu double en de waardes kloppen nog steeds niet.

Enfin, ik ga er nog wat op zoeken. Nogmaals mijn dank in ieder geval.

[ Voor 119% gewijzigd door YellowOnline op 01-10-2008 15:01 ]