M
Marc
Hi,
I am using Next query in my vba code
Could someone help me to obtain the results in the second table with one
query.
How not to have duplicates like below
This is an union query from 2 tables
table1 tblPersoneel : table employees active without date of date
discharge "UitDienst = date discharge"
table2 tblJaarPlannerDates: dates and days holliday or absent
Datum1= start of absent / Dagen = says absent / Datum2 =
absent to tis date
SELECT DISTINCT tblPersoneel.PersId, [PersNaam] & " " & [PersVNaam] AS
Werkn,"" as Datum1,"" AS Dagen,"" as Datum2 ,"" AS Beschr ,
tblPersoneel.PersUitDienst as UitDienst FROM tblPersoneel
GROUP BY tblPersoneel.PersID, tblPersoneel.PersUitDienst, [PersNaam] & " " &
[PersVNaam]
HAVING (((tblPersoneel.PersUitDienst) Is Null));
UNION SELECT DISTINCT tblPersoneel.PersId, [PersNaam] & " " & [PersVNaam] AS
Werkn, tblJaarPlannerDates.AfwStartDat AS Datum1, tblJaarPlannerDates.ADagen
AS Dagen, [AfwStartDat]+[ADagen] AS Datum2 , tblKleuren.Beschrijving AS
Beschr, "" as UitDienst FROM (tblPersoneel LEFT JOIN tblJaarPlannerDates ON
tblPersoneel.PersId = tblJaarPlannerDates.PersId) LEFT JOIN tblKleuren ON
tblJaarPlannerDates.KleurNr = tblKleuren.KleurNr WHERE
(((tblJaarPlannerDates.AfwStartDat)<=#11/20/2008#)) AND
(([AfwStartDat]+[ADagen]) >=#11/20/2008#);
First table is the result of the query,
PersId Werkn Datum1 Dagen Datum2 Beschr UitDienst
1 Bertels Els
1 Bertels Els 17/11/2008 5 22/11/2008 Opleiding
3 Dewaele Sonja
4 Dehaene Jean-Luc
5 Speelmans Louise
6 Voets Jasper
Only I need 1 Werkn name like below
PersId Werkn Datum1 Dagen Datum2 Beschr UitDienst
1 Bertels Els 17/11/2008 5 22/11/2008 Opleiding
3 Dewaele Sonja
4 Dehaene Jean-Luc
5 Speelmans Louise
6 Voets Jasper
Hope someone can help me
Many thanks in advance
Marc
I am using Next query in my vba code
Could someone help me to obtain the results in the second table with one
query.
How not to have duplicates like below
This is an union query from 2 tables
table1 tblPersoneel : table employees active without date of date
discharge "UitDienst = date discharge"
table2 tblJaarPlannerDates: dates and days holliday or absent
Datum1= start of absent / Dagen = says absent / Datum2 =
absent to tis date
SELECT DISTINCT tblPersoneel.PersId, [PersNaam] & " " & [PersVNaam] AS
Werkn,"" as Datum1,"" AS Dagen,"" as Datum2 ,"" AS Beschr ,
tblPersoneel.PersUitDienst as UitDienst FROM tblPersoneel
GROUP BY tblPersoneel.PersID, tblPersoneel.PersUitDienst, [PersNaam] & " " &
[PersVNaam]
HAVING (((tblPersoneel.PersUitDienst) Is Null));
UNION SELECT DISTINCT tblPersoneel.PersId, [PersNaam] & " " & [PersVNaam] AS
Werkn, tblJaarPlannerDates.AfwStartDat AS Datum1, tblJaarPlannerDates.ADagen
AS Dagen, [AfwStartDat]+[ADagen] AS Datum2 , tblKleuren.Beschrijving AS
Beschr, "" as UitDienst FROM (tblPersoneel LEFT JOIN tblJaarPlannerDates ON
tblPersoneel.PersId = tblJaarPlannerDates.PersId) LEFT JOIN tblKleuren ON
tblJaarPlannerDates.KleurNr = tblKleuren.KleurNr WHERE
(((tblJaarPlannerDates.AfwStartDat)<=#11/20/2008#)) AND
(([AfwStartDat]+[ADagen]) >=#11/20/2008#);
First table is the result of the query,
PersId Werkn Datum1 Dagen Datum2 Beschr UitDienst
1 Bertels Els
1 Bertels Els 17/11/2008 5 22/11/2008 Opleiding
3 Dewaele Sonja
4 Dehaene Jean-Luc
5 Speelmans Louise
6 Voets Jasper
Only I need 1 Werkn name like below
PersId Werkn Datum1 Dagen Datum2 Beschr UitDienst
1 Bertels Els 17/11/2008 5 22/11/2008 Opleiding
3 Dewaele Sonja
4 Dehaene Jean-Luc
5 Speelmans Louise
6 Voets Jasper
Hope someone can help me
Many thanks in advance
Marc