How to avoid duplicates in an union query

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
 
B

Bob Barrows

Marc said:
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



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
Click to expand...
You need to group by the fields you consider to be duplicated, and
aggregate the rest of the fields.
 
M

Marc

Hi Bob

Seems not to work, maybe I do something wrong!!

How to group this query ?? on PersId

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#);




Marc
 
B

Bob Barrows

Marc said:
Hi Bob

Seems not to work, maybe I do something wrong!!

How to group this query ?? on PersId

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#);
Click to expand...
Save the original union query, giving it a name.
Create a new query, using the saved union query as the data source. Do
the grouping and aggregation there.
 
M

Marc

Hi Bob

I know, But this is just the problem...
The query is a sql querystring in a loop inside my vba code (loop of 7 days)
I am loocking to do this with one query if possible

Many thanks for responding
Marc.



Marc said:
Hi Bob

Seems not to work, maybe I do something wrong!!

How to group this query ?? on PersId

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#);




Marc


Bob Barrows said:
You need to group by the fields you consider to be duplicated, and
aggregate the rest of the fields.
Click to expand...
Click to expand...
 
B

Bob Barrows

Why would you not run a saved query in that loop instead of that tedious
business of building sql strings? Saved parameter queries are very
powerful and easier to use than you would think.

However, if you really can't do that, then use a subquery for the union
query instead of the saved query.
select <grouping fields>,<aggregated fields> from (<union query>) as q
group by <grouping fields>

Hi Bob

I know, But this is just the problem...
The query is a sql querystring in a loop inside my vba code (loop of
7 days) I am loocking to do this with one query if possible

Many thanks for responding
Marc.



Marc said:
Hi Bob

Seems not to work, maybe I do something wrong!!

How to group this query ?? on PersId

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#);




Marc


Bob Barrows said:
Marc wrote:
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



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


You need to group by the fields you consider to be duplicated, and
aggregate the rest of the fields.
Click to expand...
Click to expand...
Click to expand...
 
M

Marc

Hi

Here the explanation:
I have 2 forms "frmPlanner" and "frmPlannerSub"

Mainform namend "frmPlanner" and 7 * frmPlannerSub the subforms named as
"frmDag1" ; "frmDag1" ..... to "frmDag7" returns a full week
In these 7 subforms "frmPlannerSub" is a combobox "cboPersoneel" that need
the data from the query below

On the form there are 2 buttons to move forward and backward day by day so
it is like scrolling days + or days -
everytime the query need to get the avalaible and absent employees.

so you see, its impossible to use a saved query for this case.

Many thanks in advance
Marc.







Bob Barrows said:
Why would you not run a saved query in that loop instead of that tedious
business of building sql strings? Saved parameter queries are very
powerful and easier to use than you would think.

However, if you really can't do that, then use a subquery for the union
query instead of the saved query.
select <grouping fields>,<aggregated fields> from (<union query>) as q
group by <grouping fields>

Hi Bob

I know, But this is just the problem...
The query is a sql querystring in a loop inside my vba code (loop of
7 days) I am loocking to do this with one query if possible

Many thanks for responding
Marc.



Marc said:
Hi Bob

Seems not to work, maybe I do something wrong!!

How to group this query ?? on PersId

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#);




Marc


"Bob Barrows" <[email protected]> schreef in bericht
Marc wrote:
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



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


You need to group by the fields you consider to be duplicated, and
aggregate the rest of the fields.
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barrows

Marc said:
Hi

Here the explanation:
I have 2 forms "frmPlanner" and "frmPlannerSub"

Mainform namend "frmPlanner" and 7 * frmPlannerSub the subforms
named as "frmDag1" ; "frmDag1" ..... to "frmDag7" returns a full
week
In these 7 subforms "frmPlannerSub" is a combobox "cboPersoneel"
that need the data from the query below

On the form there are 2 buttons to move forward and backward day by
day so it is like scrolling days + or days -
everytime the query need to get the avalaible and absent employees.

so you see, its impossible to use a saved query for this case.
Click to expand...

I disagree (think "saved parameter query"), but do what you want to do.
 
M

Marc

Hi Bob

It OK now

The solution
In the loop i create each time a new query by code (after deleting the
previous query)
and by grouping in the second query i get the correct data to use on each of
the 7 comboboxes.
Its working perfect now.....

Thanks for your idea's to get the solution
Marc.
 
Top