Access -> SQL Server SQL Problem

Access -> SQL Server SQL Problem

Post by Douglas McArthu » Fri, 17 Aug 2001 17:16:07



I've inherited an Access 2000 system which I'm trying to
migrate to SQL Server 2000. The following SQL returns
different results from the query operating on the Access
database. Can anyone help, please (sorry about the
formatting)?

SELECT TOP 100 PERCENT Supplier_1.[Supplier Name],
Supplier_1.[Supplier Id] AS [SubContractor Id], [Stock for
Component].[Component Id], SUM([Stock for
Component].Quantity * [Stock Type]. [External Stock]) AS
[External Stock Qty], SUM([Stock for Component].Quantity *
[Stock Type].Allocated) AS [Alllocated Stock Qty],
Component.Description, Component.[Part Number], Supplier_1.
[Supplier Name] AS Supplier, SUM([Stock Type].[External
Stock]) AS [SumOfExternal Stock]

FROM Component INNER JOIN Supplier Supplier_1 ON Component.
[Supplier Id] = Supplier_1.[Supplier Id] INNER JOIN [Stock
Type] INNER JOIN [Stock for Component] INNER JOIN Supplier
Supplier_2 ON [Stock for Component].Supplier = Supplier_2.
[Supplier Id] ON [Stock Type].[Type Id] = [Stock for
Component].Type ON Component.[Component Id] = [Stock for
Component].[Component Id]

GROUP BY Supplier_1.[Supplier Name], Supplier_1.[Supplier
Id], [Stock for Component].[Component Id],
Component.Description, dbo.Component.[Part Number],
Supplier_1.[Supplier Name]

ORDER BY Supplier_1.[Supplier Name], [Stock for Component].
[Component Id]

 
 
 

Access -> SQL Server SQL Problem

Post by Mary Chipma » Fri, 17 Aug 2001 23:20:01


One difference between Access SQL and T-SQL is that with a TOP values
query, Access defaults to showing ties--SQLS does not unless you use
WITH TIES. However, in this case it shouldn't matter since you're
selecting top 100 pct. I'd suggest using the Query Analyzer to test
out your queries with a small amount of sample data--there's many
subtle and not-so-subtle differences between Access SQL and T-SQL, and
your Access queries aren't guaranteed to migrate cleanly.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Thu, 16 Aug 2001 01:16:07 -0700, "Douglas McArthur"


>I've inherited an Access 2000 system which I'm trying to
>migrate to SQL Server 2000. The following SQL returns
>different results from the query operating on the Access
>database. Can anyone help, please (sorry about the
>formatting)?

>SELECT TOP 100 PERCENT Supplier_1.[Supplier Name],
>Supplier_1.[Supplier Id] AS [SubContractor Id], [Stock for
>Component].[Component Id], SUM([Stock for
>Component].Quantity * [Stock Type]. [External Stock]) AS
>[External Stock Qty], SUM([Stock for Component].Quantity *
>[Stock Type].Allocated) AS [Alllocated Stock Qty],
>Component.Description, Component.[Part Number], Supplier_1.
>[Supplier Name] AS Supplier, SUM([Stock Type].[External
>Stock]) AS [SumOfExternal Stock]

>FROM Component INNER JOIN Supplier Supplier_1 ON Component.
>[Supplier Id] = Supplier_1.[Supplier Id] INNER JOIN [Stock
>Type] INNER JOIN [Stock for Component] INNER JOIN Supplier
>Supplier_2 ON [Stock for Component].Supplier = Supplier_2.
>[Supplier Id] ON [Stock Type].[Type Id] = [Stock for
>Component].Type ON Component.[Component Id] = [Stock for
>Component].[Component Id]

>GROUP BY Supplier_1.[Supplier Name], Supplier_1.[Supplier
>Id], [Stock for Component].[Component Id],
>Component.Description, dbo.Component.[Part Number],
>Supplier_1.[Supplier Name]

>ORDER BY Supplier_1.[Supplier Name], [Stock for Component].
>[Component Id]


 
 
 

Access -> SQL Server SQL Problem

Post by Ivan Arjentinsk » Sun, 19 Aug 2001 19:05:10


Douglas,

I would recommend rewriting the INNER JOIN clauses. Try putting the joins in
JOIN/ON pairs.
Like:
FROM TableA JOIN TableB ON ....
JOIN TableC ON ....
JOIN TableD ON ...

--
Ivan Arjentinski

-----------------------------------------------------------
Checkout the SQL Upgrade Wizard -
upgrade production DB schema, while keeping the data.
www.aloeco.com/upgrader
-----------------------------------------------------------

Quote:> I've inherited an Access 2000 system which I'm trying to
> migrate to SQL Server 2000. The following SQL returns
> different results from the query operating on the Access
> database. Can anyone help, please (sorry about the
> formatting)?

> SELECT TOP 100 PERCENT Supplier_1.[Supplier Name],
> Supplier_1.[Supplier Id] AS [SubContractor Id], [Stock for
> Component].[Component Id], SUM([Stock for
> Component].Quantity * [Stock Type]. [External Stock]) AS
> [External Stock Qty], SUM([Stock for Component].Quantity *
> [Stock Type].Allocated) AS [Alllocated Stock Qty],
> Component.Description, Component.[Part Number], Supplier_1.
> [Supplier Name] AS Supplier, SUM([Stock Type].[External
> Stock]) AS [SumOfExternal Stock]

> FROM Component INNER JOIN Supplier Supplier_1 ON Component.
> [Supplier Id] = Supplier_1.[Supplier Id] INNER JOIN [Stock
> Type] INNER JOIN [Stock for Component] INNER JOIN Supplier
> Supplier_2 ON [Stock for Component].Supplier = Supplier_2.
> [Supplier Id] ON [Stock Type].[Type Id] = [Stock for
> Component].Type ON Component.[Component Id] = [Stock for
> Component].[Component Id]

> GROUP BY Supplier_1.[Supplier Name], Supplier_1.[Supplier
> Id], [Stock for Component].[Component Id],
> Component.Description, dbo.Component.[Part Number],
> Supplier_1.[Supplier Name]

> ORDER BY Supplier_1.[Supplier Name], [Stock for Component].
> [Component Id]