Hello,
I have one simple query joining two tables with left outer join on 3
fields and using MIN on two fields. These two tables have lot of data
about 3 mil in total. I am trying to migrate db from MS Access to SQL
2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS
Access.

SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.
[STATE]), T1.COUNT


FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND
T1.[STATE] = T2.[STATE]


WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING'


GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT


HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-'


I have about 30 queries to migrate and I am sort of stuck. Does any
one have any idea ?

Many thanks,
JB

dsdevonsomer (AT) gmail (DOT) com wrote in news:c624da44-43eb-4289-a24c-
7ee0a825626e (AT) q78g2000hsh (DOT) googlegroups.com:

This is my guess.

In my experience T-SQL and JET SQL are very different. I never try to
convert a JET query; I start all over.

From T-SQL documentation:

"When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name
= NULL returns zero rows even if there are null values in column_name. A
SELECT statement that uses WHERE column_name <> NULL returns zero rows even
if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison
operators do not follow the SQL-92 standard. A SELECT statement that uses
WHERE column_name = NULL returns the rows that have null values in
column_name. A SELECT statement that uses WHERE column_name <> NULL returns
the rows that have nonnull values in the column. Also, a SELECT statement
that uses WHERE column_name <> XYZ_value returns all rows that are not
XYZ_value and that are not NULL."

TTBOMR Access/JET would not conform to the last, ie, all rows that are not
XYZ_value or are NULL would be returned.

I will guess that the problem is in the Join of the 2 tables. Jet sql
seems to handle joins a little bit differently than tsql. I think Jet
is a little more forgiving.

One thing you could try is some straight comparisons between the tables
like

in Jet (in Access) try this:

Select T1.Mon From T1 Join T2 On T1.Mon = T2.Mon

then try the same thing query in Tsql (query analyzer) and see if you
get the same results. Then try it for your other join fields.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On 20 Feb, 19:42, dsdevonso... (AT) gmail (DOT) com wrote:
I had an issue on some machines where Access LEFT OUTER joins where
doing the same as INNER joins I had to upgrade the version of jet on
those machines. There was no error message.

can your fields like OCD contain NULLS, before changing config options
ANSI_NULLS etc etc.

try if its ACCEPTABLE to your query to cope with them..

e.g. in SQL Server do MIN(ISNULL(OCD,0))

or in Access MIN(NZ(OCD,0))

and see what you get - should be a quick and easy test. I cannot
remember but certain aggregate funcs in SQL omit null values depending
on config options..

On Feb 21, 8:39 am, Yitzak <terrysha... (AT) yahoo (DOT) co.uk> wrote:

Hello everyone,
I had nulls in few fields, but I had specified same criteria in both
sql and access. Even with specifying conditions to take care of NULLs
in Access, the two queries gave me different results. So after I tried
all the options and no success, I started from scratch as IYLE
suggested, 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.

Many thanks,
JB