top 10

[jwaque]

Ikheb een tabel met de kolommen:
afdeling
geneesmiddel
bedrag

Nu wil ik per afdeling de top 10 geneesmiddelen (een sommatie van) sorteren.

Ik kan een query maken waarin de bedragen per geneesmiddel worden gesommeerd. met TOP 10 krijg in dan de top10 geneesmiddelen van alle afdelingen door elkaar en niet per afdeling. Weten jullie hoe ik per afdeling een TOP10 kan samenstellen? Ik kan natuurlijk ook losse querys maken maar dat lijkt me niet de meest elegante oplossing.

hierna wil ik per afdeling een report maken met daarin de top10 in tabel en grafiek vorm.

alvast bedankt voor jullie hulp.

 

[Tardis]

Ik kan een query maken waarin de bedragen per geneesmiddel worden gesommeerd

Neem in je query ook de afdeling op.

Tardis

 

[jwaque]

afdeling stond er ook bij. dit is mijn query nu

SELECT TOP 10 Verpleeghuizen.Instelling, Verpleeghuizen.Toepassing, Sum(Verpleeghuizen.[Bedrag 2007]) AS [SumOfBedrag 2007]
FROM Verpleeghuizen
GROUP BY Verpleeghuizen.Instelling, Verpleeghuizen.Toepassing
ORDER BY Sum(Verpleeghuizen.[Bedrag 2007]) DESC;

maar nu krijg is dus instellingen (=afdeling) doorelkaar heen en geen top 10 per instelling

 

[OctaFish]

Jouw query levert de Top 10 op gebaseerd op de bedragen. Als je een Top 10 per afdeling wilt kun je voor elke afdeling een aparte top 10 maken, dus jouw query met een filtering op afdeling, en die met een Union weer verzamelen naar één query.

 

[jwaque]

kan je nog een tipje van de sluier oplichten. hoe maak ik een filter op mijn query zodat ik hem daarna weer kan samenvoegen? super bedankt voor je hulp zover.

SELECT Verpleeghuizen.Instelling
FROM Verpleeghuizen
GROUP BY Verpleeghuizen.Instelling;

UNION SELECT TOP 10  Verpleeghuizen.Instelling, Verpleeghuizen.Toepassing, Sum(Verpleeghuizen.[Bedrag 2007]) AS [SumOfBedrag 2007]
FROM Verpleeghuizen
GROUP BY Verpleeghuizen.Instelling, Verpleeghuizen.Toepassing
ORDER BY Sum(Verpleeghuizen.[Bedrag 2007]) DESC;

dan krijg ik de melding de number of colums..... don not match

 

[OctaFish]

In je queryontwerp heb je een regel Criteria. Daar zet je de naam van éen instelling. Deze query sla je op, onder een eigen naam. Vervolgens verander je de naam van de instelling, en sla je de query weer op onder een nieuwe naam. En zo ga je door tot je ze allemaal hebt. Een query ziet er dan zo uit:

SELECT TOP 10 Instelling, Sum([Bedrag 2007]) AS [Som 2007]
FROM Verpleeghuizen
WHERE Instelling = 'Zonnegloren'
GROUP BY Instelling
ORDER BY Sum([Bedrag 2007]) DESC;

De eindquery maak je dan zo: (moet je intypen, want je kunt met het ontwerpscherm geen Union query maken)

SELECT * FROM qHuis1
UNION ALL
SELECT * FROM qHuis2
UNION ALL
SELECT * FROM qHuis3
UNION ALL
SELECT * FROM qHuis4
GROUP BY Instelling

etc.

 

[jwaque]

zoiets had ik ook vaag bedacht maar wat dan als er een instelling bij komt. dan moet ik dus weer een query maken. (onhandig als ik de query ook door andere wil laten gebruiken) Is er niet een mogelijkheid om een query steeds met een andere parameter uit een tabel te laten draaien oid.

 

[OctaFish]

Ik zou zelf een knop op een formulier maken die e.e.a. uitvoert op basis van de records in je tabel. Daarbij maak je dan gebruik van een Recordset om eerst alle namen van de instellingen uit te lezen, waarna je met nieuwe recordsets de Top 10 queries opbouwt, en gelijk de Unionquery opbouwt.
Want dan heb je er verder geen omkijken meer naar...
Wel eens gedaan op die manier?

 

[jwaque]

Ik denk het niet. Ik heb nu wel een form gebaseerd op tabel. als ik nu een report open, dan komt dat formulier waarin je een instelling kan kiezen en vervolgens wordt de query uitgevoerd en in het report gedumpt. zo kan ik niet alle instellingen tegelijk doen maar kom ik een eind. Nu nog kijken of ik er een grafiek bij kan maken.
maar mocht je een tip hebben om alles in een run te doen graag.
bedankt zover.

 

[OctaFish]

Als je een voorbeeldje kan maken met wat dummy gegevens, dan wil ik wel wat uitproberen.

 

[Tardis]

Dit zou zonder een UNION moeten kunnen.
Sluit even een voobeeldbestand bij.

Tardis

 

[jwaque]

Bij deze. Ik ben benieuwd wat de oplossing wordt.

 

[OctaFish]

Hierin een uitgewerkt voorbeeldje. Kijk naar de VBA code om te zien hoe 't werkt. wachten we ondertussen op de oplossing zonder Union query...

 

[jwaque]

Super bedankt. Hier kan ik verder mee.

 

[Tardis]

Zie bijlage voor een oplossing waarin je maar 1 query nodig hebt.
Querynaam is qry_top10_in_1_keer.

Vaak worden dit soort vraagstukken in 2 stappen opgelost:

- maak eerst een totalenquery
- gebruik deze query om verdere bewerkingen uit te voeren (zoals een top 10 samenstellen).

Ook deze oplossing staat in de bijlage.

Stuk praktischer en dynamischer dan het toepassen van een UNION.

Tardis

 

[Tardis]

Om de een of andere reden kan ik geen bijlage toevoegen(?).

Dan maar even de SQL weergaves

Via 1 query

SELECT *
FROM (SELECT Instelling, Toepassing, Sum([Bedrag]) AS SumOfBedrag FROM blad1 GROUP BY Instelling, Toepassing ORDER BY Sum([Bedrag]) DESC)  AS temp1
WHERE Toepassing IN ( SELECT TOP 10 Toepassing FROM  ( SELECT Instelling, Toepassing, Sum([Bedrag]) AS SumOfBedrag FROM blad1 GROUP BY Instelling, Toepassing ORDER BY Sum([Bedrag]) DESC ) AS temp2 WHERE temp1.Instelling = temp2.Instelling ORDER BY temp2.SumOfBedrag DESC )
ORDER BY temp1.Instelling, temp1.SumOfBedrag DESC;

Via 2 queries

Bereken eerst de totalen per toepassing, sla de query op met als naam qry_totaal_per_toepassing:

SELECT blad1.Instelling, blad1.Toepassing, Sum(blad1.[Bedrag]) AS SumOfBedrag
FROM blad1
GROUP BY blad1.Instelling, blad1.Toepassing
ORDER BY Sum(blad1.[Bedrag]) DESC;

Maak vervolgens een nieuwe query.
Baseer deze op de vorige query:

SELECT qry_totaal_per_toepassing.Instelling, qry_totaal_per_toepassing.Toepassing, qry_totaal_per_toepassing.SumOfBedrag
FROM qry_totaal_per_toepassing
WHERE qry_totaal_per_toepassing.Toepassing IN
(
SELECT TOP 10 temp.Toepassing
FROM qry_totaal_per_toepassing AS temp
WHERE temp.Instelling = qry_totaal_per_toepassing.Instelling
ORDER BY temp.SumOfBedrag DESC
)
ORDER BY qry_totaal_per_toepassing.Instelling, qry_totaal_per_toepassing.SumOfBedrag DESC;

Tardis

 

[jwaque]

Tardis bedankt. Ik ga er in het weekend mee aan de slag.

 

[jwaque]

helemaal geweldig. ik begrijp de query nog niet helemaal maar hij werkt fanastisch. bedankt

 

[OctaFish]

Wat je doet met die query is een zogenaamde Subquery maken. De Subquery staat in het Criteriumveld van het veld Toepassing, omdat je dat als basis voor de Top 10 wilt gebruiken. Deze subquery maakt een Top10 lijst, zoals je in de code kunt zien. Per Toepassing worden er dus 10 records uitgefilterd.
De subquery werkt prima, zoals je hebt gezien, maar is niet overdreven snel in het gebruik, omdat er veel werk op de achtergrond moet worden verricht. Bij grote aantallen zul je dus zien dat het enige tijd duurt voordat de lijst is opgebouwd. Daarom heb ik een andere route gevolgd, die een stuk sneller werkt. En ik wilde Tardis uiteraard niet voor de voeten lopen, want die was met de enkele optie bezig... Losse queries per toepassing hebben ook het voordeel dat je de aparte queries ook nog kunt gebruiken, mocht je daar wat mee kunnn natuurlijk.

 

[Tardis]

De subquery werkt prima, zoals je hebt gezien, maar is niet overdreven snel in het gebruik, omdat er veel werk op de achtergrond moet worden verricht. Bij grote aantallen zul je dus zien dat het enige tijd duurt voordat de lijst is opgebouwd. Daarom heb ik een andere route gevolgd, die een stuk sneller werkt.

Is dat zo?
Kun je dat hard maken?
Ik waag te betwijfelen of je dat kunt.
Je stelling lijkt eerder ingegeven door perceptie dan door feiten.

Anyway, TS heeft meerdere oplossingen gekregen.

Tardis