Standortverwaltung Geräte - letztes Datum in einer Gruppe

[Erklärbär]

Hallo,

ich brauche mal wieder eine Idee wie man diese Abfrage hinbekommt.

Szenario:
In einer Geräteverwaltung wird für jedes Gerät erfasst ab wann es einem Standort zugeordnet ist.
Da ein Gerät ja nur an einem Standort gleichzeitig kann wird nur das Datum des aktuellen Standortes
erfasst. Gesucht ist eine Abfrage, die mir für jedes Gerät den aktuellen Standort zeigt.

es gibt 3 Tabellen:
1.Ort      OrtID      verknüpft mit Tabelle 3
      OrtName

2.Gerät      GerätID      verknüpft mit Tabelle 3
      GerätName

3.Aufenthalt   
      AbDatum
      OrtID
      GerätID
      Bemerkung


Für die Abfrage ist jetzt nur Tabelle 3 relevant.
Meine Abfrageidee sieht so aus:

GerätID:   sortieren-aufsteigend, gruppieren
AbDatum:   Sortieren-aufsteigend, maximalwert
GerätID:   ?

da lässt sich leider keine Funktion finden, die den ganzen Datensatz mitschleppt.

Habt Ihr einen Tip für mich?

[oma]

Hallo evt. so:

Code [Auswählen] Erweitern

select tblAufenthalt.GerätID,
first(tblGeräte.Gerätname) AS Gerät,
first(tblOrte.OrtName) AS Ort,
max(tblAufenthalt.DatumAuf) AS Datum
from(tblAufenthalt inner join tblGeräte on tblAufenthalt.GerätID = tblGeräte.GerätID)
inner join tblOrte ON tblAufenthalt.OrtID = tblOrte.OrtID
group by tblAufenthalt.GerätID

Gruß Oma

[oma]

Hallo,

nee, nach Überlegung gehts so nicht.

Aber so gehts :

Code [Auswählen] Erweitern

SELECT tblAufenthalt.GerätID,
First(tblGeräte.Gerätname) AS Gerät,
Max(tblAufenthalt.DatumAuf) AS LetztesDatum,
CLng(DLookUp("OrtID","tblAufenthalt","GerätID=" & [tblAufenthalt.GerätID] & " AND DatumAuf=" & Format([LetztesDatum],"\#yyyy-mm-dd\#"))) AS ID,
DLookUp("OrtName","tblOrte","OrtID=" & [ID]) AS Ort
FROM tblAufenthalt INNER JOIN tblGeräte ON tblAufenthalt.GerätID = tblGeräte.GerätID
GROUP BY tblAufenthalt.GerätID;


Hierbei ist
tblAufenthalt:  AufenthaltID, OrtID, GerätID, DatumAuf, Bemerk
tblGeräte:        GerätID, Gerätname
tblOrt:              OrtID, OrtName

Gruß Oma

[Erklärbär]

Hallo Oma,

erst mal vielen Dank für Deine Antwort.
Eins vorab, es klappt! Zumindest das Feld OrtID, der Ort Text zeigt Fehler, ist auch erst mal egal.

Deine Lösung hat mich dazu beflügelt mal mit der DLookup (DomWert) Funktion zu experimentieren (Stundenlang  ).
Ich möchte das auch richtig wissen was da passiert.

Wenn ich das richtig verstanden habe sucht die DomWert Funktion in einer eigenen Abfrage den passenden Datensatz
wo das Datenpaar GerätID und AbDatum übereinstimmt und gibt den Wert für OrtID zurück. Soweit so gut.

Um die Sache zu vereinfachen habe ich die Abfrage nur auf die Tabelle Aufenthalt reduziert und die Verknüpfung rausgenommen.
Als Ergebnis brauche ich dann nur noch:

   GerätID
   AbDatum
   OrtID

Das Feld AbDatum hast Du in der Abfrage schon mit "LetztesDatum" benannt.
Ich konnte die Sache bis zu dem Datumsvergleich nachvollziehen, dann hörte es auf.
Was wird jetzt eigendlich miteinander verglichen? Der Datums-String?
Ich komme darauf weil Du die Format Funktion benutzt.
In Access sind ja bekannlicherweise Datumsoperationen mit Vorsicht zu gebrauchen
um nicht bei anderen Zeitformatierungen schwierigkeiten zu bekommen.
Daher würde ich aus dem Bauch heraus nicht den String sondern den intern gespeicherten Double-Wert miteinander vergleichen.
Das habe ich aber bisher noch nicht hinbekommen.
Kannst du mir den Teil noch mal erklären?

Gruß
Ralph

[database]

Hallo Ralph,

möchte versuchen dir die Sache ein wenig näher zu bringen.

DLookup / DomWert ist eine der s.g. Domänenfunktionen.
Mit Hilfe derer ist es möglich, aus einer 'DOMÄNE', welche eine Tabelle oder Abfrage (eine Datensatzgruppe) darstellen kann, einen bestimmten 'WERT' durch Anwendung einer WHERE-Klausel zu ermitteln.
Domänenfunktionen werden meist in VBA-Codes verwendet können aber auch in der vorliegenden Syntax in Access-Abfragen eingesetzt werden.

Wenn ich das richtig verstanden habe sucht die DomWert Funktion in einer eigenen Abfrage den passenden Datensatz

...bist ziemlich nahe drann - sie ermittelt einen gespeicherten Wert aus einem explizit angegebenen Feld des durch die WHERE-Klausel gefilterten Datensatzes. (klassisches Deutsch ... ich liebe meine Sprache  )

Oma hat in der SQL die WHERE-Klausel der Dlookup unter anderem mit einer Einschränkung auf einen Datumseintrag bedacht.
Um diesen Vergleich anstellen zu können muß das Datum von der Deutschen Schreibweise ins US- (mm/dd/yy) oder ISO-Format (yyyy-mm-dd) umgewandelt werden - dieses passiert mit der Format-Funktion.
Die Jet-Engine, die für die Umsetzung der SQL bei Access zuständig ist verlangt diese Schreibweise wenn ihr ein Datum zur Bearbeitung übergeben wird.

In Access sind ja bekannlicherweise Datumsoperationen mit Vorsicht zu gebrauchen
um nicht bei anderen Zeitformatierungen schwierigkeiten zu bekommen

Das ist nicht nur bei Access so, der SQL-Server kann ebenfalls ganz schön beleidigt sein. Das Problem liegt nicht an den Anwendungsprogrammen, das Problem liegt an den Anwendern.
Es ist klarerweise einem Deutschsprechenden angenehmer, das Datum in seiner, ihm eigenen Schreibweise zu verwenden - Datenbanken sind da halt manchmal anderer Meinung.   

Daher würde ich aus dem Bauch heraus nicht den String sondern den intern gespeicherten Double-Wert miteinander vergleichen

Wenn du dich an die Vorgabe des Umwandelns hältst kann nicht viel passieren - da brauchst du das Gewürge mit dem Zahlenspiel nicht und ein Datum darf ein Datum bleiben 

Wenn du das aber mal probieren möchtest:

Code [Auswählen] Erweitern


Dim DatumMitZeit as Double
Dim DatumOhneZeit as Long
DatumMitZeit = Str(CDbl(DeinDatumZeitFeld))    'Für Datumsfelder, die Datum + Uhrzeit enthalten
DatumOhneZeit = CLng(DeinDatumfeld)            'Für Datumsfelder in denen NUR das Datum gespeihert ist


Erklärungen zur DLookup-Funktion findest du in recht umfangreicher Ausführung unter Anderem auch in der VBA-Hilfe unter dem Suchbegriff DLookup
Info zur Datumsformatierung unter Anderem bei donkarl ...  http://www.donkarl.com?FAQ6.8

HTH

[oma]

Hallo Ralph,

einmal ein Versuch, die einzelnen Bestandteile der Abfrage zu erläutern

Code [Auswählen] Erweitern

SELECT tblAufenthalt.GerätID,
First(tblGeräte.Gerätname) AS Gerät,
Max(tblAufenthalt.DatumAuf) AS LetztesDatum,
CLng(DLookUp("OrtID","tblAufenthalt","GerätID=" & [tblAufenthalt.GerätID] & " AND DatumAuf=" & Format([LetztesDatum],"\#yyyy-mm-dd\#"))) AS ID,
DLookUp("OrtName","tblOrte","OrtID=" & [ID]) AS Ort
FROM tblAufenthalt INNER JOIN tblGeräte ON tblAufenthalt.GerätID=tblGeräte.GerätID
GROUP BY tblAufenthalt.GerätID

Evt. ist es mit der QBE-Entwurfsansicht (siehe Bild) besser zu erklären:

1. Die Tabelle tblAufenthalt und die Tabelle tblGeräte werden verknüpft
2. Es wird nach dem Feld GerätID  gruppiert
3. Der Name des Gerätes wird dargestellt (Erster Wert aus der Domäne (=Datensatzgruppe) der Gruppierung
4. Aus der Domäne wird das maximale Datum ermittelt u. als LetztesDatum   bezeichnet
5. Nun kann ermittelt werden, in welchem Ort ein Gerät mit jeweiligen max. Datum sich aufgehalten hat

Dazu wird eine ID aus einer Domäne ermittelt; es wird mit der Domänenfunktion ein Wert (Domwert) aus einer Domäne ( Tabelle tblAufenthalt) ermittelt u. zwar die OrtID.  Hierzu sind 2 Kriterien nötig: die GerätID der Gruppierungsdomäne und das ermittelte Feld  LetztesDatum
Mit dieser ID kannst du mit der nächsten dargestellten Domänenfunktion mit der ermittelten ID des Ortes auch den Ortsnamen ermitteln.

Naja, ich hoffe du hast nun mit beiden Erläuterungen die Sache verstanden

Gruß Oma


[Anhang gelöscht durch Administrator]

[Erklärbär]

Hallo Peter,
hallo Oma,

erst mal vielen Dank für Eure ausführlichen Erklärungen.
Jetzt, so hoffe ich, ist der Groschen gefallen.
Von der Logig her war mir die Sache schon klar. Probleme hatte ich nur bei der Umsetzung der Syntax.

Ich hatte mich zu sehr darauf versteift auf jeden Fall den Vergleich "binär" und nicht mit einem formatierten String zu machen.
Die Erklärung mit der WHERE Klausel brachte dann die Erleuchtung. Letztendlich ist es ja nicht die Vergleichsoperation selbst,
sondern "eine als Text verfasste Anweisung" an die Engine, wie eben auch bei der WHERE Klausel. Wie die Engine dann den Vergleich
tatsächlich dürchführt ist auf diesem Wege nicht zu beeinflussen. Kann man das so sagen?

In meinen ursrünglichen Überlegungen bin ich davon ausgegangen, dass wenn ich in einem Ausdruck die Felder miteinander vergleiche,
die tatsächlichen inhalte (float) miteinander verglichen werden. Erst wenn diese zur Anzeige gebracht werden, würde eine entsprechende
Formatierung durchgeführt. Dann wäre aber eine Format-Anweisung nicht erforderlich, da ich identische felder miteinander vergleiche!?

Also gilt die Faustregel bei Datums- / Zeit-Ausdrücken immer in ISO-Format umwandeln, dann passt es immer unabhängig von den persönlichen Einstellungen,
wie Länder- und Sprachoptionen?

Gruß
Ralph

[oma]

Hallo Ralph,

sondern "eine als Text verfasste Anweisung" an die Engine, wie eben auch bei der WHERE Klausel. Wie die Engine dann den Vergleich tatsächlich dürchführt ist auf diesem Wege nicht zu beeinflussen. Kann man das so sagen?

Kann man eigentlich nicht ganz so sagen; das ist eben die Datenbanksprache SQL, die auf die Daten zugreift. Dabei ist einfach bei der Benutzung des Datums in einer WHERE-Bedingung folgendes zu beachten::

Soll in einem Abfrage-Entwurf ein Datum als Kriterium eingesetzt werden, so muss das Datum in Nummernzeichen eingeschlossen sein, wie z.B.:  #03.12.2010#      Access macht daraus automatisch:   SELECT *   FROM tblDaten WHERE DatumA=#12/3/2010#
Es wird ersichtlich: SQL braucht das amerikanische Datumsformat mit dem Muster mm/dd/yy !

Soll das Datum als Kriterium variabel sein und z.B. aus einem Formular einem SQL-String übergeben werden, muss dieser Wert in dem SQL-Ausdruck "eingebaut" werden. Hierzu eignet sich eben die Funktion Format(), die aus dem Datum ein String baut, der dem US-Format entspricht.

Aber es gibt noch andere Möglichkeiten!  Du kannst natürlich auch ausnutzen, dass das Datum als Zahl gespeichert wird! Folgendes ist möglich:


 strSQL = "SELECT  ...   WHERE  DatumA = " & Format(DatumFeld, "\#mm\ / dd \ / yyyy \#")    ---> mit  US Format  
 strSQL = "SELECT  ...   WHERE  DatumA = " & Format(DatumFeld, "\#yyyy-mm-dd\#")             --->mit  ISO-Format  
 strSQL = "SELECT  ...   WHERE  DatumA = DateValue(' " & Datumsfeld & ' ")"                           ---> mit  Funktion DateValue  
 strSQL = "SELECT  ...   WHERE DatumA ="  & CDbl(DatumFeld)                                                 ---> mit  Funktion CDbl  
 strSQL = "SELECT  ...   WHERE DatumA = " & DatumSQL(DatumFeld)                                        ---> mit  Funktion DatumSQL  

Wie du siehst, kannst du auch das Datum als Zahl abfragen  (siehe Funktion CDbl)
Wenn du öfters in einer DB mit Datumsvergleiche arbeitest, ist die letzte Variante die beste! Dabei hats du in deiner DB in ein beliebiges Modul eine Funktion, die ein übergebenes Datum als String in ISO-Format zurück liefert:

Code [Auswählen] Erweitern

Public Function DatumSQL(dat As Date) As String
DatumSQL = Format(dat, "\#yyyy-mm-dd\#")
End Function

Somit kannst du in beliebigen Abfrragen einfach nur schreiben   ...WHERE DatumA = " & DatumSQL(DatumFeld)

Wie du richtig bemerkt hast, sollte man immer das  ISO-Format benutzen, da es unabhängig von Spracheinstellungen und auch in anderen SQL-Dialekten vorhanden ist.

Nun haben wir das Thema aber ausführlich diskutiert u. evt hat ja auch mancher Mitleser etwas von unserer Diskussion

Gruß Oma

[database]

Hallo,

vielleicht solten wir unsere Ausführungen Microsoft für's nächste Update der OH anbieten ...  (und mächtig Kohle damit machen) 

[oma]

Hallo,

jo, wir müssen nur noch Billy unsere Seite bekannt machen. Hast du nicht einige Beziehungen zu Redmond??

Gruß Oma

[database]

Hmmm...

Redmond

ich fürchte fast du meinst damit NICHT das da:
http://www.redmonds.de/

DIE kenn ich - Redmond's Billy eher weniger 

[oma]

Hallo,

nee, ich meine  http://www.microsoft.com/presspass/gallery/campus.mspx

in der Cafeteria (3. Bild oben links) können wir uns dann mal treffen...
Zusammen mit Ralph ... und Franz nehmen wir auch mit!


Gruß Oma

[database]

@oma

Auf JEDEN Fall - ich bin dabei!   

[Erklärbär]

... Ihr habt ganz offensichtlich Spass an der Sache.
Finde ich superklasse 

Ich leg bald noch einen nach 

Gruß
Ralph