query maken van een deel van een veld

[JEPEDEWE]

Ik hoop dat ik dit uitgelegd krijg...
Ik heb een tabel met 2 velden, een datum en een referentie veld
Het referentieveld is een soort van unieke factuurnummer die bestaat ui 7 karakters
de factuurnummers zijn gegroepeerd in "pakjes" van 100
bvb
5623800 tot 5623899
dan
5623900 tot 5623999
enz
Ik wil een query hebben waarin alleen de eerste 5 karakters gegroepeerd worden en dit op aflopende datumvolgorde.
De eerste 5 karakters worden dus NIET op of aflopend gesorteerd
Ik kom er helemaal niet uit, dus graag wat hulp
Ik stuur een bestandje mee met de tabel zoals ik die heb

Bedankt
JP

Bekijk bijlage Database7.rar

 

[OctaFish]

Dat is toch niet zo'n moeilijke query:

SELECT Left([REFERENTIE],5) AS FactuurGroep, DATUM
FROM referenties
GROUP BY Left([REFERENTIE],5), DATUM
ORDER BY Left([REFERENTIE],5), DATUM DESC;

Je haalt dus de 1e 5 tekens uit [Referentie] en groepeert de query op dat veld en aflopend op datum.
Overigens zitten er in je voorbeeld waarden als: 06019,0612/ en EXTRA. Lijkt mij niet helemaal correct

 

[JEPEDEWE]

Oeps, detail vergeten, alleen de referenties die 7 karakters lang zijn mogen in de lijst gebruikt worden dus schreef ik:

SELECT Left([REFERENTIE],5) AS FactuurGroep, referenties.DATUM
FROM referenties
GROUP BY Left([REFERENTIE],5), referenties.DATUM, Len([REFERENTIE])
HAVING (((Len([REFERENTIE]))=7))
ORDER BY Left([REFERENTIE],5), referenties.DATUM DESC;

wat helemaal niet de correcte lijst oplevert...
dus.. niet goed

JP

 

[OctaFish]

Waarom gebruik je HAVING? In dit soort gevallen moet je WHERE gebruiken.

SELECT Left([REFERENTIE],5) AS FactuurGroep, referenties.DATUM
FROM referenties
WHERE (((Len([REFERENTIE]))=7))
GROUP BY Left([REFERENTIE],5), referenties.DATUM
ORDER BY Left([REFERENTIE],5), referenties.DATUM DESC;

[

 

[JEPEDEWE]

Sorry michel maar als ik jouw code uitvoer dan staan de codes niet op datum gerangschikt... referenties van 2014 staan tussen die van 2012
en ik heb bvb ook meerdere keren dezelfde referentie: bvb 10 maal 16520 terwijl ddat eigenlijk elke "sub-referentie" maar 1 meer mag voorkomen.
JP

 

[OctaFish]

Ik snap 'm, niet; als ik de query in jouw db draai groepeert hij netjes op de factuurgroep, en aflopend op datum. En in jouw vraagstelling heb je het niet over filteren op jaartal. Dus dat doet de query ook niet. Lijkt mij een kwestie van een extra veld er bij zetten:

WHERE (((Len([REFERENTIE]))=7) AND ((Year([DATUM]))=Year(Date())))

Dat je referentienummers terugkomen is logisch, want je groepeert zowel op ref nr als op datum. Zodra een datum meerdere keren voorkomt, krijg je meerdere ref nrs.
Kortom: begin nog eens overnieuw, en stel je vraag nu met alle ingrediënten die nodig zijn voor een goed antwoord .

 

[JEPEDEWE]

Michel...

Ik heb wat moeite om het deftig uitgelegd te krijgen, maar zoals het nu is wordt de volgorde van de query gebaseerd op "referentie"
Laat ik de query lost op de data zoals ik ze nu heb (data verspreid over 10 jaar) dan staan factuurnummers van 2014 tussen die van 2012.
de sortering zou dus op de datum moeten gebeuren maar zodanig dat er slechts 1 maal dezelfde eerste 5 cijfers voorkomen.
Ik stuur een schermafdruk mee
Hopelijk komen we er uit
JP

 

[OctaFish]

Sorteren in Access (en elk ander programma natuurlijk) gebeurt op basis van de volgorde van de kolommen. Nu staat het nummer voorop, wat betekent dat er wordt gegroepeerd op nummer. Zet je de datum voorop, dan groepeert hij op datum. De combinaties zijn uniek, dus daar zit je probleem niet. Kwestie van kolommen omdraaien dus.

 

[JEPEDEWE]

Ik schrij dus:

SELECT Left([REFERENTIE],5) AS FactuurGroep, betalingen.DATUM
FROM betalingen
WHERE (((Len([REFERENTIE]))=7))
GROUP BY Left([REFERENTIE],5), betalingen.DATUM
ORDER BY betalingen.DATUM DESC, LEFT([REFERENTIE],5);

maar dan krijg ik verschillende keer dezelfde code...
Zie voorbeeld jpg-tje

JP

 

[OctaFish]

Ik snap nog steeds niet wat je wilt, en ga dus nog steeds uit van deze vraag:

Ik wil een query hebben waarin alleen de eerste 5 karakters gegroepeerd worden en dit op aflopende datumvolgorde.

Je wilt groeperen op de eerste 5 karakters en dat hoeft niet gesorteerd te zijn, maar de datum moet aflopend. En dat is nog steeds het geval. Maar ik vermoed dat je het allemaal niet goed uitlegt, en wat anders wilt. Wat, da's de vraag!

Je kunt op basis van deze gegevens twee kanten op:
1. Je wilt elke datum één keer zien, en daarbij dus één referentienummer.
2. Je wilt elk referentienummer één keer zien, en daarbij dus één keer de datum.
Elke andere variant heb je al. (is er ook maar één met verschillende sorteringen)

Beide opties lijken mij niet logisch, maar meer kan ik er nu ook niet van maken Maak eens een voorbeeldje (Excel of zo) waarin je laat zien wat je nu eigenlijk bedoelt.

 

[JEPEDEWE]

Het is moeilijk uit te leggen, dit is mijn query nu:

SELECT Left([REFERENTIE],5) AS Expr1
FROM Betalingen
WHERE (((Len([REFERENTIE]))=7) AND ((Right([REFERENTIE],2))=1))
ORDER BY Betalingen.DATUM DESC;

Dit geeft een lijst van codes in datumvolgorde, maar als ik een code heb die eindigt op een 0 dan komt deze niet in de lijst voor, en laat nu net DAT het probleem zijn

het formulier zoals het nu is toont een lijst met de eerste 5 karakters van de factuurnummer
Klik ik hier op dan krijg ik in de lijst ernaast alle factuurnummers die starten met die eerste 5 geselecteerde... dit werkt perfect maar dus niet voor een code die eindigt op een 0

Vandaar mijn probleem

zie jpg-ke

JP

 

[OctaFish]

Als je filtert op de waarde 1 moet je niet gek opkijken als de waarde 0 niet in de lijst voorkomt. Ik snap nog steeds niet wat je wilt, maar volgens mij heb je nu een heel andere vraag... Overigens zie ik in je lijst een waarde die op 0 eindigt, Maar dat bedoel je vast niet.

 

[JEPEDEWE]

Zoals ik al zei... de manier zoals het nu opgebouwd is sluit inderdaad factuurnummers uit die eindigen op een 0, en zo nu en dan heb ik die... en die staan NIET in mijn lijst aan de linker kant, dus... fout
Ik probeer je uit te leggen hoe het zit:

Een normaal factuurnummer bevat geen 5 maar 7 karakters
(die zie je in de rechter lijst)
Voor 100 facturen zijn de eerste karakters dezelfde
Ik krijg mijn facturen van de Belgische overheid opgestuurd per 2000 exemplaren. Eens een doos leeg is krijg ik een nieuwe doos, maar de nummering volgt nooit netjes op die uit de eerste doos
Het formulier (waarvan de schermafdruk bij de vorige mail zat) is nu zo opgebouwd dat ik in de linker kolom een "startnummer" van 5 karakters kies en de 100 facturen die deze 5 eerste startnummers hebben, in de rechter kolom verschijnen.
Dat werkt perfect,
alleen
Het laatste formulier van een doos eindigt steeds op een 0 en hierna komen er geen nummers meer, dus die "startnummer" staat er nooit tussen
Dus als ik bvb
5623900-5623901-5623902....
heb dan staat 56239 in de lijst, ik klik er op en de beschikbare factuurnummers, beginnende met 56239 komen in de rechter lijst
heb ik alleen
5623900 (als laatste van de doos)
dan staat 56239 NIET in mijn lijst omdat er geen 5623901 meer komt
dus die lijst is niet correct zoals ik hem opstel...
heb je hem?

Merci in elk geval hoor!!!

JP

 

[OctaFish]

Ik snap er eerlijk gezegd nog steeds niet zo veel van.... Als ik deze query draai:

SELECT Left([REFERENTIE],5) AS FactuurGroep, ID, REFERENTIE, DATUM
FROM referenties
WHERE (((Len([REFERENTIE]))=7) AND ((Left([REFERENTIE],5))="56239"))
ORDER BY Left([REFERENTIE],5), REFERENTIE;

dan zit alles er netjes bij, dus ook 5623900. O basis van deze query kun je dus een subformulier filteren, want je gebruikt de Factuurgroep als filter die je uit de keuzelijst links haalt. Ik zie het probleem dus niet.

 

[JEPEDEWE]

Michel.....
ik snap niet dat jij het niet snapt....
Zoals jij het schrijft werkt het natuurlijk wel
De query die ik maakte werkt ook, maar niet voor alle factuurnummers
Het systeem zit misschien wel wat raar in mekaar, maar lijkt m.i. ingewikkelder dan het is.
Alle, nog een poging..
Ik de tabel zoals die hier nu bij mij staat, heb ik 25000 records
elke record heeft een "referentie"nummer
dit nummer refereert naar het nummer op de factuur.
Dit zij officele documenten die we bij de overheid aanschaffen en die in dozen van 1000 aangekocht moeten worden
Elke factuur heeft een vooraf opgedrukte volgnummer
Als we die dozen aankopen dan volgen deze nummers netjes op elkaar voor die 1000 facturen, een volgende doos heeft een heel andere nummering
vandaar
ik kocht vorig jaar bvb een doos met nummers die ALLEMAAL starten met dezelfde eerste 4 karakters, bvb 5623, ik koop er nu een die straten allemaal met 2534
Dus
ik kan geen sortering doen op die eerste karakters, want dan komen, in mijn lijst, die van nu VOOR die van vorig jaar te staan... dus, niet goed
om het handig te maken verdeel ik die door in groepjes van 100 facturen, vandaar dat ik de eerste 5 karakters neem als groep
een volledige factuurnummering ziet er bvb als volgt uit:
5623700 tot 5623799
dan
5623800 tot 5623899
dan
5623900 tot 5623999

ik neem de eerste 5 karakters voor de linker lijst, kies hier een van die data en krijg rechts de 100 facturen die allemaal starten met die eerste 5 karakters

Zoals het nu is loopt het perfect, maar

Die verdomde dozen eindigen allemaal met een factuur waarvan er maar 1, ipv van 100 cijfers bestaat
Nu is dat bvb 5624000
er bestaat dus geen 5624001 of hoger

Op de manier waarop ik de query maak, en filter op nummers die niet op een 0 eindigen, komt dus deze laatste factuur niet in de lijst voor.

En laat nu net DAT het probleem zijn

Ik kan je de data opsturen maar dan is het bestand te groot, zelfs al schrap ik de oudste records, dan nog

Hopelijk is het nu wat duidelijker....,,, :thumb:

 

[Pletter]

Query referenties

JEPEDEWE,

De Query gegevens van jouw database in topic 1 moet hiervan het resultaat zijn zoals in de bijlage?

Groet, COr.

 

[Tardis]

JEPEDEWE,

kan het mis hebben maar je lijkt jezelf tegen te spreken.
Je geeft aan dat je een doos koopt met 100 facturen.
Volgnummer is de eerste vier cijfers.
Dus, als je een doos koopt met volgnummer 5324 is het eerste factuurnummer 5324000 en het laatste is 5324999.
In totaal 1000 facturen.
Hoe kan het laatste factuurnummer dan 5424000 zijn?

Tardis

 

[OctaFish]

Je vertelt het verhaal nu (eindelijk ) op een manier waaruit het probleem duidelijk wordt. En dat wordt dus niet veroorzaakt doordat je groepering van 100 niet klopt, maar omdat je de facturen per duizend binnenkrijgt, en het hoogste nummer niet tot de reeks behoort. Want je laatste nummer eindigt op 2546000, en je weet niet of de volgende doos met 2546001 begint, dat kan net zo goed 4237001 zijn.
De makkelijkste manier zou denk ik zijn om die factuur als eerste te pakken, en dan pas de rest, maar dat zal wel niet kunnen. Meestal genereer je een factuurnummer als je hem nodig hebt, maar in jouw geval werkt dat dus niet. Ik zou dan voor de factuurnummers een aparte tabel maken met een Autunummer als sleutelveld, en die vullen als je een nieuwe doos nummers binnen krijgt. Daarvoor kun je een simpele procedure maken die met een Recordset de nummers toevoegt aan de tabel. Je begint dan bijvoorbeeld met 2546001 en de procedure eindigt met 2547000.
De facturen staan dan netjes op de goed volgorde, en je kunt dan nog steeds groepen maken van 100, gebaseerd op het eerste nummer dat in de groep zit (laagste ID lookup) met de volgende 100 records. Sowieso kun je dan het eerstvolgende vrije nummer als standaardwaarde in je factuur zetten, want de autonummers zijn per definitie goed gesorteerd.

 

[JEPEDEWE]

@Tardis
Ik krijg de facturen inderdaad per 1000 binnen, maar telkens verdeeld in bundels van 100:
7412001 tot 7412099
7412100 tot 7412199
7412200 tot 7412299
enz
in de doos zit geen 7412000
koop ik een volgende aan dan kan dit starten met bvb
1232001 enz
dus als je ordenen op de eerste 5 karakters lukt niet want dan is de chronologie weg

@ Octafish
dat gaat boven mijn petje vrees ik... ben al blij dat je het begrijpt

JP

 

[OctaFish]

Volgens mij leg je het nu toch weer anders uit: eerst zeg je dat je de facturen per 2000 binnen krijgt, en nu maak je er er 1000 van. En ze zijn gebundeld? Dus je pakt een willekeurige stapel van 100 facturen met een stiekie er omheen, en die ga je gebruiken?
En je nummering snap ik nu ook niet meer: volgens mij moet het dan zijn:
Doos 1:
7412001 - 7413000
7413001 - 7414000
Doos 2:
7467001 - 7468000
7468001 - 7469000
Enz.
En dat je dus niet met ####000 kunt beginnen, omdat elke reeks met ###(#+1)000 eindigt.
Maar wat mij betreft mag je de db wel doorsturen, dan kijk ik er wel naar. Ik blijf van mening dat het simpel is op te lossen.