[SQL/MsAccess] Unieke tijdperiode

[W00fer]

Ik heb een kleine database waar ik auto's uit kan lenen.

Heb een unieke index gemaakt die Auto nummer 1 en Uitleendatum 1 koppelt aan elkaar, zodat een auto op een dag vergeven is.

Een 2e index is gemaakt om een einddatum aan te geven.

Nu kan ik echter de auto nog steeds uitlenen aan iemand anders, op de tussenliggende data.

Hoe kan ik dit voorkomen ?

Bedankt alvast.

[BalusC]

Gewoon niet uitlenen zodra het datum daartussen valt?

[RobIII]

Zorgen dat de auto+gewenste-startdatum combinatie niet ligt tussen een willekeurige start-einddatum+auto combinatie

[ Voor 8% gewijzigd door RobIII op 09-12-2008 02:32 ]

[W00fer]

Bedankt voor jullie reactie op dit tijdstip.

Hoe doe ik dit in access, ik ben niet zo'n programmeerheld.

[ Voor 4% gewijzigd door W00fer op 09-12-2008 02:43 ]

[RobIII]

W00fer schreef op dinsdag 09 december 2008 @ 02:42:
Hoe doe ik dit in access, ik ben niet zo'n programmeerheld.

SQL:

Select foo, bar
from sometable
where requestedcarid = sometable.carid and requesteddate between sometable.startdate and sometable.enddate

Krijg je records terug -> niet verhuren; anders: go.
Wel nog even rekening houden met overlap op gewenste einddatum e.d., maar met wat creatief queryen kom je daar wel uit dan

[ Voor 20% gewijzigd door RobIII op 09-12-2008 02:49 ]

[W00fer]

Hoe is dit toe te passen in een formulier. Want mijn formulier is gebaseerd op een table. Zou ik dan het formulier op moeten bouwen nav de query ?

Ik snap niet wat je bedoeld met creatief querien.

[ Voor 15% gewijzigd door W00fer op 09-12-2008 09:25 ]

[The_Ghost16]

Gewoon een extra veld maken met de status verhuurbaar en daar een 1 inzetten als de auto niet verhuurd is en een 0 als de auto verhuurd is. En die waarde updaten als de startdate is of als de enddate is.

[RobIII]

W00fer schreef op dinsdag 09 december 2008 @ 09:24:
Hoe is dit toe te passen in een formulier. Want mijn formulier is gebaseerd op een table. Zou ik dan het formulier op moeten bouwen nav de query ?

Je zou natuurlijk eens een poging kunnen doen i.p.v. verwachten dat wij je handje gaan vast houden Mocht je dan ergens tegen aan lopen dan kun je op z'n minst concrete vragen stellen; de manier waarop nu je topic loopt is niet echt de bedoeling op GoT. We verwachten toch zeker wel wat eigen inzet en als je niet thuis bent in de materie dan zul je het of moeten uitbesteden aan iemand die dat wel is, of bereid moeten zijn je te verdiepen in die materie en het je eigen te maken.

The_Ghost16 schreef op dinsdag 09 december 2008 @ 09:32:
Gewoon een extra veld maken met de status verhuurbaar en daar een 1 inzetten als de auto niet verhuurd is en een 0 als de auto verhuurd is. En die waarde updaten als de startdate is of als de enddate is.

NOFI, maarre... what? Dat is niet eens in de verste verte een oplossing...

[ Voor 20% gewijzigd door RobIII op 09-12-2008 09:41 ]

[W00fer]

Tja zo raak ik ook door de war.

Ik probeer een oplossing te verzinnen, maar na vele uren zoeken is het nog niet gelukt.

[RobIII]

W00fer schreef op dinsdag 09 december 2008 @ 10:02:
Tja zo raak ik ook door de war.

Des te meer reden om je te verdiepen in waar je mee bezig bent; dan sta je wat steviger in je schoenen en heb je tenminste een 'clue' waar we over praten

[W00fer]

Ik zal eens kijken wat ik kan maken.

[MSalters]

De vraag of twee intervallen [a,b] en [c,d] overlappen is niet helemaal triviaal. Het antwoord is ja, dan en slechts dan als max(b,d)-min(a,c) < (b-a) + (d-c).

[farlane]

Maw of de eindatum van periode A op of voorbij de startdatum van periode B ligt en de startdatum van periode A op of voor de eindatum van periode B.

[W00fer]

Het punt waar ik mee zit is ALS een auto al is ingepland op DATUM 1 en DATUM 2 (bijvoorbeeld 1 t/m 5 januari), dat dan 1 en 5 januari uniek zijn voor die klant op die auto.

Klant nummer 2 kan vervolgens vrolijk de auto van 3 tot en met 6 januari lenen, wat dus niet zou mogen.

Daarom zit ik met die unique index welke niet werkt.

[RobIII]

W00fer schreef op dinsdag 09 december 2008 @ 12:19:
Daarom zit ik met die unique index welke niet werkt.

Het heeft ook geen drol met unique indices te maken

[W00fer]

farlane schreef op dinsdag 09 december 2008 @ 12:12:
Maw of de eindatum van periode A op of voorbij de startdatum van periode B ligt en de startdatum van periode A op of voor de eindatum van periode B.

Dat snap ik, maar als periode B nog niet ingevuld is (het eerste record vormt periode A) dan kun je dat niet afdichten.

Vanaf het 2e record moet hij dus bekijken of de auto:

A ingepland is (werkt, maar laat tussenliggende data toe zoals gezegd)
B Uitgeleend is
C Uitgeleend is op de gewenste periode
D Een foutboodschap geven

[W00fer]

RobIII schreef op dinsdag 09 december 2008 @ 12:19:
[...]

Het heeft ook geen drol met unique indices te maken

Kan ik de query die jij noemt als validation rule gebruiken dan ?

[mocean]

Ik zou een gedeelde agenda aanmaken (Outlook, Gmail) per auto. Volgens mij is dat net zo gemakkelijk.

En anders jezelf eens verdiepen in SQL, relaties tussen tabellen gebruiken, Query's. Het kijken of een datum tussen 2 andere datums valt is niet echt heel moeilijk, ook niet voor een beginner:

[farlane]

W00fer schreef op dinsdag 09 december 2008 @ 12:24:
Dat snap ik, maar als periode B nog niet ingevuld is (het eerste record vormt periode A) dan kun je dat niet afdichten.

Periode A is de periode waarin je de auto wilt uitlenen, periode B is de periode ( if any ) waarin de auto al uitgeleend is. Maw als je een query uitvoert die een resultaat oplevert voor periode B voor die auto betekent dat dat deze auto al uitgeleend is in die periode en dus "bezet" is.

Overigens moet je dus niet kijken of een datum "tussen" twee andere data valt want dat dekt niet alle mogelijkheden af.

Al met al denkt ik dat je iets teveel hooi op je vork neemt; je mist volgens mij wat basiskennis database design om dit tot een goede oplossing te brengen. Je begint over unique keys terwijl deze bij dit probleem niet van toepassing zijn: je kunt deze niet gebruiken om je probleem volledig op te lossen.

[ Voor 19% gewijzigd door farlane op 09-12-2008 16:41 ]

[W00fer]

Aan database kennis zal het niet liggen, eerder aan het logisch toepassen van code. Daar ben ik niet echt goed in.

Een between code kan ik ook nog wel verzinnen, dat is het punt niet.

Ik heb na veel zoeken vandaag ergens anders wat gevonden, hopen dat het daarmee lukt !

[ Voor 41% gewijzigd door W00fer op 09-12-2008 19:10 ]

[pedorus]

W00fer schreef op dinsdag 09 december 2008 @ 19:10:
Ik heb na veel zoeken vandaag ergens anders wat gevonden, hopen dat het daarmee lukt !

Wat heb je gevonden, en houdt het ook rekening met andere datuminstellingen? En verder kun je best snel wat vinden, hier bijvoorbeeld.

[ Voor 31% gewijzigd door pedorus op 10-12-2008 00:49 ]

[W00fer]

Dat is inderdaad ook nog een probleem. Ik ga uit van de formatting van het datumveld.

In mijn geval: middellange datumnotatie.

Access houd standaard rekening met de US format dacht ik zo.

[ Voor 12% gewijzigd door W00fer op 10-12-2008 01:32 ]

[W00fer]

pedorus schreef op woensdag 10 december 2008 @ 00:41:
[...]

Wat heb je gevonden, en houdt het ook rekening met andere datuminstellingen? En verder kun je best snel wat vinden, hier bijvoorbeeld.

Die code werkt echt niet feilloos. Ik kan nog gewoon data invullen. Alleen een enkelvoudige check is ingebouwd tijdens het invoeren. Als ik het later aanpas, kan het alsnog. Dat zou niet moeten mogen.

[pedorus]

W00fer schreef op woensdag 10 december 2008 @ 21:34:
Die code werkt echt niet feilloos. Ik kan nog gewoon data invullen. Alleen een enkelvoudige check is ingebouwd tijdens het invoeren. Als ik het later aanpas, kan het alsnog. Dat zou niet moeten mogen.

Dat lijkt me ook. Zit hem in:

quote: http://groups.google.nl/g...orms/msg/d3efaacfa9d69d99

Visual Basic:

    If IsNull(Me.[Date]) Or IsNull(Me.[StartTime]) Or _
        IsNull(Me.[End Time]) Or (Me.[Date] = Me.[Date].OldValue) Or _
        (Me.[Start Time] = Me.[Start Time].OldValue) Or _
        (Me.[End Time] = Me.[End Time].OldValue) Then
            'do nothing
    Else 

denk ik zo... Or tussen de OldValue's moet vast And zijn...

[W00fer]

Ik heb nu gewoon gezegd dat bewerken van het form niet meer mogelijk is, only allow new fields. Maarja een mooie oplossing is het niet.

[W00fer]

Deze code werkt al een heel stuk beter:

code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    With Me
        If IsNull(.Employee_ID) Or IsNull(.Start) Or IsNull(.End) Then
            MsgBox "Field(s) required for validation is/are empty"
            Cancel = True
            Exit Sub
        End If
        If DCount("Employee_ID", "tblscheduling", _
            "(([Start]>=#" & .Start & "# And [Start]<#" & .End & _
            "#) Or ([End]>#" & .Start & "# And [End]<=#" & .End & _
            "#) Or ([Start]<=#" & .Start & "# And [End]>=#" & .End & _
            "#)) AND [Employee_ID]=" & .Employee_ID)<>0 Then
            MsgBox ("time interval overlapped bla bla bla")
            Cancel = True
        Else
            Cancel = False
        End If
    End With
 
End Sub

Daarmee heb je ook tijdvalidatie NA invoeren, dus ook bij bewerken. Raar dat dat toch met OR values werkt.

EDIT: Helaas werkt dit NIET met datumintervallen, alleen met tijdintervallen. DAMN

[ Voor 10% gewijzigd door W00fer op 10-12-2008 22:51 ]

[pedorus]

W00fer schreef op woensdag 10 december 2008 @ 22:47:
EDIT: Helaas werkt dit NIET met datumintervallen, alleen met tijdintervallen. DAMN

Dat komt omdat je de logica niet hetzelfde hebt gehouden (misleidt door dit topic? ), en doordat je format hebt weggelaten. Ik gok dat zoiets wel werkt:

Visual Basic:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim varResult As Variant 
    With Me
        If IsNull(.Employee_ID) Or IsNull(.Start) Or IsNull(.End) Then
            MsgBox "Field(s) required for validation is/are empty"
            Cancel = True
            Exit Sub
        End If
        varResult = DLookup("Employee_ID", "tblscheduling", _
            "(" & Format(.Start, "\#mm\/dd\/yyyy\#") & _
            " <= [End]) AND ([Start] <= " & _
            Format(.End, "\#mm\/dd\/yyyy\#") & _
            ") AND ([Employee_ID] <> " & .Employee_ID & ")")
        If not IsNull(varResult) Then
            MsgBox "time interval overlapped with Employee ID " & varResult
            Cancel = True
        Else
            Cancel = False
        End If
    End With 
End Sub

Dit gaat ervan uit dat er op tabelniveau een Validation Rule zit dat [Start]<=[End].
Employee_ID en tblscheduling vind ik trouwens vreemde namen. Ik zou meer iets als Appointments(AppointmentId, Start, End) verwachten.

[W00fer]

Waarom pak je varresult = dlookup ?

[W00fer]

pedorus schreef op woensdag 10 december 2008 @ 23:33:
[...]

Dat komt omdat je de logica niet hetzelfde hebt gehouden (misleidt door dit topic? ), en doordat je format hebt weggelaten. Ik gok dat zoiets wel werkt:

Visual Basic:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim varResult As Variant 
    With Me
        If IsNull(.Employee_ID) Or IsNull(.Start) Or IsNull(.End) Then
            MsgBox "Field(s) required for validation is/are empty"
            Cancel = True
            Exit Sub
        End If
        varResult = DLookup("Employee_ID", "tblscheduling", _
            "(" & Format(.Start, "\#mm\/dd\/yyyy\#") & _
            " <= [End]) AND ([Start] <= " & _
            Format(.End, "\#mm\/dd\/yyyy\#") & _
            ") AND ([Employee_ID] <> " & .Employee_ID & ")")
        If not IsNull(varResult) Then
            MsgBox "time interval overlapped with Employee ID " & varResult
            Cancel = True
        Else
            Cancel = False
        End If
    End With 
End Sub

Dit gaat ervan uit dat er op tabelniveau een Validation Rule zit dat [Start]<=[End].
Employee_ID en tblscheduling vind ik trouwens vreemde namen. Ik zou meer iets als Appointments(AppointmentId, Start, End) verwachten.

De door jou voorgestelde code werkt niet. Het heeft met het datumformat te maken.

[farlane]

W00fer schreef op donderdag 11 december 2008 @ 00:58:
[...]


De door jou voorgestelde code werkt niet. Het heeft met het datumformat te maken.

Houd voor queries het (iso) YYYY-MM-dd HH:mm:ss formaat aan, dat zou eender welke landinstelling moeten werken.

http://www.w3.org/QA/Tips/iso-date

[RobIII]

farlane schreef op donderdag 11 december 2008 @ 11:14:
[...]


Houd voor queries het (iso) YYYY-MM-dd HH:mm:ss formaat aan, dat zou eender welke landinstelling moeten werken.

http://www.w3.org/QA/Tips/iso-date

Ook in onze eigen FAQ's

[farlane]

RobIII schreef op donderdag 11 december 2008 @ 11:26:
Ook in onze eigen FAQ's

Owh, hebben wij een FAQ?

[pedorus]

W00fer schreef op donderdag 11 december 2008 @ 00:45:
Waarom pak je varresult = dlookup ?

Omdat je dan kan zien met welk record je overlapt, het sneller is (je hoeft alleen de eerste overlap te vinden, niet alle), en het in de nieuwsgroeppost ook zo werd genoemd.

W00fer schreef op donderdag 11 december 2008 @ 00:58:
De door jou voorgestelde code werkt niet. Het heeft met het datumformat te maken.

Het datumformaat is in US zoals Access dat verwacht. Hij werkt bij mij trouwens ook gewoon als ik het snel even test met een tabel tblscheduling(Employee_ID, Start, End). Regel 17 en 18 zijn trouwens onzin, en als je ook uren kan invoeren gaat het mis (als dat kan met het inputmask).
Dus, succes met zelf oplossen...

[W00fer]

Ik heb geen inputmask gekozen, omdat je dan een calendar chooser krijgt in Access 2007. Ik weet dat het ook via een ActiveX plugin kan, maar dat is veel lelijker, en genereerd meer load.