outer join not working

S

seeker

When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
 
K

KARL DEWEY

Try changing this ---
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

to this --
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21)) AND (((member_time.date)
Between #10/1/2009# And #10/30/2009#));
 
M

Marshall Barton

seeker said:
When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...


I dont' see anything obvious that would no match any
recordsm unless they all hace date of 10/30/2009.

Before going any deeper into this, you need to change you
use of Now() to Date()

Another thing is that you should not have that stuff in a
HAVING clause. Move it to the WHERE clause.

Do those two things and see what results you get.
 
S

seeker

the following error appears when below change is made;

"You tried to execute a query that does not include the specified expression
member_number as part of an aggregate function"

Thanks.

KARL DEWEY said:
Try changing this ---
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

to this --
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21)) AND (((member_time.date)
Between #10/1/2009# And #10/30/2009#));


--
Build a little, test a little.


seeker said:
When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...
Click to expand...
 
S

seeker

This query;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

produces this;

MemberNumber member_number Total Hours
276 276 19.75
469 0
612 0
1291 1291 7.5
1802 0
1858 0
1872 0
1896 1896 1041.71666666667
1897 0
1934 0
2038 0
2049 0
2057 0
2071 0
2110 0
2125 0
2145 2145 0.25
2155 0
2157 0
2159 0
2164 0
2190 2190 77
2194 0
2251 2251 57.25
2253 2253 665.75
2288 2288 312.75
2311 2311 57.75
2335 2335 44.5
2358 2358 80
2370 2370 13
2381 2381 0.25
2390 2390 5.25
2391 2391 4
2397 2397 4.75

seeker said:
the following error appears when below change is made;

"You tried to execute a query that does not include the specified expression
member_number as part of an aggregate function"

Thanks.

KARL DEWEY said:
Try changing this ---
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

to this --
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21)) AND (((member_time.date)
Between #10/1/2009# And #10/30/2009#));


--
Build a little, test a little.


seeker said:
When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...
Click to expand...
Click to expand...
 
S

seeker

when I add the date resulting in this query structure;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #11/1/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

this is the result;

MemberNumber member_number Total Hours
1896 1896 36.25
2288 2288 14.25
2311 2311 5
2335 2335 13.75
2370 2370 2.25
2390 2390 5
2391 2391 3.5
2397 2397 4.75

where are all the members with 0 time I should have 34 records in this
query also.

seeker said:
the following error appears when below change is made;

"You tried to execute a query that does not include the specified expression
member_number as part of an aggregate function"

Thanks.

KARL DEWEY said:
Try changing this ---
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

to this --
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21)) AND (((member_time.date)
Between #10/1/2009# And #10/30/2009#));


--
Build a little, test a little.


seeker said:
When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...
Click to expand...
Click to expand...
 
S

seeker

if i place a field from the left table (membernamesandaddress) the 34 records
are not effected but if I place a field in the query from the right table
(member_time) then it acts as if the outer join is not in existence. FYI

seeker said:
when I add the date resulting in this query structure;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #11/1/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

this is the result;

MemberNumber member_number Total Hours
1896 1896 36.25
2288 2288 14.25
2311 2311 5
2335 2335 13.75
2370 2370 2.25
2390 2390 5
2391 2391 3.5
2397 2397 4.75

where are all the members with 0 time I should have 34 records in this
query also.

seeker said:
the following error appears when below change is made;

"You tried to execute a query that does not include the specified expression
member_number as part of an aggregate function"

Thanks.

KARL DEWEY said:
Try changing this ---
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

to this --
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21)) AND (((member_time.date)
Between #10/1/2009# And #10/30/2009#));


--
Build a little, test a little.


:

When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try changing --
WHERE (((member_time.date) Between #10/1/2009# And #11/1/2009#))
to this --
WHERE (((member_time.date) Between #10/1/2009# And #11/1/2009#)) OR
member_time.date Is Null

--
Build a little, test a little.


seeker said:
when I add the date resulting in this query structure;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #11/1/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

this is the result;

MemberNumber member_number Total Hours
1896 1896 36.25
2288 2288 14.25
2311 2311 5
2335 2335 13.75
2370 2370 2.25
2390 2390 5
2391 2391 3.5
2397 2397 4.75

where are all the members with 0 time I should have 34 records in this
query also.

seeker said:
the following error appears when below change is made;

"You tried to execute a query that does not include the specified expression
member_number as part of an aggregate function"

Thanks.

KARL DEWEY said:
Try changing this ---
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

to this --
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21)) AND (((member_time.date)
Between #10/1/2009# And #10/30/2009#));


--
Build a little, test a little.


:

When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...
Click to expand...
Click to expand...
 
S

seeker

without date range;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((DateDiff("d",[dateofBirth],Date())/365)>=16 And
(DateDiff("d",[dateofBirth],Date())/365)<21))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number;
results are as they should be except for totals
MemberNumber member_number Total Hours
276 276 19.75
469 0
612 0
1291 1291 7.5
1802 0
1858 0
1872 0
1896 1896 1041.71666666667
1897 0
1934 0
2038 0
2049 0
2057 0
2071 0
2110 0
2125 0
2145 2145 0.25
2155 0
2157 0
2159 0
2164 0
2190 2190 77
2194 0
2251 2251 57.25
2253 2253 665.75
2288 2288 312.75
2311 2311 57.75
2335 2335 44.5
2358 2358 80
2370 2370 13
2381 2381 0.25
2390 2390 5.25
2391 2391 4
2397 2397 4.75

with date range;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((DateDiff("d",[dateofBirth],Date())/365)>=16 And
(DateDiff("d",[dateofBirth],Date())/365)<21) AND ((member_time.date) Between
#10/1/2009# And #10/31/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number;

MemberNumber member_number Total Hours
1896 1896 36.25
2288 2288 14.25
2311 2311 5
2335 2335 13.75
2370 2370 2.25
2390 2390 5
2391 2391 3.5
2397 2397 4.75

which does not give me members with zero hours. I need help digging deeper.
Thanks.
Marshall Barton said:
seeker said:
When I run the following query I get 34 records with many showing null in the
one member_number field just like it should be:

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));

but when I add the date parameter it no longer shows the 34. Why?

SELECT tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365 AS Age,
Sum(DateDiff("n",[time_in],[time_out]))/60 AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((member_time.date) Between #10/1/2009# And #10/30/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber,
DateDiff("d",[dateofBirth],Now())/365
HAVING (((DateDiff("d",[dateofBirth],Now())/365)>=16 And
(DateDiff("d",[dateofBirth],Now())/365)<21));
Click to expand...


I dont' see anything obvious that would no match any
recordsm unless they all hace date of 10/30/2009.

Before going any deeper into this, you need to change you
use of Now() to Date()

Another thing is that you should not have that stuff in a
HAVING clause. Move it to the WHERE clause.

Do those two things and see what results you get.
Click to expand...
 
M

Marshall Barton

seeker said:
without date range;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((DateDiff("d",[dateofBirth],Date())/365)>=16 And
(DateDiff("d",[dateofBirth],Date())/365)<21))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number;
results are as they should be except for totals
MemberNumber member_number Total Hours
276 276 19.75
469 0
612 0
1291 1291 7.5
1802 0
1858 0
1872 0
1896 1896 1041.71666666667
1897 0
1934 0
2038 0
2049 0
2057 0
2071 0
2110 0
2125 0
2145 2145 0.25
2155 0
2157 0
2159 0
2164 0
2190 2190 77
2194 0
2251 2251 57.25
2253 2253 665.75
2288 2288 312.75
2311 2311 57.75
2335 2335 44.5
2358 2358 80
2370 2370 13
2381 2381 0.25
2390 2390 5.25
2391 2391 4
2397 2397 4.75

with date range;

SELECT tblMemberNameandAddress.MemberNumber, member_time.member_number,
nz(Sum(DateDiff("n",[time_in],[time_out]))/60,0) AS [Total Hours]
FROM tblMemberNameandAddress LEFT JOIN member_time ON
tblMemberNameandAddress.MemberNumber = member_time.member_number
WHERE (((DateDiff("d",[dateofBirth],Date())/365)>=16 And
(DateDiff("d",[dateofBirth],Date())/365)<21) AND ((member_time.date) Between
#10/1/2009# And #10/31/2009#))
GROUP BY tblMemberNameandAddress.MemberNumber, member_time.member_number;

MemberNumber member_number Total Hours
1896 1896 36.25
2288 2288 14.25
2311 2311 5
2335 2335 13.75
2370 2370 2.25
2390 2390 5
2391 2391 3.5
2397 2397 4.75

which does not give me members with zero hours. I need help digging deeper.
Click to expand...


The members that have no records in the member_time table do
not have a record in the date range. If you want the date
range condition to succeed when there is no matching record,
then you need to explicitly allow for that case with
something like:

WHERE (DateDiff("d",dateofBirth,Date())/365>=16 And
DateDiff("d",dateofBirth,Date())/365<21)
AND ((member_time.date Between #10/1/2009# And #10/31/2009#)
OR (member_time.member_number Is Null))

BTW, your age check can be off by a day or two. For an
precise calculation see:
http://www.mvps.org/access/datetime/date0001.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

functions in queries 1
Access Not Showing Up Records for Rt Join or Inner Join 2
calculate time to complete application 8
Calculating time difference using working days and working times 6
Help w/ SUMIF ; INDEX 2
Help with SUMIF, INDEX, LOOKUP Please !! 13
HELP! Crosstab Query by Week 2
Saving Fax Console to reinstall after a full wipe restore 1

Top