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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • dsdevonsomer@gmail.com
    #1

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

    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
    Tags: None
    • Yitzak
      #2
      Re: Why Same query results in two different # in SQL Server vs MSAccess

      On 20 Feb, 19:42, dsdevonso...@gm ail.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..

        Comment

        • dsdevonsomer@gmail.com
          #3
          Re: Why Same query results in two different # in SQL Server vs MSAccess

          On Feb 21, 8:39 am, Yitzak <terrysha...@ya hoo.co.ukwrote:
          On 20 Feb, 19:42, dsdevonso...@gm ail.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

            Comment

            Previous template Next
            Working...