this sql command make me sick

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

enzo - 02 Jun 2006 22:26 GMT

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

Reply to this Message

salad - 02 Jun 2006 22:34 GMT

> hello everyone...
>
[quoted text clipped - 35 lines]
> 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.

Reply to this Message

Bob Quintal - 02 Jun 2006 23:30 GMT

>> hello everyone...
>>
[quoted text clipped - 48 lines]
> 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);

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message