By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,970 Members | 1,668 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,970 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Feb 20 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 20 Feb, 19:42, dsdevonso...@gmail.com 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..
Feb 21 '08 #2

P: n/a
On Feb 21, 8:39 am, Yitzak <terrysha...@yahoo.co.ukwrote:
On 20 Feb, 19:42, dsdevonso...@gmail.com 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
Feb 28 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.