Avoid double rows in a query

[L4serK!LL]

(SELECT DistinctUIT.Datum, DistinctUIT.[Uit] as Tijd, Count(Planning_1.ID)-1 AS AantalVanID
FROM Planning AS Planning_1 RIGHT JOIN DistinctUIT ON Planning_1.Datum = DistinctUIT.Datum
WHERE (((Planning_1.[In])<=[DistinctUIT].[Uit]) AND ((DistinctUIT.Datum)=[forms]![Afspraken]![datum]) AND ((Planning_1.[Uit])>=[DistinctUIT].[Uit]) AND ((Planning_1.[Wacht?])=True))
GROUP BY DistinctUIT.Datum, DistinctUIT.[Uit]
ORDER BY DistinctUIT.[Uit])
UNION (SELECT DistinctIN.Datum, DistinctIN.[In] as Tijd, Count(Planning_1.ID) AS AantalVanID
FROM DistinctIN RIGHT JOIN Planning AS Planning_1 ON DistinctIN.Datum = Planning_1.Datum
WHERE (((Planning_1.[In])<=[DistinctIN].[In]) AND ((DistinctIN.Datum)=[forms]![Afspraken]![datum]) AND ((Planning_1.[Uit])>[DistinctIN].[In]) AND ((Planning_1.[Wacht?])=True))
GROUP BY DistinctIN.Datum, DistinctIN.[In]
ORDER BY DistinctIN.[In]);

This query serves its use except that I occassionally get double rows like, e.g.:
Datum Tijd AantalVanID
17/09/2002 9:00:00 7
17/09/2002 9:00:00 8

How can I adjust the SQL query so it only takes the row with the lowest 'AantalVanID' if date and time are equal...?


Alternatively, I could first do the union so oI get distinct date/time rows and then calculate the 'AantalVanID' but I don't know how to do a right join between a union and a table
It would have to be something like:

SELECT Datum, Data.[In] as Tijd, Count(Plan.ID) AS AantalVanID FROM (((SELECT * FROM DistinctIN) UNION (SELECT * FROM DistinctUIT)) AS Data RIGHT JOIN Planning AS Plan ON Data.Datum=Planning.Datum) WHERE (Plan.[In]<=Data.[In] AND Plan.[Uit]>Data.[In] AND Plan.[Wacht?]=True)

... however this query don't work (invalid syntax on UNION)

 

[Jon K]

Since your query serves its use except for the double rows, you can create a new query based on your query to retrieve the rows with the minimum value of AantalVanID in each group of Datum & Tijd.


Assuming both Datum and Tijd are date/time fields, you can try this query (type/paste in the SQL View of a new query, replacing with the correct name of your query in two instances):-

SELECT *
FROM yourQuery AS a
WHERE AantalVanID = (Select Min(AantalVanID) from yourQuery where Datum=a.Datum and Tijd=a.Tijd);


Or try this query:-

SELECT *
FROM yourQuery AS a
WHERE AantalVanID = DMin("AantalVanID", "yourQuery", "Datum=#" & a.Datum & "# and Tijd=#" & a.Tijd & "#");


The first query uses a subquery. A subquery is optimised and should be your first choice, though the subquery might fail if Access finds your original query too complicated.

The second query uses the inefficient DMin() function but it generally works.


Hope this helps.

 

[L4serK!LL]

Works like a charm, thx!