Help with query please!

  • Thread starter Thread starter info
  • Start date Start date
I

info

SELECT tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
Sum(tblUurStaat.UurStaat_aantalUur) AS SomVanUurStaat_aantalUur
FROM tblStoffeerder LEFT JOIN tblUurStaat ON tblStoffeerder.PersId =
tblUurStaat.PersRef
GROUP BY tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
Year([UurStaat_datum]), weeknum([UurStaat_datum]),
tblStoffeerder.Pers_naam, tblStoffeerder.IsVervallen
HAVING (((tblStoffeerder.WerkmyRef)=[Forms]![frmUrenStaat_2]!
[kader_WM]) AND ((Year([UurStaat_datum]))=[Forms]![frmUrenStaat_2]!
[lngJaar]) AND ((weeknum([UurStaat_datum]))=[Forms]![frmUrenStaat_2]!
[weeknr]) AND ((tblStoffeerder.IsVervallen)=False))
ORDER BY tblStoffeerder.WerkmyRef, tblStoffeerder.Pers_naam;

The problem is: not every record in tblStoffeerder is showed. when
there is no record in tblUurStaat I want to show the query:
Name Hours
Gerard 0
Jan 38
.....

Does anybody know to solve this?
Thanx in advance,

Gerard Krijgsman
 
You are using an outer join query (good!), but your criteria has the effect
of excluding the records that don't match. The result is therefore the same
as an inner join.

For the criteria under the fields from tblUUrStatt, add:
Or Is Null
 
Thanx for ur reaction.
maybe I misunderstood you but the results are not good yet.

SELECT tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
Sum(tblUurStaat.UurStaat_aantalUur) AS SomVanUurStaat_aantalUur
FROM tblStoffeerder LEFT JOIN tblUurStaat ON tblStoffeerder.PersId =
tblUurStaat.PersRef
GROUP BY tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
Year([UurStaat_datum]), weeknum([UurStaat_datum]),
tblStoffeerder.Pers_naam, tblStoffeerder.IsVervallen
HAVING (((tblStoffeerder.WerkmyRef)=[Forms]![frmUrenStaat_2]!
[kader_WM]) AND ((Year([UurStaat_datum]))=[Forms]![frmUrenStaat_2]!
[lngJaar] Or (Year([UurStaat_datum])) Is Null) AND
((weeknum([UurStaat_datum]))=[Forms]![frmUrenStaat_2]![weeknr] Or
(weeknum([UurStaat_datum])) Is Null) AND
((tblStoffeerder.IsVervallen)=False))
ORDER BY tblStoffeerder.WerkmyRef, tblStoffeerder.Pers_naam;
 
Pardon me, but I don't think that will work correctly unless there is a
record in tblUurStaat that is related to tblStoffEerder and has a null value
for the field(s) in question OR if there is NO related record in tblUurStat
for a particular tblStoffEerder.PersID. I tested this against one of my
databases with a similar structure and the results I get were:

If a person had activities during the time frame then the person showed up
in the results.
If a person had no associated activities ever then the person showed up in
the results.
If a person had activities but not during the time frame then the person did
not show up in the results.
If a person had an activity with no datetime (null) entered then the person
showed up in the results.

The only way I know to reliably do this is to use nested queries or
subqueries in the from clause. The problem with subqueries in the from
clause is that Access will not allow square brackets within the subquery.
All that said the following may work for the poster.

SELECT tblStoffeerder.PersId
, tblStoffeerder.Pers_naam
, tblStoffeerder.AantalUur
, tblStoffeerder.WerkmyRef
, Sum(T.UurStaat_aantalUur) AS SomVanUurStaat_aantalUur

FROM tblStoffeerder LEFT JOIN
(SELECT UurStaat_anntalUUR
, PersRef
FROM tblUurStaat
WHERE Year(UurStaat_Datum) = Forms!frmUrenStaat_2!lngJaar
AND WeekNum(UurStaat_Datum) = Forms!frmUrenStaat_2!weeknr
) as T
ON tblStoffeerder.PersId = T.PersRef

WHERE tblStoffeerder.WerkmyRef=[Forms]![frmUrenStaat_2]![kader_WM]
AND tblStoffeerder.IsVervallen=False

GROUP BY tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
tblStoffeerder.Pers_naam, tblStoffeerder.IsVervallen

ORDER BY tblStoffeerder.WerkmyRef, tblStoffeerder.Pers_naam;

The other option is to create a saved query that gets the sum of
UurStaat_anntalUur and the PersRef for the desired period and then use that
in a left join to the tblSoffeerder table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Allen Browne said:
You are using an outer join query (good!), but your criteria has the
effect of excluding the records that don't match. The result is therefore
the same as an inner join.

For the criteria under the fields from tblUUrStatt, add:
Or Is Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SELECT tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
Sum(tblUurStaat.UurStaat_aantalUur) AS SomVanUurStaat_aantalUur
FROM tblStoffeerder LEFT JOIN tblUurStaat ON tblStoffeerder.PersId =
tblUurStaat.PersRef
GROUP BY tblStoffeerder.PersId, tblStoffeerder.Pers_naam,
tblStoffeerder.AantalUur, tblStoffeerder.WerkmyRef,
Year([UurStaat_datum]), weeknum([UurStaat_datum]),
tblStoffeerder.Pers_naam, tblStoffeerder.IsVervallen
HAVING (((tblStoffeerder.WerkmyRef)=[Forms]![frmUrenStaat_2]!
[kader_WM]) AND ((Year([UurStaat_datum]))=[Forms]![frmUrenStaat_2]!
[lngJaar]) AND ((weeknum([UurStaat_datum]))=[Forms]![frmUrenStaat_2]!
[weeknr]) AND ((tblStoffeerder.IsVervallen)=False))
ORDER BY tblStoffeerder.WerkmyRef, tblStoffeerder.Pers_naam;

The problem is: not every record in tblStoffeerder is showed. when
there is no record in tblUurStaat I want to show the query:
Name Hours
Gerard 0
Jan 38
....

Does anybody know to solve this?
Thanx in advance,

Gerard Krijgsman
Click to expand...
Click to expand...
 
Back
Top