Help for an complex querry

I have 2 tables:

tblPersoneel: with standard LijnNr
PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

tblPlanningLijn With changed LijnNr on particulary date.
ProdDatum PersId LijnNr
18/09/2011 3 | 4
19/09/2011 3 | 1

I want next data from an query:
Query7If PersID has no date in tblPlanning
query must show a view of all PersID and PersNaam from tblPersoneel.

If by example selection on date 18/09/2011
query must show all PersId and PersNaam on that date from table tblPlanninglijn and + the other PersID and PersNaam from tblPersoneel.

so to have the correct lijnNr on date of 18/09/2011 and standard lijnnr from the other PersNaam from table tblPersoneel.

The results must always be 9 records with the richt LijnNr.

Many thanks in advance

Scotty

You need an outer join to show all the records from one table.
Right click on the join and set the join type.

Welcome to the forum.

Hi,
Thanks for your answer, but outer join does not give the wright solution

If selected date = 18/09/2011

The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2 > 18/09/2011 > 3 Werknemer03 | 4
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

if no date corresponding like 17/09/2011
The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

Scotty said:

Hi,
Thanks for your answer, but outer join does not give the wright solution

If selected date = 18/09/2011

The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2 > 18/09/2011 > 3 Werknemer03 | 4
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

if no date corresponding like 17/09/2011
The result must be:

PersID PersNaam LijnNr
1 Werknemer01 | 1
2 Werknemer02 | 1
3 Werknemer03 | 2
4 Werknemer04 | 2
5 Werknemer05 | 3
6 Werknemer06 | 3
7 Werknemer07 | 3
8 Werknemer08 | 4
9 Werknemer09 | 4

Click to expand...

If I understand the question correctly, this is the SQL you are looking for.

GalaxiomAtHome said:

If I understand the question correctly, this is the SQL you are looking for.

Code:

SELECT A.PersID, A.PersNaam, IIF(B.LijnNr Is Null, A.LijnNr, B.LijnNr)
FROM tblPersoneel AS A
LEFT OUTER JOIN 
         (
          SELECT PersID, LijnNr
          FROM tblPlanningLijn
          WHERE ProdDatum = [Enter Selected Date]
         ) AS B
ON A.PersID = B.PersID
ORDER BY A.PersID

Click to expand...

Thanks... is working Perfect