[Algemeen] query/dataset ordenen

[Remco]

Ik zit met een probleem waar ik maar niet uit kom.
Ik heb een mdb database waar registraties staan die registreert wie wanneer erin of eruit gaat.
Hieronder een klein stukje van de tabel met de velden die ik nodig heb (de rest heb ik even weggelaten)

Datum	Tijd	In_of_Uit
16-06-2004	12:13:45	In
16-06-2004	13:00:45	Uit
17-06-2004	09:13:45	In
18-06-2004	08:13:45	In
18-06-2004	08:33:45	In

Nu doe ik de volgende query op de database:

code:

select datum, in_of_uit, count (datum) as aantal from Registratie where (datum
 >=#" & begin_datum & "# and datum <=#" & eind_datum & "#) group by datum,
 in_of_uit

Dan krijg ik het volgende resultaat:

Datum	In of uitgaand	Aantal registraties
16-06-2004	uit	1
16-06-2004	in	1
17-06-2004	In	1
18-06-2004	In	2

Tot zo ver gaat alles wel goed. Echter moet ik op basis van bovenstaande tabel een rapportage maken.
Hoe ik het ook wend(t?) of keer ik heb niks aan deze opmaak.
Graag wil ik het volgende hebben:

Datum	Uitgaand	Ingaand
16-06-2004	1	1
17-06-2004	0	1
18-06-2004	0	2

En nu weet ik even niet hoe ik dit moet oplossen.
Kan ik mijn query uit de mdb zo aanpassen dat ik deze waardes krijg ?
Ik kan natuurlijk ook een datatabel erbij maken (runtime) en dan de andere tabel doorlopen met allerlei checks (wat als er een datum niet is ?, of wat als er 0 staat ?), maar dat zie ik ook niet zo zitten.

De query word gemaakt met variabele data (meervoud datum) en het resultaat moet naar een rapportage gaan.

Heeft 1 van jullie nog een briljant idee hoe dit aan te pakken ?

[nenne]

[quote]Remc0 schreef op 25 juni 2004 @ 21:40:

Nu doe ik de volgende query op de database:

code:

select datum, in_of_uit, count (datum) as aantal from Registratie where (datum
 >=#" & begin_datum & "# and datum <=#" & eind_datum & "#) group by datum,
 in_of_uit

Probeer de volgende code eens:

code:

select datum, in_of_uit, count (datum) as aantal from Registratie where (datum
 >=#" & begin_datum & "# and datum <=#" & eind_datum & "#) and in_of_uit = "in" group by datum, in_of_uit

Op deze manier krijg je volgens mij 1 kolom gevuld met alle tijden dat er iemand naar binnen is gegaan. Combineer dat met een zelfde stukje voor alle mensen die naar buiten zijn gegaan, en je bent weer op weg ...

Edit: Ook ff alle ingaande mensen bij elkaar optellen etc. etc.

[ Voor 12% gewijzigd door nenne op 25-06-2004 21:55 ]

[Remco]

@nenne:
Ja, dat klopt.
Maar dan heb ik 2 datasets.
Dan moet ik ze nog doorlopen om er 1 dataset/tabel van te maken met alle obstakels van dien.
Dit is nodig omdat de grafiek die ik wil maken maar aan 1 datasource gebind kan worden.

[whoami]

Kan je niet iets met subqueries of self-joins bereiken?

code:

select registratie.datum, sum(ingaand.aantal), sum(uitgaand.aantal)
from registratie, registratie ingaand, registratie uitgaand
LEFT JOIN registratie ingaand ON registratie.datum = ingaand.datum
LEFT JOIN registratie uitgaand ON registratie.datum = uitgaand.datum
WHERE ingaand.in_uit = 'in'
AND uitgaand.in_uit = 'uit'
group by registratie.datum

Je zult hier wel outer joins moeten gebruiken zoals je ziet in m'n code.
Misschien kan je het ook wel oplossen dmv (correlated) subqueries:

code:

select datum, ( select sum( aantal) from registraties r1 where r1.datum = registraties.datum  and r1.in_uit = 'in'),
       ( select sum(aantal) from registraties r2 where r2.datum = registraties.datum AND r2.in_uit = 'uit')
from registraties

[Olaf van der Spek]

Eerst moet je de In_of_Uit kolom splitsen in twee kolommen, een In en een Uit.
Dit kan in de query met iets als
if (In_of_Uit = 'In') 1 else 0 In
if (In_of_Uit = 'Uit') 1 else 0 Uit
Dan kunt je een group by datum doen met sum(In) en sum(Uit).

[Remco]

Hartstikke bedankt allemaal.
Jullie hadden me goed op weg geholpen.
Hieronder staat de juiste query, moet alleen de data er nog in verwerken.

code:

SELECT [selectie vanaf in].aantalin, [selectie vanaf uit].aantaluit,
 Registratie.Datum
FROM [selectie vanaf uit] RIGHT JOIN ([selectie vanaf in] RIGHT JOIN 
Registratie ON [selectie vanaf in].datum = Registratie.Datum) ON 
[selectie vanaf uit].datum = Registratie.Datum
GROUP BY [selectie vanaf in].aantalin, [selectie vanaf uit].aantaluit,
 Registratie.Datum
HAVING (((Registratie.Datum)>=#6/16/2004#));

Ik heb dus 2 queries aangemaakt, 1 voor in en 1 voor uit.
Hierbij relaties gemaakt naar de "grote" query en het was klaar.

tnx