Die Basisabfrage ist in MS-
Access SQL so:
SELECT Count(ProcessAlarms_1.TagName) AS AnzahlvonTagName, ProcessAlarms.SubType, ProcessAlarms.TagName, ProcessAlarms_1.StartDate, ProcessAlarms_1.ALMTime, ProcessAlarms_1.Type, ProcessAlarms_1.PTDesc, ProcessAlarms_1.ALMType, ProcessAlarms_1.ALMLimit, ProcessAlarms_1.ALPRrior, ProcessAlarms_1.Unit, ProcessAlarms_1.PV, ProcessAlarms_1.X1, ProcessAlarms_1.X2, ProcessAlarms.StartDate
FROM ProcessAlarms RIGHT JOIN ProcessAlarms AS ProcessAlarms_1 ON (ProcessAlarms.SubType = ProcessAlarms_1.SubType) AND (ProcessAlarms.TagName = ProcessAlarms_1.TagName)
GROUP BY ProcessAlarms.SubType, ProcessAlarms.TagName, ProcessAlarms_1.StartDate, ProcessAlarms_1.ALMTime, ProcessAlarms_1.Type, ProcessAlarms_1.PTDesc, ProcessAlarms_1.ALMType, ProcessAlarms_1.ALMLimit, ProcessAlarms_1.ALPRrior, ProcessAlarms_1.Unit, ProcessAlarms_1.PV, ProcessAlarms_1.X1, ProcessAlarms_1.X2, ProcessAlarms.StartDate
HAVING (((ProcessAlarms_1.StartDate)>#11/8/2005 14:14:0# And (ProcessAlarms_1.StartDate)<#11/8/2005 15:14:0#) AND ((ProcessAlarms.StartDate)>#11/8/2005 14:14:0# And (ProcessAlarms.StartDate)<#11/8/2005 15:14:0#))
ORDER BY Count(ProcessAlarms_1.TagName) DESC , ProcessAlarms.TagName, ProcessAlarms_1.StartDate;
Die auf dem Ergebnis aufbauende Abfrage ist:
SELECT Count(Abfrage1.AnzahlvonTagName) AS AnzahlvonAnzahlvonTagName, Abfrage1_1.SubType, Abfrage1_1.TagName, Abfrage1_1.StartDate, Abfrage1_1.ALMTime, Abfrage1_1.Type, Abfrage1_1.PTDesc, Abfrage1_1.ALMType, Abfrage1_1.ALMLimit, Abfrage1_1.ALPRrior, Abfrage1_1.Unit, Abfrage1_1.PV, Abfrage1_1.X1, Abfrage1_1.X2
FROM Abfrage1 INNER JOIN Abfrage1 AS Abfrage1_1 ON Abfrage1.TagName = Abfrage1_1.TagName
GROUP BY Abfrage1_1.SubType, Abfrage1_1.TagName, Abfrage1_1.StartDate, Abfrage1_1.ALMTime, Abfrage1_1.Type, Abfrage1_1.PTDesc, Abfrage1_1.ALMType, Abfrage1_1.ALMLimit, Abfrage1_1.ALPRrior, Abfrage1_1.Unit, Abfrage1_1.PV, Abfrage1_1.X1, Abfrage1_1.X2;
Also etwas einfacher:
Das Problem ist, das ich es nicht schaffe die beiden Join Typen in einer Abfrage zu verknüpfen.