[SQL/Access] Relaties naast elkaar in 1 record

[Orphix]

Hallo, ik zit hier met het volgende. Voor een bedrijf worden planningen bijgehouden, bij elke opdracht dus. Het concept is ongeveer het volgende (db structuur):

code:

| Opdracht   |
|------------|
| opdrachtNr |

| planningen   |
|--------------|
| planningNr   |
| planningNaam |

| opdrachtPlanningen |
|--------------------|
| opdrachtNr       |
| planningNr       |
| geplandeDatum |

In planningen worden dus de verschillende soorten planningen bijgehouden (bijvoorbeeld 'verpakken' of 'bereiden') en in opdrachtPlanning wordt voor een opdracht/planning combinatie bijgehouden wat de geplande datum is.

Okay, dit werkt. Maar nu zijn ze gewend op de produktie-afdeling een planningsoverzicht te zien waarbij alle opdrachtplanningen naast elkaar staan. Ik heb in het vernieuwde systeem de opdrachten van de planningen gescheiden ivm met een groot aantal planningen en het feit dat lang niet altijd alle planningen van toepassing zijn.

Stel je hebt de planning 'verpakken' en de planning 'bereiden', de plannings-uitdraai zou dan dit zijn:

code:

|     Opdracht    | Bereidings Datum | Verpakken Datum |
  4021           9 jul 2002    12 jul 20002

Nu kan het zo zijn dat bv opdracht# 4022 geen verpakkingsdatum nodig heeft. Dit wordt dan niet opgeslagen in de database.

Hoe krijg ik nu op een mooie manier die velden naast elkaar ook als ze niet van toepassing zijn voor die opdracht?

(indien mijn design verbeterd kan worden hoor ik dat ook graag )

[Orphix]

Ik heb dus uiteindelijk wel een oplossing gemaakt maar die vind ik zelf een beetje vies. Dat gaat als volgt:

- In je select clause geef je alle velden op die je nodig hebt op het rapport.

- Voor elke planning-type die er is vraag je de informatie op deze zet je in de overeenkomstige planning-velden, voor de overige planning-types vul je dan een '0' in.

- Via UNION's plak je al die selects bij elkaar.

- Je groupeert dan op opdracht nummer en telt de planningsdata bij elkaar op.

Dus via de union krijg je dan ongeveer dit resultaat:

code:

| Opdrachtnr | verpakken  | bereiden | verzenden   |
|  3000 | 2 jul 2002 |    0     |     0  |
|  3000 |     0 |    0     | 14 jul 2002 |

Met grouperen krijg je dit in 1 record.

Je krijgt dan wel het resultaat wat ik wil maar het is vrij complex en niet makkelijk onderhoudbaar

[Goodielover]

Is het wel zo dat er in het overzicht in de eerste kolom altijd hetzelfde staat (dus bereidingsdatum) en in de tweede kolom altijd .... en in de derde ...
of wordt het overzicht voor 1 planning gemaakt.
concreet zou het overzicht er als volgt uit kunnen zien:

code:

Opdracht  | Bereiddingsdatum | Verpakkingsdatum | Verzenddatum
4021    |   21-12-2001     |  23-12-2001    | 31-12-2001
4022    |           |  24-12-2001   | 31-12-2001
4023    |   21-12-2001     |            | 31-12-2001

En is het aantal planningsstappen beperkt en vooraf bekend?

[Goodielover]

Jouw oplossing komt dichtbij mijn voorstel voor een oplossing. Mijn oplossing is iets efficienter, maar eerst graag antwoord op mijn vorige vraag

[Verwijderd]

Waarom zo moeilijk? Volg gewoon het 'datamodel':

code:

--------------------------------------------------------------------------------
| Opdracht |   Activiteit  |    Datum   |
  4021    Bereiden  9  jul 2002
  4021    Verpakken     12 jul 2002
  4022    Verpakken     15 jul 2002
--------------------------------------------------------------------------------

Voordeel is dat je dan ook nog kan sorteren op opdrachtnummer, activiteit en datum of een combinatie hiervan.

[Orphix]

Op dinsdag 09 april 2002 20:36 schreef Goodielover het volgende:
Is het wel zo dat er in het overzicht in de eerste kolom altijd hetzelfde staat (dus bereidingsdatum) en in de tweede kolom altijd .... en in de derde ...
of wordt het overzicht voor 1 planning gemaakt.
concreet zou het overzicht er als volgt uit kunnen zien:

code:

Opdracht  | Bereiddingsdatum | Verpakkingsdatum | Verzenddatum
4021    |   21-12-2001     |  23-12-2001    | 31-12-2001
4022    |           |  24-12-2001   | 31-12-2001
4023    |   21-12-2001     |            | 31-12-2001

En is het aantal planningsstappen beperkt en vooraf bekend?

Ja in principe wel, een produktie-afdeling is namelijk maar in een beperkt aantal planningen geinteresseerd.

[Orphix]

Op dinsdag 09 april 2002 20:40 schreef remedy70 het volgende:
Waarom zo moeilijk? Volg gewoon het 'datamodel':

code:

--------------------------------------------------------------------------------
| Opdracht |   Activiteit  |    Datum   |
  4021    Bereiden  9  jul 2002
  4021    Verpakken     12 jul 2002
  4022    Verpakken     15 jul 2002
--------------------------------------------------------------------------------

Voordeel is dat je dan ook nog kan sorteren op opdrachtnummer, activiteit en datum of een combinatie hiervan.

Ja maar het probleem is dat de mensen op de produktie-afdeling ter plekke graag een overzicht willen hebben van alle openstaande opdrachten om zelf een interne planning te maken. De manier waarop ik het beschreef is iets hoe ze al 1.5 jaar mee werken en vrij effectief werkt. Zoals jij het hier beschrijft zou het een totaal onoverzichtelijke planning worden.

Het gaat me niet zozeer om de output die een query me geeft, als jij een makkelijke methode weet om hier een rapport van te maken die de bovengenoemde planning kan weergeven ben ik ook gelukkig!

[Orphix]

Op dinsdag 09 april 2002 20:39 schreef Goodielover het volgende:
Jouw oplossing komt dichtbij mijn voorstel voor een oplossing. Mijn oplossing is iets efficienter, maar eerst graag antwoord op mijn vorige vraag

Wat zou je efficienter doen?

[Verwijderd]

De manier waarop ik het beschreef is iets hoe ze al 1.5 jaar mee werken en vrij effectief werkt.

De informatie die het 'oude' overzicht gaf, wordt ook in mijn voorstel getoond, zei het op 2 regels ipv 1. Dus als het oude goed werkte, zal dit het ook doen.

Nog een argument: Nu is het zo dat er maar 2 activiteiten zijn, in de toekomst wil het bedrijf misschien wel meer gedetailleerd plannen en dus meer activiteiten in de planning opnemen. Als dat gebeurd, moet je een nieuw rapport maken, in mijn voorstel niet.

[Goodielover]

Gebruik een MAX(DECODE() constructie.
in MySQL-code ziet je SQL er alsvolgt uit:

code:

select o.opdrachtnr
    ,max(iif(op.planningnr=1,op.geplandedatum,null)) bereidingsdatum 
    ,max(iif(op.planningnr=2,op.geplandedatum,null)) verpakkingsdatum
    ,max(iif(op.planningnr=3,op.geplandedatum,null)) verzenddatum
    ,max(iif(op.planningnr=4,op.geplandedatum,null)) factureringsdatum
from   opdracht o
    ,opdrachtplanning op
where  o.opdrachtnr = op.opdrachtnr
and    op.planningsnummer in (1,2,3,4)
group by o.opdrachtnummer

Je codeert indit geval dus wel dat planningnr 1 de bereidingsdatum is.
door de IN in de WHERE-clause kan je de soorten beperken, anders kan je ook opdrachten krijgen die wel een planning hebben maar niet een van deze soorten.
Als je het heel mooi wilt maken, genereer je dit statement uit je planningstabel. De omschrijving wordt dan de alias van de kolom in dit statement. Geen moeilijke generator dus.

[Orphix]

Op dinsdag 09 april 2002 20:54 schreef remedy70 het volgende:

De informatie die het 'oude' overzicht gaf, wordt ook in mijn voorstel getoond, zei het op 2 regels ipv 1. Dus als het oude goed werkte, zal dit het ook doen.

Nog een argument: Nu is het zo dat er maar 2 activiteiten zijn, in de toekomst wil het bedrijf misschien wel meer gedetailleerd plannen en dus meer activiteiten in de planning opnemen. Als dat gebeurd, moet je een nieuw rapport maken, in mijn voorstel niet.

Dat laatste klopt idd, maar het aantal plannings-mogelijkheden per afdeling/rapport zal niet zo snel veranderen. Een produktie proces verandert niet zo snel. Ik heb het zo opgezet om de invoer/administratie van gegevens duidelijker en veiliger te maken door planningen die niet van toepassing zijn niet te laten zien.

In het echt zijn er ongeveer 6 planningen waar ze rekening mee houden. 6 regels onder elkaar met ongeveer 120 opdrachten gaat redelijk in de papieren lopen en raakt het overzicht kwijt.

[Orphix]

Op dinsdag 09 april 2002 20:55 schreef Goodielover het volgende:
Gebruik een MAX(DECODE() constructie.
in MySQL-code ziet je SQL er alsvolgt uit:

code:

select o.opdrachtnr
    ,max(iif(op.planningnr=1,op.geplandedatum,null)) bereidingsdatum 
    ,max(iif(op.planningnr=2,op.geplandedatum,null)) verpakkingsdatum
    ,max(iif(op.planningnr=3,op.geplandedatum,null)) verzenddatum
    ,max(iif(op.planningnr=4,op.geplandedatum,null)) factureringsdatum
from   opdracht o
    ,opdrachtplanning op
where  o.opdrachtnr = op.opdrachtnr
and    op.planningsnummer in (1,2,3,4)
group by o.opdrachtnummer

Je codeert indit geval dus wel dat planningnr 1 de bereidingsdatum is.
door de IN in de WHERE-clause kan je de soorten beperken, anders kan je ook opdrachten krijgen die wel een planning hebben maar niet een van deze soorten.

WOW!!
Thanks als dit werkt is het een STUK beter dan mijn query van ~70 regels
Dat hard-coden is geen probleem, dat doe ik nu ook.
Ik kan het helaas nu niet proberen, zal het morgen direct testen.

Dit is ook nog een keer aan te passen door wat minder ervaren mensen.

Als je het heel mooi wilt maken, genereer je dit statement uit je planningstabel. De omschrijving wordt dan de alias van de kolom in dit statement. Geen moeilijke generator dus.

Hoe zou ik zoiets met Access op moeten lossen. Gewoon een knopje maken met VBA erachter?

_{nooit veel mee gewerkt maar nu heb ik een reden om te op VBA, geeneens een replace functie}

[Goodielover]

Misschien door een extra veld op te nemen in de planningstabel: af_te_drukken_op_positie integer
en dan voor het moment waarop je het select statement richting DB stuurt de query op te bouwen.
Of dit in Access kan weet ik niet. Ik ken alleen SQL als taal erg goed, maar van de verschillende host-talen heb ik weinig verstand. Door sit forum begin ik nu wel een beetje verstand te krijgen van PHP.