this sql command make me sick

[enzo]

hello everyone...

after several days trying to find the right command.. i have to ask you

people...
this is the deal:
i have two tables related...
the first one is the master, the second the slave...
the first has fields like code, name, department and periodicity of
payment...
the second has the payment data... code, date of payment, amount to
pay...
not every one in the first table has the payment in the same month...
because a few people receives its pay one time in the month, and other
receives payment every two or three months (periodicity of payment)...
my trouble is when i want to see all the payments in one month of the
people from one specific department, because if somebody doesn't get
his payment (different periodicity) i have to see the code and name of
the worker AND the date and amount of payment in blank (in that
case)...
i thing that i must to be a simple LEFT JOIN but it doesn't work in
Access (i have a web asp page with and access database in this case)...

i don't know what to do.. please.. if somebody has some ideas.. i need
it...
thanks..
Enzo..
ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.departm)=53) AND ((table2.date)=#2/15/2006#));

[salad]

enzo wrote:

hello everyone...

after several days trying to find the right command.. i have to ask you

people...
this is the deal:
i have two tables related...
the first one is the master, the second the slave...
the first has fields like code, name, department and periodicity of
payment...
the second has the payment data... code, date of payment, amount to
pay...
not every one in the first table has the payment in the same month...
because a few people receives its pay one time in the month, and other
receives payment every two or three months (periodicity of payment)...
my trouble is when i want to see all the payments in one month of the
people from one specific department, because if somebody doesn't get
his payment (different periodicity) i have to see the code and name of
the worker AND the date and amount of payment in blank (in that
case)...
i thing that i must to be a simple LEFT JOIN but it doesn't work in
Access (i have a web asp page with and access database in this case)...

i don't know what to do.. please.. if somebody has some ideas.. i need
it...
thanks..
Enzo..
ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.departm)=53) AND ((table2.date)=#2/15/2006#));

Is Table2.Date a field that contains date stuffed with Date or does it
contain both date and time stuffed with Now(). If Now'd, you'd need to
check between 2/15/2006 and 2/16/2006. A field with mm/dd/yyyy only has
a time of 00:00:00. You'd only pull in records that were Now'd at
midnight exactly.

If that's not the case...maybe someone else has another solution.

[Bob Quintal]

salad <oi*@vinegar.com> wrote in
news:K9**************@newsread3.news.pas.earthlink .net:

enzo wrote:

hello everyone...

after several days trying to find the right command.. i have
to ask you

people...
this is the deal:
i have two tables related...
the first one is the master, the second the slave...
the first has fields like code, name, department and
periodicity of payment...
the second has the payment data... code, date of payment,
amount to pay...
not every one in the first table has the payment in the same
month... because a few people receives its pay one time in
the month, and other receives payment every two or three
months (periodicity of payment)...
my trouble is when i want to see all the payments in one
month of the people from one specific department, because if
somebody doesn't get his payment (different periodicity) i
have to see the code and name of the worker AND the date and
amount of payment in blank (in that case)...
i thing that i must to be a simple LEFT JOIN but it doesn't
work in Access (i have a web asp page with and access
database in this case)...

i don't know what to do.. please.. if somebody has some
ideas.. i need it...
thanks..
Enzo..
ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.departm)=53) AND
((table2.date)=#2/15/2006#));

Is Table2.Date a field that contains date stuffed with Date or
does it contain both date and time stuffed with Now(). If
Now'd, you'd need to check between 2/15/2006 and 2/16/2006. A
field with mm/dd/yyyy only has a time of 00:00:00. You'd only
pull in records that were Now'd at midnight exactly.

If that's not the case...maybe someone else has another
solution.

When you do a left join, you must modify any where clause to
accept a null value in addition to the value you are trying to
match.

WHERE (((table1.departm)=53) AND
((table2.date)=#2/15/2006#) OR Table2.date is null);

--
Bob Quintal

PA is y I've altered my email address.