Regels uit tabel selecteren bij verhuizing naar nieuwe gemeente

Hallo allemaal,

Ik heb eerder met SPSS en SAS gewerkt, en daar wist ik wel het een en ander te vinden, maar sinds kort gebruik ik Access en de gedachtegang daarin wil nog niet zo. Ik wil een bepaalde tabel creeeren in Access, die dan dynamisch doorgestuurd wordt naar een Excel bestand (dat laatste lukt)r.

Het gaat om een bronbestand wat er qua regels zo uitziet (voorbeeld)
Naam, BeginGemeente, Verhuisdatum, Gemeente:
Henk, Amsterdam, 1-1-2018, Rotterdam
Henk, Amsterdam, 1-2-2018, Tiel
Henk, Amsterdam, 1-3-2018, Tiel
Henk, Amsterdam, 1-4-2018, Amsterdam
Kees, Rotterdam, 1-1-2018, Den Haag
Kees, Rotterdam, 1-2-2018, Den Haag
Kees, Rotterdam, 1-4-2018, Rotterdam
Truus, Den Haag, 1-4-2018, Den Haag
Truus, Den Haag, 1-6-2018, Utrecht
Truus, Den Haag, 1-7-2018, Utrecht

Nu wil ik dat alleen de regels overblijven waarbij de persoon verhuist is naar een nieuwe gemeente. Verhuizingen binnen gemeente die regels moeten eruit (regel 3, 6, 8,10 dus). En de tabel die ontstaan is gaat dan naar Excel. Meestal stapel ik dan selectiequery's, want query's met een ! handmatig uitvoeren kunnen niet automatisch naar Excel verwerkt worden?
Het lukt mij niet de tabel zo om te bouwen dat hij de nieuwe gemeente vergelijkt met de vorige gemeente. Iemand een idee hoe ik dat in elkaar krijg? Welke stappen moet ik daarvoor nemen? Wel graag een simpele gedachtegang als dat kan. VBA en Macro's etc heb ik nog nooit gebruikt.

Alvast dank voor het meedenken!

Wat wil je precies? Ik snap je bedoeling niet helemaal. Of je getallen kloppen niet, dat kan ook. Ik zie slechts 3 records waarin personen binnen dezelfde gemeente verhuizen: 4, 7 en 8. Het gaat jou dus niet om de laatste verhuizing, maar om elke verhuizing waarbij de [Begingemeente] niet gelijk is aan de [EindGemeente].

Daarnaast heb je het steeds over tabellen en dat lijkt mij ook niet juist: wat jij wilt hebben kun je makkelijk met een query maken en die query is dan ook nog eens heel makkelijk te koppelen in Excel. En dat gaat dus heel simpel; je moet bij één van de gemeentevelden een criterium zetten dat de gemeente ongelijk maakt aan het andere veld. Dus bij [Begingemeente] zet je: <> [EindGemeente]. Of, dat mag ook, bij[EindGemeente] zet je: <>[Begingemeente].
In de SQL staat dan iets als:
En dat zou al moeten werken.

Bedankt voor je reactie Octafish! Ik gooi de terminologie door elkaar inderdaad, ik bedoel inderdaad een (selectie)query. Of als het gewenste niet met 1 query lukt (denk ik?) dan meerdere query's voor alle tussenstappen. Het laatste resultaat wil ik dan naar Excel sturen, en van daar wil ik bijvoorbeeld lijstjes kunnen maken met bijv alle mensen die in de maand mei verhuist zijn naar een andere gemeente.

In het fictieve voorbeeld zijn Henk, Kees en Truus dezelfde mensen die telkens blijven verhuizen. Dus Henk woonde eerst in Amsterdam, ging per 1-1-2018 naar Rotterdam, per 1-2-2018 weer naar Tiel, per 1-3-2018 verhuisde hij binnen Tiel en per 1-4-2018 verhuisd hij weer naar Amsterdam. De derde regel dat hij binnen Tiel ging verhuizen wil ik graag eruit hebben. Ik begrijp de oplossing die je voorstelt, maar ik bedoel zegmaar dat bij personen met meerdere verhuizingen de verhuisgemeente niet altijd vergeleken wordt met de begingemeente, maar telkens met de gemeente van de vorige verhuizing. En ik weet even niet hoe ik dat handig kan aanpakken? Is de eerste stap om de data te kantelen dat ik per persoon 1 rij krijg met daar achter alle verhuisgemeentes + verhuisdata?

Heb je mijn oplossing al toegepast? Want wat jij wilt kan volgens mij dus makkelijk in één query, en wel met een criterium zoals voorgesteld. Dat kan dan zowel op de begingemeente als op de eindgemeente. Niet op allebei natuurlijk, want dan schakelt het ene filter het ander uit.

Als je jouw oplossing toepast dan verdwijnen record 4,7 en 8 toch?
Het moet 3,6,8 en 10 worden. De query moet dus de verhuisgemeente niet vergelijken met de begingemeente in dezelfde record, maar hij moet het vergelijken met de verhuisgemeente van een regel er boven (in geval van dezelfde persoon).

Hoe leg jij uit dat dezelfde persoon op 1 februari van Amsterdam naar Tiel verhuist, en op 1 maart óók van Amsterdam naar Tiel verhuist? Lijkt mij fysiek volslagen onmogelijk! Tenzij je twee huizen hebt, en ze allebei verkoopt om een of twee nieuwe huizen in een andere stad te betrekken. Bovendien is wat je nu vraagt, niet in wat je in je originele vraag hebt neergezet.

azimut zei:

Verhuizingen binnen gemeente die regels moeten eruit!

Klik om te vergroten...

En dat lees ik toch echt als: Van Tiel Naar Tiel, of van Amsterdam naar Amsterdam.

Mijn echte dataset is een stuk complexer. Dus dit waren even theoretische voorbeelden. Ik heb serieus personen die een stuk of 8 keer verhuizen, wel binnen een wat langere periode
Maar ik bedoel nog steeds hetzelfde. Dus voorbeeld-Henk is wel verhuist van Amsterdam naar Amsterdam, maar tussendoor heeft hij in Rotterdam en Tiel gewoond. Dus ik wil het telkens vergelijken met de vorige gemeente waar de persoon heeft gewoond. En dan moet de derde record er dus uit, omdat voorbeeld-Henk verhuist van Tiel naar Tiel, en de vierde record mag erin blijven omdat hij van Tiel naar Amsterdam verhuist (de regel erboven). Snap je wat ik bedoel of is het onduidelijk?

En dan kom ik toch echt weer terug bij mijn eerdere antwoord. Je haalt de gegevens ook op een nogal omslachtige op vind ik. Een verhuizing doe je per definitie altijd van het laatste woonadres naar het volgende. Je hoeft dus bij een verhuizing niet de oude woonplaats in te vullen, want die héb je al een keer ingevuld. Dus het is overbodig om zowel BeginGemeente in te vullen als EindGemeente. Omdat de logica ook vereist dat je nooit een ingangsdatum van de nieuwe woning kan hebben die vóór die van de vorige woning ligt, is het simpel om de laatste woonplaats in een query naast die van de nieuwe te leggen. En daar op te filteren als die gelijk zijn. Of niet.

Maar zelfs met jouw voorbeeld, waar het filteren dus simpeler is, kun je deze filtering maken.
Maak anders een voorbeeldje met wat dummy data, want ik heb toch het gevoel dat we wat langs elkaar heen denken.

Lees ook je eigen voorbeeld nog eens door, want in mijn browser gaat Henk in het derde record echt van Amsterdam naar Tiel, net als dus in het tweede, en in het vierde van Amsterdam naar Amsterdam.

Ik heb jouw tabel even geïmporteerd in een database, en daar een query op losgelaten die volgens mij dus doet wat jij wilt: verhuizingen binnen dezelfde stad overslaan. En, zoals ik al verwachtte, vallen dan de records 4, 7 en 8 af en niet 3, 6, 8 en 10.
En dat komt dus door de onmogelijke verhuisbewegingen die je in de tabel hebt staan.

De indeling is inderdaad niet helemaal logisch, zo ziet mijn bronbestand er alleen uit wat ik als startpunt heb. De verwarring tussen ons ligt denk ik in de tweede kolom begingemeente. Dit is niet de begingemeente van elke verhuizing. Het is alleen de állereerste begingemeente van de allereerste verhuizing waar die persoon is begonnen. Dus bij de eerste verhuizing ging Henk verhuizen van Amsterdam naar Rotterdam (record 1, kolom 2 vs. record 1, kolom 4). En bij de tweede verhuizing ging hij van Rotterdam naar Tiel (record 1, kolom 4 vs. record 2, kolom 4)

Ik zou de tweede kolom er wel uit kunnen halen, dat scheelt een stuk verwarring. Maar dan heb ik wel extra records nodig, dan moet ik bij Henk bijvoorbeeld een record helemaal bovenaan invoegen waar staat "Henk, 1-1-2000 (een datum heel lang geleden), Amsterdam"
Ik denk dat dat nog wel gaat lukken, dan zou na deze eerste stap de data er zo uit zien:
Naam, verhuisdatum, bestemmingsgemeente
Henk, 1-1-2000, Amsterdam
Henk, 1-1-2018, Rotterdam
Henk, 1-2-2018, Tiel
Henk, 1-3-2018, Tiel
Henk, 1-4-2018, Amsterdam
Kees, 1-1-2000, Rotterdam
Kees, 1-1-2018, Den Haag
Kees, 1-2-2018, Den Haag
Kees, 1-4-2018, Rotterdam
Truus, 1-1-2000, Den Haag
Truus, 1-4-2018, Den Haag
Truus, 1-6-2018, Utrecht
Truus, 1-7-2018, Utrecht

Heb je zo een idee hoe ik als volgende stap Access zo ver krijg dat hij de rode records verwijderd, namelijk de verhuizingen binnen dezelfde gemeente?
Hij moet (als ze op chronologische volgorde staan) dus de bestemmingsgmeente vergelijken met de gemeente in de record er boven.

Ik heb nóg een voorbeeldje gemaakt, volgens de techniek die ik in #8 heb aangegeven, met één gemeente, waarbij je dus kijkt naar de verhuisdatum en naar wat de vorige gemeente was. Met een criterium gooi je dan de records met dezelfde plaatsnaam er weer uit. Doe je het op die manier, dan krijg je een resultaat dat lijkt op wat je wilt, dus zonder records 3,6 en 10. Record 8 zit er dan nog steeds bij, omdat dat nu het eerste record in de set is, en dat eerste record altijd wordt getoond.
De query kun je niet meer vergelijken met de vorige, want hij is een tikkie langer .

Hier vind je een overzicht van de specs die Access hanteert. Ik ben nog nooit in de buurt van de limieten gekomen, en als jij dat wel doet, kan er wellicht nog wel wat verbeterd worden aan het ontwerp. Of upgraden naar SQL server o.i.d.

azimut zei:

Het stukje code van 'top 1' kende ik nog niet en ga ik even uitpluizen want dat kan ik vast vaker gebruiken.

Klik om te vergroten...

Een 'gewone' query laat alle records zien. Dat zie je ook in de werkbalk bij <Query's instellen>; daar staat dan bij <Resultaat>: Alles. Klik je op de keuzelijst, dan staan daar wat default keuzes: 5, 25, 5% en 25% bijvoorbeeld. Daarmee beperk je de resultaatlijst op basis van de keuze. Met de waarde 1 krijg je maar één record terug, bij 5 5 records. en bij 5% de eerste 5% van de records. Dat percentage moet je niet te letterlijk nemen, want dat is natuurlijk altijd afgerond naar een geheel getal. Bij 7 records kun je nooit 5% laten zien. Dat resultaat wordt dan ook nog eens beïnvloed door de sorteervolgorde. Dus Aflopend op Datum levert een ander record (bij Top 1) op dan oplopend op ID, of oplopend op Naam.

azimut zei:

Ik zie ook tmp staan, gebruikt hij dan als tussenstap een Tijdelijk bestand?

Klik om te vergroten...

In dit voorbeeld wordt niet zozeer een tijdelijk bestand aangemaakt, alswel een tweede kopie van de tabel geopend. Zou in jou geval de zaak alleen maar erger maken . De kopie is nodig om het laatste record op te zoeken vóór het actuele record. Dat doe je met Where tmp.VerhuisDatum < Verhuizingen.VerhuisDatum. Als je 8 records hebt (er zit ook nog een criterium op naam, maar dat laat ik nu even buiten beschouwing), dan voldoen er nu dus 7 records aan het criterium. En bij het 12e record voldoen er 11 records. Omdat de Verhuisdatum uit de kopietabel dus kleiner is dan de datum uit de hoofdtabel. Maar hiermee zou je een probleem hebben, omdat je in een veld maar één waarde kan hebben, en je wilt natuurlijk ook maar één waarde zien: de voorlaatste gemeente. En daar komt de Top 1 dus om de hoek kijken: hiermee beperk je de recordset altijd tot één record, dus ook tot één waarde.

Hoop dat het zo wat duidelijker is?

Wat je andere vragen betreft: de Handleidingen sectie heeft niet een eigen knopje op de hoofdpagina, maar je ziet rechtsboven altijd wel 3 handleidingen als 'teaser' vermeld staan. Als je daar op één van de drie klikt, kom je in de handleingen sectie. Dan kun je in de keuzelijst rechtst filteren op 'Office Suite' en dan staan de Access hoofdstukken bovenaan. (vanwege de A).

Kennis van SQL is altijd handig, zeker als het gaat om uitgebreidere queries. VBA zou ik zeker oppakken, omdat je pas echt kunt automatiseren als je kunt programmeren. En steek vooral geen tijd in macro's, die tijd kun je beter besteden aan VBA.
Ik krijg de indruk dat die database nogal onhandig in elkaar zit; als je een goede database maakt, hoeven gebruikers echt niet naar Excel te 'vluchten'. Ik zou zelfs zeggen: probeer dat zoveel mogelijk te voorkomen; je kunt in Access de zaken zóveel beter beschermen en afschermen! Nog afgezien van de gebruiksvriendelijkere GUI die Access heeft.... Iemand die beweert dat je de databasegegevens beter in Excel dan in Access kunt beheren, werkt met een waardeloze database. Dat is hetzelfde als een koerier die beweert dat hij een koelkast beter op een gammele omaiets kan vervoeren dan in een glimmend nieuw busje. Is mijn stelling .

Hallo Octafish,
Een maandje later inmiddels. Bedankt voor de uitleg van de codering Top en Tmp!
Ik ben hiermee aan de slag gegaan, dit keer met de echte data. Na een paar uur prutsen met een foutmelding kom ik er toch niet uit.

Ik heb de door jou gegeven code gebruikt. Mijn bestand heet i.p.v. verhuizingen heet het Verhuizingen1. Ik heb als SQL input:
SELECT Verhuizingen1.VerhuisID, Verhuizingen1.Naam, Verhuizingen1.Verhuisdatum, Verhuizingen1.Gemeente, (select top 1 Gemeente From Verhuizingen1 As tmp Where tmp.VerhuisDatum < Verhuizingen1.VerhuisDatum
And tmp.Naam = Verhuizingen1.Naam Order by tmp.VerhuisDatum Desc) AS VorigeGemeente
FROM Verhuizingen1
GROUP BY Verhuizingen1.VerhuisID, Verhuizingen1.Naam, Verhuizingen1.Verhuisdatum, Verhuizingen1.Gemeente
HAVING ((((select top 1 Gemeente From Verhuizingen1 As tmp Where tmp.VerhuisDatum < Verhuizingen1.VerhuisDatum
And tmp.Naam = Verhuizingen1.Naam Order by tmp.VerhuisDatum Desc))<>[gemeente]
Or ((select top 1 Gemeente From Verhuizingen1 As tmp Where tmp.VerhuisDatum < Verhuizingen1.VerhuisDatum
And tmp.Naam = Verhuizingen1.Naam Order by tmp.VerhuisDatum Desc)) Is Null));

Dan krijg ik de foutmelding:
"De query bevat geen opgegeven expressie ' Not [gemeente]= Or is Null' als onderdeel van een statistische functie"

Als ik in de ontwerpweergave de criteria "<>[gemeente]" en de OR: "is Null" weghaal om het wat simpeler te maken (ik was aan het zoeken waar het fout kon gaan), dan krijg ik in eerste instantie wel een tabel te zien, maar al snel daarna de melding "Deze subquery kan maximaal één record als resultaat hebben"
Vervolgens wordt de tabel geleegd en elke cel vervangen naar "#naam?"

In de SQL code zie ik 1 keer staan "VorigeGemeente"
Waar wordt dit voor gebruikt? Want het staat niet als kolom in mijn verhuizingen1 bestand, en ik zie het ook maar 1 keer staan in de code.
In verhuizingen 1 heb ik als kolommen: VerhuisID, Naam, Verhuisdatum, Gemeente

Ik zie zelf geen verschil in mijn SQL code en die van jou, maar misschien kijk ik verkeerd.
Heb je misschien een idee wat ik verkeerd doe, of waar het fout gaat?

Ik ben er gister weer een hele dag mee bezig geweest, maar ik blijf tegen die 2 foutmeldingen aanlopen. Ik heb bijna mijn computer het raam uit gegooid. Zou je misschien willen reageren of je weet wat die 2 foutmeldingen betekenen? Je reactie wordt heel erg gewaardeerd!!

De kolom "vorigegemeente" snap ik nu. Als hij leeg is, is er geen vorige gemeente. Als er een andere gemeente staat, is het een verhuizing naar een andere gemeente. En als er dezelfde gemeente staat, is het een verhuizing binnen een gemeente.

SELECT Verhuizingen1.VerhuisID, Verhuizingen1.Naam, Verhuizingen1.Verhuisdatum, Verhuizingen1.Gemeente, (select top 1 Gemeente From Verhuizingen1 As tmp Where tmp.VerhuisDatum < Verhuizingen1.VerhuisDatum
And tmp.Naam = Verhuizingen1.Naam Order by tmp.VerhuisDatum Desc) AS VorigeGemeente
FROM Verhuizingen1
GROUP BY Verhuizingen1.VerhuisID, Verhuizingen1.Naam, Verhuizingen1.Verhuisdatum, Verhuizingen1.Gemeente
HAVING ((((select top 1 Gemeente From Verhuizingen1 As tmp Where tmp.VerhuisDatum < Verhuizingen1.VerhuisDatum
And tmp.Naam = Verhuizingen1.Naam Order by tmp.VerhuisDatum Desc))<>[gemeente]
Or ((select top 1 Gemeente From Verhuizingen1 As tmp Where tmp.VerhuisDatum < Verhuizingen1.VerhuisDatum
And tmp.Naam = Verhuizingen1.Naam Order by tmp.VerhuisDatum Desc)) Is Null));

Kun je misschien die 2 vetgedrukte stukjes code uitleggen? <>[gemeente] snap ik (dan is het een verhuizing binnen gemeente), maar waarom is het "is Null" en niet "is not null"

Al geeft dat nog steeds dezelfde foutmeldingen. Ik probeer en verander vanalles, maar ik blijf 1 van de 2 foutmeldingen:
"De query bevat geen opgegeven expressie ' Not [gemeente]= Or is Null' als onderdeel van een statistische functie"
"Deze subquery kan maximaal één record als resultaat hebben"

Zou je misschien een reactie hierop hebben? Ik zou er echt heeeeeel erg blij van worden!

Post eens een db met wat voorbeeld gegevens, dan kan ik kijken wat er bij jou niet goed gaat. Ik heb geen db (meer) met daarin de tabel, dus ik kan even niet testen.

Dank voor je reactie!
Het vreemde is dat ik thuis een klein oefen databestandje heb gemaakt (met de gegevens van hierboven) en met die stuk of 12 regels gaat het goed zonder foutmeldingen.
Als ik echter de complexere data van vrijwilligerswerk gebruik zijn het ongeveer 1700 verhuizingen en gaat het wel ergens verkeerd. Ligt misschien niet aan de aantallen, maar ik dacht misschien zitten er per ongeluk mensen in die op 1 dag twee keer verhuist zouden zijn en loopt hij daar op vast. Mijn Access bestand loopt sowieso ook nogal tegen zijn limiet aan (stuk of 20 query's vanwege andere handelingen die eerst moeten), en regelmatig duurt het 10 minuten voor hij een query opent of Access loopt vast met 'reageert niet'.

Vandaag weer de hele dag geprobeerd om die verhuizingen in 1 tabel te krijgen, en omdat ik met deze methode niet verder kwam weer terug gegaan naar mijn oude methode. Ik heb geprobeerd mijn query's zo efficient mogelijk op te bouwen (waar ik waarschijnlijk niet heel goed in ben), maar ik loop na stuk of 12 query's (wel met veel joins) weer tegen de foutmelding aan "kan geen tabellen meer openen". Het hele bestand is nu af en werkend, alleen één query moet er nog bij voor hij echt af is. En precies die laatste query wil hij echt niet meer bijvoegen (kan geen tabellen meer openen) Ik ga maandag maar weer verder prutsen. Ik vond Access eerst heel leuk, maar nu vooral frustrerend :evil:

Ik zal het je laten weten als het gelukt is, dan spring ik een gat de lucht in, ik kijk uit naar het moment :d

Het lijkt er toch op alsof je db bepaald niet goed is opgezet, en dat er dus behoorlijke verbeteringen zijn te maken. Ik heb heel wat hele grote databases gemaakt, maar jouw problemen kom ik dus nooit tegen. Ik raad je echt aan om er een specialist bij te halen, als je de db niet hier wilt laten zien. Wat ik mij ook wel kan voorstellen, als er privacygevoelige gegevens in zitten. Maar zoals jij nu met die database werkt, dat hoeft dus echt niet. Het is aan jou om te bepalen of je door wilt modderen op deze manier (lijkt dan toch op een vrijwillige keuze) of bereid bent om je db wél goed en snel te laten functioneren. Ik zeg het misschien een beetje hard, maar dat is denk ik wel zoals het nu is.