On Feb 21, 5:55 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 21 Feb 2008 10:10:22 -0800 (PST), dsdevonso...@gmail.com wrote:
>
> (snip)
>
> >Hugo, to your question, I have some null values in both tables for
> >MON and Reasons. Total of about 5000 rows between both tables are
> >having null values.
>
> >Is that what causing Access to return more results vs SQL Server ?
>
> Hi JB,
>
> I do not *know* that, but it was my suspicion. That's why I asked.
>
> In standard SQL, a row with Reason equal to NULL should be excluded with
> "WHERE Reason = 'Something'" (this is quote intuitive), but it should
> also be excluded with "WHERE Reason <> 'Something'" (which a lot of
> people happen to find very unintuitive). SQL Server works like that,
> unless you choose to use some compatibility settings to mimic behaviour
> of a much older version (which you shouldn't).
>
> I know little of Jet SQL (what Access uses), but I do know that this
> implementation of SQL is a lot further removed from the standard than
> SQL Server. So maybe, just maybe, the Access team decided to go for what
> many people consider to be intuitive rather than do it right - and if
> that's the case, it might explain the different row counts.
>
> > I
> >tried including the clause " MON IS NOT NULL " and "Reason IS NOT
> >NULL" but Access still returns the same number of rows. ? Should I be
> >doing something different ?
>
> Hmmm, that suggests that Access was already filtering these rows out (as
> it should). So unless you are actually using that backward compatibility
> setting in SQL Server, the problem has to lie somewhere else.
>
> I'm afraid it's time for you to start the old job of adding filters in
> order to narrow down the result set bit by bit until you get an amount
> of rows small enough for manual inspection that still displays the
> inconsistency. Once you know which rows are causing the problem, it's
> much easier to find the reason!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Hello everyone,
I tried all the options and after no success, I started from scratch
with one by one column in both sql and ms access and I am now getting
similar results. This was a weird exercise as still I am not sure,
what could have been wrong.

But I want to thank everyone who helped me understand little better
sql concepts.
thanks,
JB