[Access] Finetunen query

[Millennyum]

Ik heb een Access database met een aantal tabellen die gekoppeld zijn via het veld OnderwerpNr. Voor alle records (OnderwerpNr is de primaire sleutel) wil ik bepalen wie de eindverantwoordelijke is. Dit is degene in het veld Verantwoordelijke, waarvan het veld Termijn de hoogste datum heeft. (er zijn meerdere verantwoordelijken + termijnen per OnderwerpNr) Tenzij het veld Termijn leeg is, dan is de eindverantwoordelijke de Bron.

Ik ben al de hele middag bezig en ben er uiteindelijk bijna, op een ding na.

Probleem:

De bedoeling is dat ik in de volgende query:

code:

SELECT Onderwerpen.OnderwerpNr, Stukken.Bron, Routing.Verantwoordelijke, Routing.Termijn, IIf(IsNull([Verantwoordelijke]),[Bron],[Verantwoordelijke]) AS Uiteindelijke_Verantwoordelijke
FROM (Onderwerpen INNER JOIN Stukken ON Onderwerpen.OnderwerpNr = Stukken.OnderwerpNr) INNER JOIN Routing ON Stukken.StukID = Routing.StukID
GROUP BY Onderwerpen.OnderwerpNr, Stukken.Bron, Routing.Verantwoordelijke, Routing.Termijn, IIf(IsNull([Verantwoordelijke]),[Bron],[Verantwoordelijke]), Onderwerpen.Status
HAVING (((Onderwerpen.Status)<>3));

nog een paar aanpassingen maak, zodat ik uiteindelijk dit overhoud:



Maar: als de Bron en de Verantwoordelijke die bij een OnderwerpNr horen verschillend zijn, blijkt het OnderwerpNr tweemaal weergegeven te worden in de query. Daardoor krijg ik momenteel dit:

code:

SELECT 
Onderwerpen.OnderwerpNr, 
Max(Routing.Termijn) AS MaxVanTermijn, 
IIf(IsNull([Verantwoordelijke]),[Bron],[Verantwoordelijke]) AS Uiteindelijke_Verantwoordelijke
FROM 
(Onderwerpen 
INNER JOIN Stukken 
ON Onderwerpen.OnderwerpNr = Stukken.OnderwerpNr) 
INNER JOIN Routing 
ON Stukken.StukID = Routing.StukID
GROUP BY 
Onderwerpen.OnderwerpNr, 
IIf(IsNull([Verantwoordelijke]),[Bron],[Verantwoordelijke]), 
Onderwerpen.Status
HAVING (((Onderwerpen.Status)<>3));

Hoe kan ik er voor zorgen dat hij niet kijkt of Bron en Verantwoordelijke verschillend zijn, maar dat hij gewoon kijkt welke de hoogste datum in Termijn heeft staan, en op basis daarvan de andere weglaat?

[Verwijderd]

Om te beginnen ziet 't datamodel eruit als iets waar ik slapeloze nachten van zou kunnen krijgen, maar alla, soms moet je roeien met de riemen die je hebt.
Maar alleen al door ervoor te zorgen dat er voor ieder Stukken record een Routing record bestaat, waarbij dan Routing.Verantwoordelijke gelijk is aan Stukken.Bron, wordt 't al een stuk handiger. Je bent dan op z'n minst van die gruwelijke IIf(IsNull(...)) constructie af. (Die je ook nog 's in de GROUP BY gebruikt, prima manier om de snelheid van je query om zeep te helpen...)

Verder zul je, om die laatste Verantwoordelijke te krijgen, een subselect moeten/kunnen gebruiken. Alleen weet ik niet of Access subselects ondersteunt. Of je moet 't client side oplossen met een 2e query die de laatste Verantwoordelijke ophaalt.

edit:
En verder ben ik 't helemaal met whoami eens
edit2:
Daarnet stond er toch echt een reply van whoami met alleen maar een '.'..

[ Voor 13% gewijzigd door Verwijderd op 02-04-2005 00:51 ]

[Verwijderd]

Post ook eens je datamodel c.q. relevante tabellen.
Het is nu nogal abstract.

[Millennyum]

De database is bedoeld om bij te houden in welke fase van de besluitvormingsprocedure onze vergaderstukken zich bevinden.

De opzet van de database is als volgt:

Er worden "Onderwerpen" gedefinieerd (tabel Onderwerpen), waar verschillende vergaderstukken bij kunnen horen (tabel Stukken). Ieder stuk heeft ook weer een of meerdere "routings" (welke vergaderingen heeft het stuk al doorlopen, wat was het besluit, wat is de vervolgactie, etc: tabel 'routing').

Voorbeeld van de onderlinge samenhang:

Onderwerpen	Stukken	Routing
1. Pauzeregeling	1. Voorstel nieuwe Pauzeregeling	1. Stafvergadering	Doel = advies	Besluit = negatief advies	Vervolgactie = stuk herzien	Verantw. = AA
	2. Herzien voorstel nieuwe Pauzeregeling	1. Stafvergadering	Doel = advies	Besluit = positief advies	Vervolgactie = naar management overleg	Verantw. = BB
		2. Management overleg	Doel = besluitvorming	Besluit = accoord	Vervolgactie = implementatie	Verantw. = CC

In bovenstaande voorbeeld is dus het eerste stuk gekoppeld aan één routingsregel in de tabel Routing en heeft het tweede stuk twee routingsregels waaraan hij gekoppeld is.

Als een stuk nog niet door een vergadering is geweest maar alleen ingediend is, bestaat er dus nog geen routing record. Bij iedere routing-record kan bovendien een verantwoordelijke horen die die vervolgactie moet uitvoeren. Maar er is maar één bron: degene die het stuk destijds heeft geschreven.

Ik hoop dat dit begrijpelijk is

[ Voor 6% gewijzigd door Millennyum op 02-04-2005 19:05 ]

[Verwijderd]

Kan je hier iets mee?

TOP n [PERCENT] Retourneert een bepaald aantal records boven of onder aan een bereik, aangeduid door de component ORDER BY. Als u de namen van de 25 beste studenten wilt zien uit de klas van 1994, gebruikt u de volgende syntaxis:
SELECT TOP 25
Voornaam, Achternaam
FROM Studenten
WHERE Examenjaar = 1994
ORDER BY GemiddeldCijfer DESC;

Als u de component ORDER BY niet gebruikt, levert de query 25 willekeurige records op uit de tabel Studenten die voldoen aan de component WHERE.

Met het predikaat TOP wordt geen keuze gemaakt tussen gelijke waarden. Dit betekent dat in het vorige voorbeeld 26 records worden geretourneerd wanneer het vijfentwintigste en zesentwintigste gemiddelde gelijk zijn.

U kunt ook het gereserveerde woord PERCENT gebruiken om een bepaald percentage records te retourneren die boven of onder aan een bepaald bereik vallen, aangeduid door de component ORDER BY. Als u niet de 25 beste studenten wilt zien, maar de tien procent die het slechtst presteren, gebruikt u de volgende syntaxis:

SELECT TOP 10 PERCENT
Voornaam, Achternaam
FROM Studenten
WHERE Examenjaar = 1994
ORDER BY GemiddeldCijfer ASC;

Met het predikaat ASC worden de laagste waarden geretourneerd. De waarde na TOP moet een niet-ondertekende integer zijn.

TOP geeft niet aan of de query kan worden bijgewerkt.

[Millennyum]

Nee, want ik weet niet van tevoren hoeveel resultaten de query moet opleveren.

Verwijderd schreef op vrijdag 01 april 2005 @ 23:21:
Om te beginnen ziet 't datamodel eruit als iets waar ik slapeloze nachten van zou kunnen krijgen, maar alla, soms moet je roeien met de riemen die je hebt.

Wat is er mis mee?

[...]Je bent dan op z'n minst van die gruwelijke IIf(IsNull(...)) constructie af. (Die je ook nog 's in de GROUP BY gebruikt, prima manier om de snelheid van je query om zeep te helpen...)

Zonder die group by werkt de query niet... hoe zou het volgens jou dan wel moeten? Hij is door Access zelf gegenereerd via de Query wizard.

[ Voor 81% gewijzigd door Millennyum op 03-04-2005 12:12 ]

[Millennyum]

*KICK*

Iemand met een idee?

[Millennyum]

Laatste poging...

Weet niemand een oplossing of heb ik de situatie gewoon niet goed uitgelegd?

[D4Skunk]

SQL:

create view samengevoegd as
select 
  onderwerpen.onderwerpnr as o_nr,
  onderwerpen.Status as o_status, 
  iif(isnull(Routing.termijn),#1/1/1900#,Routing.Termijn) as r_termijn
  IIf(isnull(Routing.termijn),[Bron],[Verantwoordelijke]) AS v_Verantwoordelijke
FROM (
  Onderwerpen 
  INNER JOIN Stukken 
    ON Onderwerpen.OnderwerpNr = Stukken.OnderwerpNr) 
  INNER JOIN Routing 
   ON Stukken.StukID = Routing.StukID

resultaat :

SELECT 
  a.o_nr,
  a.o_status,
  iif(a.r_termijn=#1/1/1900#,null,a.r_termijn),
  a.v_verantwoordelijke
FROM samengevoegd a
WHERE a.r_termijn= (select max(b.r_termijn) from samengevoegd b where a.o_nr=b.o_nr)

Indien dit niet duidelijk genoeg is, laat me iets weten...
Naar wie mag ik mijn factuurtje opsturen ?

[Millennyum]

Het spijt me, blijkbaar ben ik te veel n00b om je query te begrijpen...
Ik waardeer het heel erg dat ik een zowat kant-en-klare query krijg ipv aanwijzingen, maar ik heb geen idee wat ik er mee moet...

Create view... wat doet dat?? Hoe moet ik het stukje voor en na 'resultaat:' combineren? En waar komen die a en b vandaan?

Is een virtuele ook goed ipv betaling van de factuur?

[ Voor 11% gewijzigd door Millennyum op 08-04-2005 15:14 ]

[Boss]

create view werkt niet in Access. Je moet een nieuwe query maken en daar het eerste stuk code in plakken. Deze query sla je op onder de naam samengevoegd.

Daarna maak je een nieuwe query en daar komt het tweede stuk SQL in (dat gebruik maakt van het eerste stuk).

[Millennyum]

Dankje voor de aanwijzingen, Access-goeroe Boss !

De query van D4Skunk klopte bijna. Dit is wat ik ervan heb gemaakt en nu werkt het zoals ik het wil \o/

Query 1 "Samengevoegd"

code:

SELECT Onderwerpen.OnderwerpNr AS Onderwerp_Nr, 
Onderwerpen.Status AS Onderwerp_Status, 
IIf(IsNull(Routing.termijn),#1/1/1900#,Routing.Termijn) AS Routing_Termijn, 
IIf(IsNull(Routing.termijn),
[Bron],
[Verantwoordelijke]) AS Def_Verantwoordelijke
FROM (Onderwerpen INNER JOIN Stukken ON Onderwerpen.OnderwerpNr = Stukken.OnderwerpNr) 
INNER JOIN Routing ON Stukken.StukID = Routing.StukID
WHERE (((Onderwerpen.Status)<>3));

Query 2 "Resultaat"

code:

SELECT DISTINCT a.Onderwerp_Nr AS OnderwerpNummer, 
a.Def_Verantwoordelijke AS [Def Verantwoordelijke]
FROM samengevoegd AS a
WHERE (((a.Routing_Termijn)=(select max(b.Routing_Termijn) 
from samengevoegd b 
where a.Onderwerp_Nr=b.Onderwerp_Nr)));

voor jullie allebei

[ Voor 4% gewijzigd door Millennyum op 11-04-2005 14:41 ]

[Boss]

graag gedaan hoor

[D4Skunk]