Why Same query results in two different # in SQL Server vs MS Access

[dsdevonsomer]

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

[Yitzak]

On 20 Feb, 19:42, [e-mail] wrote:

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

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..

[dsdevonsomer]

On Feb 21, 8:39 am, Yitzak <[e-mail]:

On 20 Feb, 19:42, [e-mail] wrote:

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

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..

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