[Access][SQL] Gegevens uit meerdere tabellen (JOIN?)

[Mike Jarod]

Nee dit is geen huiswerkvraag

• Inleiding - niet echt relevant
Een kennis van me verhuurt vakantiehuisjes en gebruikt hiervoor een reserveringsprogramma. Helaas heeft dit programma heel weinig export functies (tbv administratie) en de maker ervan is niet van plan deze te implementeren. Voorheen opende die kennis dan de database in Access en haalde er met de hand gegevens uit. Maar dit is ontzettend tijdrovend. Daarom vroeg hij mij om te kijken of ik e.e.a. kon automatiseren. Dat heb ik dus gedaan en op dit moment heb ik met een zooi simpele queries de bewerkingstijd verkort van enkele dagen naar enkele uren. Nu komt ie net met een vraag voor een aanvulling op hetgeen wat ik gemaakt heb, maar daar kom ik dus niet uit. Volgens mij is het heel simpel, ik heb SQL en Access op school gehad (heb TI opleiding gedaan) maar omdat ik dit soort dingen niet dagelijks doe ben ik het helemaal kwijt.

• Probleem/vraag - wel relevant
Ik heb de volgende 2 tabellen (heel erg versimpeld om het probleem voor te leggen):

Tabel Buchung:
EDIT: probleem opgelost, dus plaatje verwijderd, stond op m'n eigen PC "gehost".

Tabel Posten:
EDIT: probleem opgelost, dus plaatje verwijderd, stond op m'n eigen PC "gehost".

Ja het is een Duitse applicatie heb alleen op de plaatjes e.e.a. veranderd in het NL om het duidelijker te maken. Tabel Buchung geeft dus aan een BoekingsNr (NR), begin- en einddatum en de naam. Tabel Posten geeft dus aan een BoekingsNr, en verschillende kostenposten (TypeNr) voor die boeking. 1 BoekingsNR heeft dus meerdere kostenposten (1-N).

Nou wil graag uiteindelijk een overzicht krijgen (mbv SQL) in de trant van:
EDIT: probleem opgelost, dus plaatje verwijderd, stond op m'n eigen PC "gehost".
Dat die waarden voor administratie en schoonmaak nu hetzelfde zijn is toeval.

Ik kom daar dus niet uit, ik blijf hier een beetje steken:

code:

SELECT Buchung.NR, Buchung.Naam, Buchung.DatumIn - Buchung.DatumUit AS Dagen, 
[wat zet ik hier?] AS Administratiekosten, [wat zet ik hier] AS Schoonmaakkosten 
FROM Posten RIGHT JOIN Buchung ON Posten.BoekingsNr=Buchung.Nr (dit is ook een gok)

Wie kan mij wat tips geven? Die JOIN's ben ik helemaal kwijt.

[ Voor 16% gewijzigd door Mike Jarod op 24-10-2003 22:28 ]

[Verwijderd]

Zou een subquery niet handig zijn op die plekken [wat zet ik hier] en dan is zo'n JOIN overbodig.

iets van: (SELECT SUM(Bedrag) FROM Posten WHERE BoekingsNR=Buchung.NR AND TypeNr=25000) AS Administratiekosten

volgens mij is sql wel zo slim om dan voor ieder record die subquery te runnen en dan gaat die link met Buchung.NR dus goed. Ik vraag me namelijk af of je dat overzicht uberhaupt met een JOIN zou kunnen bouwen omdat je minimaal over 2 velden porbeert te sommeren waarbij je dan ook nogeens groepeert op TypeNr en BoekingNR. Maar zoveel verstand heb ik ook weer niet van SQL.

[Crazy D]

Voor het aantal dagen kun je de datediff functie gebruiken. DateDiff("d", datumIn, datumUit) as AantalDagen. Dat is iets logischer en duidelijker dan met + en - gaan werken

Die subquery werkt inderdaad.
Een join is niet zo handig, omdat je dan alles uit posten erbij ophaalt, en je wilt niet op regel 1 de naam, aantal dagen, admin kosten zien, en op regel 2 de naam, aantal dagen, en de schoonmaakkosten. Je wilt die gegevens juist op 1 regel hebben, en dat kan niet met een join (nou ja, misschien met een 2e join erbij, maar maak het jezelf niet te moeilijk, subqueries zijn eenvoudiger). En die join kan er dan helemaal uit.

[Boss]

Hiervoor moet je een kruistabel-query maken.

Dat zijn lastige dingen, maar de enige manier om gegevens die in rijen staan in kolommen te krijgen.

Als je er niet uitkomt moet je maar ff een mailtje sturen, die dingen zijn best lastig te begrijpen.

[Witte]

precies: kruistabel-query. Misschien kan je beter een Excel-sheet hiervoor pakken en dan de gegevens uit de database halen, met 'Externe gegevens'. Excel gaat net wat makkelijker om met draaitabellen.

[Mike Jarod]

Bedankt voor de nuttige reacties!

Ik ben zojuist aan de slag gegaan met subqueries, maar dat lijkt niet te werken. Volgende voorbeeld wijkt iets af van het voorbeeld uit de topicstart, deze is "real-life". Ik wil dus de "energie" uit tabel Posten filteren (Energie = Posten.Betrag waar Posten.Konto=21000). Volgens mij heb ik dan de volgende query nodig:

code:

SELECT Buchung.Nr, Buchung.Buchart, [abreise]-[anreise] AS Dagen,
(SELECT Posten.Betrag FROM Buchung, Posten WHERE
Buchung.Nr=Posten.MVnr AND Posten.Konto=21000) AS Energie 
FROM Posten, Buchung;

Access geeft me dan de melding "Deze subquery kan maximaal één record als resultaat hebben". Ik weet zeker dat bij 1 Buchung.NR maar 1 energiewaarde hoort, dus ik krijg het idee dat ie met die subquery alle Buchung.NR's filtert. Overigens heb ik bovenstaande al aan de praat gekregen met een JOIN, maar ik probeer het dus zonder. Als ik bij die subquery SELECT (SUM(Posten.Betrag) etc) doe dan werkt het weer wel alleen krijg ik voor die waarde altijd dezelfde terug, en ik wil helemaal geen SUM.

#NB: het SQL deel gebeurt dus allemaal in de SQL view van een query in Access, ik vraag me af in hoeverre Access SQL ook "compatible" is met standaard SQL.

Tenzij iemand een "echte" fout op kan merken op bovenstaande statement, ga ik me even verdiepen in die kruistabelqueries. U hoort ervan

[Crazy D]

mikejarod schreef op 22 October 2003 @ 09:27:

code:

SELECT Buchung.Nr, Buchung.Buchart, [abreise]-[anreise] AS Dagen,
(SELECT Posten.Betrag FROM Buchung, Posten WHERE
Buchung.Nr=Posten.MVnr AND Posten.Konto=21000) AS Energie 
FROM Posten, Buchung;

Die query klopt niet...
Uitleggen is niet mijn sterkste vak, maar wat ik altijd doe als ik met subqueries aan de slag ga, is het in 1e instantie zo eenvoudig mogelijk houden:

code:

SELECT Nr, Buchart, [abreise]-[anreise] AS Dagen
FROM Buchung;

Gezien de gegevens die je uiteindelijk uit je query wilt hebben, is dit "de basis". De overige gegevens moeten uit die andere tabel komen.
Die subquery is een gewone simpele query:

code:

select Betrag from Posten where MVnr = [iets] and Konto = 21000

En dan hoef je het alleen nog even samen te voegen en ervoor te zorgen dat [iets] iets zinnigs wordt

code:

SELECT Nr, Buchart, [abreise]-[anreise] AS Dagen,
(select Betrag from Posten where MVnr = Buchung.Nr and Konto = 21000) as Dagen
FROM Buchung;

Per gevonden record in Buchung zal Access de tabel Posten query'en, met als voorwaarde dat MVnr gelijk moet zijn aan het Nr in het huidige record (en Konto uiteraard 21000).

Met kruis-tabellen heb ik geen ervaring, ik heb dit soort queries zelden nodig (gelukkig )... als je daar een werkende oplossing mee hebt, kun je die posten? Kan allicht nuttig zijn

[Mike Jarod]

Mensen, ontzettend bedankt voor de hulp! Crazy D jouw laatste post gaf de doorslag.

Ik denk nu alles op orde te hebben, de persoon die het meest verstand heeft van die applicatie + boekhouding loopt nu de gegevens na. Duimen maar.

Nog even de gebruikte query:

code:

SELECT 
Nr, Betrieb, Objekt, Buchart, Anreise, Abreise, [abreise]-[anreise] AS AantalDagen, PersAnz, 
(SELECT SUM(Betrag) FROM Posten WHERE MVNr=Buchung.Nr AND Konto=21000) AS Energie, 
(SELECT SUM(Betrag) FROM Posten WHERE MVNr=Buchung.Nr AND Konto=25000) AS Administratie, 
(SELECT SUM(Betrag) FROM Posten WHERE MVNr=Buchung.Nr AND Konto=24000) AS Schoonmaak, 
(SELECT SUM(Betrag) FROM Posten WHERE MVNr=Buchung.Nr AND Konto=30000) AS Annuleringsverzekering, 
(SELECT SUM(Betrag) FROM Posten WHERE MVNr=Buchung.Nr AND Konto=22000) AS Toeristenbelasting, 
Miete, BuKosten1, BuKosten2, Name, Nebenkosten INTO AlleGegevens
FROM Buchung
WHERE Anreise>=Forms!Hoofdmenu!BeginDatum And Anreise<=Forms!Hoofdmenu!EindDatum And Storno=0
ORDER BY Betrieb, Objekt, Anreise;

Ben blij dat ik geen kruistabelqueries heb hoeven gebruiken. Mocht het toch nog niet in orde zijn laat ik het wel weten

offtopic:
Trouwens verrot dat Access SQL view alle enters/linebreaks weghaalt zodat het overzicht helemaal weg is...

[Boss]

OK, die query zoals je hem nu hebt zal best werken, maar zal niet echt snel zijn. Een querie, met daarin maarliefst 5 subqueries...

Dat is niet het ergste, wat als er nu na de 5 soorten kosten die je nu hebt staan nog een 6e soort bijkomt? Je database is nu niet meer flexibel en kan niet meer makkelijk door de gebruiker worden aangepast. Als er een nieuwe omzet-groep bij komt hebben ze jou weer nodig.

Dus toch maar een kruistabel query?

[Mike Jarod]

OK, die query zoals je hem nu hebt zal best werken, maar zal niet echt snel zijn. Een querie, met daarin maarliefst 5 subqueries...

Daar heb je helemaal gelijk in . Die query uitvoeren duurt zo'n 15sec op mijn XP1800+. Enig idee of die kruistabelqueries ook in SQL uit te voeren zijn? Ik zal er me er nog eens in verdiepen, maar omdat het allemaal nu werkt zoals het moet, zal ik er niet te veel mee doen. Ik krijg namelijk een vast bedrag ... als het nou per uur was .

Maar je hebt gelijk, technisch gezien is dit niet echt "mooi".

[Boss]

Hoe bedoel je ' of die kruistabelqueries ook in SQL uit te voeren zijn'? Moet je het geheel koppelen aan een SQL server, of wil je gewoon de SQL statements invoeren?

Die kruistabel queries zijn lastig, maar als je het truucje doorhebt, is het eigenlijk niet zo moeilijk. Ik weet niet hoeveel ervaring je hebt, maar met eeen uurtje ' spelen' en dan nog een uurtje er echt over nadenken moet je er wel uit komen...

[Mike Jarod]

Boss schreef op 26 October 2003 @ 12:48:Hoe bedoel je ' of die kruistabelqueries ook in SQL uit te voeren zijn'? Moet je het geheel koppelen aan een SQL server, of wil je gewoon de SQL statements invoeren?

Nee geen koppeling, gewoon simpel in Access. lokaal zegmaar. SQL heeft gewoon mijn voorkeur. Ik dacht dat die kruistabelqueries "Access only" waren, maar misschien is zoiets ook te realiseren met puur SQL. Ik zal me er binnenkort nog eens in verdiepen.