locked
Conversion of access queries to SQL RRS feed

  • Question

  • Hi,

    I need some help in converting access queries to sql queries.

    Here is my access querie

     

    SELECT dbo_CustomerMaster.CustomerCode, dbo_CustomerMaster.CustomerName, dbo_PartMaster.PartID, dbo_StyleMaster.StyleName, dbo_PartMaster.Description, dbo_DivisionMaster.DivisionName, dbo_ShipmentDetail.Quantity AS [Qty Shipped], "10" & dbo_SalesOrderHeader!SalesOrderID & "/" & dbo_ShipmentDetail!ShipmentID AS [Invoice No]

    FROM dbo_WarehouseMaster INNER JOIN ((dbo_CustomerMaster INNER JOIN ((((((dbo_SalesOrderDetail INNER JOIN dbo_SalesOrderHeader ON dbo_SalesOrderDetail.SalesOrderID = dbo_SalesOrderHeader.SalesOrderID) INNER JOIN dbo_ShipmentDetail ON dbo_SalesOrderDetail.SalesOrderDetailID = dbo_ShipmentDetail.SalesOrderDetailID) INNER JOIN dbo_ShipmentHeader ON dbo_ShipmentDetail.ShipmentID = dbo_ShipmentHeader.ShipmentID) INNER JOIN (dbo_PartMaster INNER JOIN dbo_PartStyle ON dbo_PartMaster.PartID = dbo_PartStyle.PartID) ON dbo_SalesOrderDetail.PartID = dbo_PartMaster.PartID) INNER JOIN dbo_StyleMaster ON dbo_PartStyle.StyleID = dbo_StyleMaster.StyleID) INNER JOIN dbo_PartNameMaster ON dbo_PartMaster.PartNameID = dbo_PartNameMaster.PartNameID) ON dbo_CustomerMaster.CustomerID = dbo_SalesOrderHeader.CustomerID) INNER JOIN dbo_DivisionMaster ON dbo_StyleMaster.DivisionID = dbo_DivisionMaster.DivisionID) ON dbo_WarehouseMaster.WarehouseID = dbo_SalesOrderHeader.WarehouseID

    GROUP BY dbo_CustomerMaster.CustomerCode, dbo_CustomerMaster.CustomerName, dbo_PartMaster.PartID, dbo_StyleMaster.StyleName, dbo_PartMaster.Description, dbo_DivisionMaster.DivisionName, dbo_ShipmentDetail.Quantity, "10" & dbo_SalesOrderHeader!SalesOrderID & "/" & dbo_ShipmentDetail!ShipmentID;

    Someone please help me.

    Thanks,

    Goutham


    gouthamraj
    • Moved by litdev Thursday, July 7, 2011 8:19 PM Not a SmallBasic question (From:Small Basic)
    Thursday, July 7, 2011 8:09 PM

Answers

  • Hi Goutham,

    You can use SSMA for Access to SQL Server for migrating Access Tables and Queries to SQL Server Tables and Views automatically with less manual work

    Please read the following articles for step by step instructions

    http://sqlxpertise.com/2011/04/27/migrating-access-databases-to-sql-azure/

    http://sqlxpertise.com/2011/04/30/migrating-access-databases-to-sql-server-denali/

    FYI, This tool comes with Free support as well from Microsoft

    Hope this helps !!!


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"
    • Proposed as answer by RaheelKhan Saturday, July 9, 2011 12:22 PM
    • Marked as answer by KJian_ Friday, July 15, 2011 8:49 AM
    Friday, July 8, 2011 5:31 PM
  • Please check following, assume that SalesOrderId and ShipmentId are INT.

    SELECT 
    	dbo_CustomerMaster.CustomerCode
    	,dbo_CustomerMaster.CustomerName
    	,dbo_PartMaster.PartID
    	,dbo_StyleMaster.StyleName
    	,dbo_PartMaster.Description
    	,dbo_DivisionMaster.DivisionName
    	,dbo_ShipmentDetail.Quantity AS [Qty Shipped]
    	,'10' + Convert(Varchar(20), dbo_SalesOrderHeader.SalesOrderID) + '/' + Convert(Varchar(20), dbo_ShipmentDetail.ShipmentID) AS [Invoice No]
    FROM 
    	dbo_WarehouseMaster 
    	INNER JOIN ((dbo_CustomerMaster INNER JOIN ((((((dbo_SalesOrderDetail INNER JOIN dbo_SalesOrderHeader ON dbo_SalesOrderDetail.SalesOrderID = dbo_SalesOrderHeader.SalesOrderID) INNER JOIN dbo_ShipmentDetail ON dbo_SalesOrderDetail.SalesOrderDetailID = dbo_ShipmentDetail.SalesOrderDetailID) INNER JOIN dbo_ShipmentHeader ON dbo_ShipmentDetail.ShipmentID = dbo_ShipmentHeader.ShipmentID) INNER JOIN (dbo_PartMaster INNER JOIN dbo_PartStyle ON dbo_PartMaster.PartID = dbo_PartStyle.PartID) ON dbo_SalesOrderDetail.PartID = dbo_PartMaster.PartID) INNER JOIN dbo_StyleMaster ON dbo_PartStyle.StyleID = dbo_StyleMaster.StyleID) INNER JOIN dbo_PartNameMaster ON dbo_PartMaster.PartNameID = dbo_PartNameMaster.PartNameID) ON dbo_CustomerMaster.CustomerID = dbo_SalesOrderHeader.CustomerID) INNER JOIN dbo_DivisionMaster ON dbo_StyleMaster.DivisionID = dbo_DivisionMaster.DivisionID) ON dbo_WarehouseMaster.WarehouseID = dbo_SalesOrderHeader.WarehouseID
    GROUP BY dbo_CustomerMaster.CustomerCode, dbo_CustomerMaster.CustomerName, dbo_PartMaster.PartID, dbo_StyleMaster.StyleName, dbo_PartMaster.Description, dbo_DivisionMaster.DivisionName, dbo_ShipmentDetail.Quantity, '10' + Convert(Varchar(20), dbo_SalesOrderHeader.SalesOrderID) + '/' + Convert(Varchar(20), dbo_ShipmentDetail.ShipmentID)
    
    


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by KJian_ Friday, July 15, 2011 8:49 AM
    Friday, July 8, 2011 9:56 PM

All replies

  • Hi Goutham,

    You can use SSMA for Access to SQL Server for migrating Access Tables and Queries to SQL Server Tables and Views automatically with less manual work

    Please read the following articles for step by step instructions

    http://sqlxpertise.com/2011/04/27/migrating-access-databases-to-sql-azure/

    http://sqlxpertise.com/2011/04/30/migrating-access-databases-to-sql-server-denali/

    FYI, This tool comes with Free support as well from Microsoft

    Hope this helps !!!


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"
    • Proposed as answer by RaheelKhan Saturday, July 9, 2011 12:22 PM
    • Marked as answer by KJian_ Friday, July 15, 2011 8:49 AM
    Friday, July 8, 2011 5:31 PM
  • Please check following, assume that SalesOrderId and ShipmentId are INT.

    SELECT 
    	dbo_CustomerMaster.CustomerCode
    	,dbo_CustomerMaster.CustomerName
    	,dbo_PartMaster.PartID
    	,dbo_StyleMaster.StyleName
    	,dbo_PartMaster.Description
    	,dbo_DivisionMaster.DivisionName
    	,dbo_ShipmentDetail.Quantity AS [Qty Shipped]
    	,'10' + Convert(Varchar(20), dbo_SalesOrderHeader.SalesOrderID) + '/' + Convert(Varchar(20), dbo_ShipmentDetail.ShipmentID) AS [Invoice No]
    FROM 
    	dbo_WarehouseMaster 
    	INNER JOIN ((dbo_CustomerMaster INNER JOIN ((((((dbo_SalesOrderDetail INNER JOIN dbo_SalesOrderHeader ON dbo_SalesOrderDetail.SalesOrderID = dbo_SalesOrderHeader.SalesOrderID) INNER JOIN dbo_ShipmentDetail ON dbo_SalesOrderDetail.SalesOrderDetailID = dbo_ShipmentDetail.SalesOrderDetailID) INNER JOIN dbo_ShipmentHeader ON dbo_ShipmentDetail.ShipmentID = dbo_ShipmentHeader.ShipmentID) INNER JOIN (dbo_PartMaster INNER JOIN dbo_PartStyle ON dbo_PartMaster.PartID = dbo_PartStyle.PartID) ON dbo_SalesOrderDetail.PartID = dbo_PartMaster.PartID) INNER JOIN dbo_StyleMaster ON dbo_PartStyle.StyleID = dbo_StyleMaster.StyleID) INNER JOIN dbo_PartNameMaster ON dbo_PartMaster.PartNameID = dbo_PartNameMaster.PartNameID) ON dbo_CustomerMaster.CustomerID = dbo_SalesOrderHeader.CustomerID) INNER JOIN dbo_DivisionMaster ON dbo_StyleMaster.DivisionID = dbo_DivisionMaster.DivisionID) ON dbo_WarehouseMaster.WarehouseID = dbo_SalesOrderHeader.WarehouseID
    GROUP BY dbo_CustomerMaster.CustomerCode, dbo_CustomerMaster.CustomerName, dbo_PartMaster.PartID, dbo_StyleMaster.StyleName, dbo_PartMaster.Description, dbo_DivisionMaster.DivisionName, dbo_ShipmentDetail.Quantity, '10' + Convert(Varchar(20), dbo_SalesOrderHeader.SalesOrderID) + '/' + Convert(Varchar(20), dbo_ShipmentDetail.ShipmentID)
    
    


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by KJian_ Friday, July 15, 2011 8:49 AM
    Friday, July 8, 2011 9:56 PM
  • Arbi,

    Thank you for the query.

    Raj


    gouthamraj
    Monday, July 11, 2011 3:10 PM
  • Thank you arunraj..

    Raj


    gouthamraj
    Monday, July 11, 2011 3:11 PM