MS Access To Sql Error

  • I do not use the wizard.

    If you do it manually and you know what you are doing you can do it quickly,you know what you are doing and you can clean up the flaws in the DB, etc.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I would suggest that you look into the JOIN Statement.

    SELECT tbl_PI_Data.Date, tbl_PI_Data.Assoc_Name, tbl_PI_Associates.MgrName, tbl_PI_Data.Func_Name, tbl_PI_Data.Total_Proc, tbl_PI_Data.Prod_Hrs, tbl_PI_Data.Paid_Hrs, [Total_Proc]/[Prod_Hrs] AS PPH, tbl_PI_Functions.PPH_Goal, [Prod_Hrs]/[Paid_Hrs] AS PTP, tbl_PI_Functions.PTP_Goal

    FROM tbl_PI_Functions INNER JOIN (tbl_PI_Associates INNER JOIN tbl_PI_Data ON tbl_PI_Associates.Assoc_Name = tbl_PI_Data.Assoc_Name) ON tbl_PI_Functions.Func_Name = tbl_PI_Data.Func_Name

    Try using aliases as I suggested. It lends itself to cleaner code.

    This is a start of the stored procedure ut you need to incorporate the other tables. Take a stab at it...

    No need for the brackets unless you use a reserved word or have spaces.

    If you create an alias as I suggested your code will be a lot cleaner when you migrate it.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Your data does not match so that even after constructing a query with valid joins you will not return any results.

    The func_name column in the tbl_PI_Functions Table does not match the func_name column in the tbl_PI_Data Table.

    The Assoc_Name in the tbl_PI_Associates Table does not match the Assoc_Name tbl_PI_Data Table.

    The Wizard to to convert to Access to horrible. For example, take a look at your Data Types. THe columns that should be defined as the Primary & Foriegn Keys are NVarChar(50).

    I use SSIS (or DTS in the past) and change the Data Types and change the names of columns with reserved words, spaces, etc.

    What is the purpose of the ID Column in the tbl_PI_Data Table? That should be the Primary Key the sample Data that you provided does not fil that model.

    Where are your Constarints and Indexes?

    You should update your data and when the data maches if you change the JOIN to an INNER you will not get any Nulls.

    SELECT PiData.Date, PiData.Assoc_Name, PiAssoc.MgrName, PiData.Func_Name, PiData.Total_Proc,

    PiData.Prod_Hrs, PiData.Paid_Hrs, Total_Proc/Prod_Hrs AS PPH, PiFunc.PPH_Goal,

    PiData.Prod_Hrs/PiData.Paid_Hrs AS PTP, PiFunc.PTP_Goal

    FROM tbl_PI_Functions AS PiFunc

    FULL OUTER JOIN tbl_PI_Data AS PiData ON PiFunc.Func_Name = PiData.Func_Name

    FULL OUTER JOIN tbl_PI_Associates PiAssoc ON PiData.Assoc_Name = PiAssoc.Assoc_Name

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Check and update your Data:

    SELECT PiData.Date, PiData.Assoc_Name, PiAssoc.MgrName, PiData.Func_Name, PiData.Total_Proc,

    PiData.Prod_Hrs, PiData.Paid_Hrs, Total_Proc/Prod_Hrs AS PPH, PiFunc.PPH_Goal,

    PiData.Prod_Hrs/PiData.Paid_Hrs AS PTP, PiFunc.PTP_Goal

    FROM tbl_PI_Functions AS PiFunc

    FULL OUTER JOIN tbl_PI_Data AS PiData ON PiFunc.Func_Name = PiData.Func_Name

    FULL OUTER JOIN tbl_PI_Associates PiAssoc ON PiData.Assoc_Name = PiAssoc.Assoc_Name

    Create your Stored Procedure:

    CREATE PROCEDURE AssocHours (@StartDate Datetime, @EndDate DateTime)

    AS

    SELECT PiData.Func_Name,PiAssoc.MgrName AS Manager, PiData.Assoc_Name AS Associate,

    SUM(PiData.Total_Proc) AS Processed,SUM(PiData.Prod_Hrs) AS Productive_Hrs,

    SUM(PiData.Paid_Hrs) AS Paid_Hrs

    FROM tbl_PI_Functions AS PiFunc

    INNER JOIN tbl_PI_Data AS PiData ON PiFunc.Func_Name = PiData.Func_Name

    INNER JOIN tbl_PI_Associates PiAssoc ON PiData.Assoc_Name = PiAssoc.Assoc_Name

    WHERE PiData.Date BETWEEN @StartDate AND @EndDate

    GROUP BY PiData.Func_Name,PiAssoc.MgrName, PiData.Assoc_Name

    Execute your SP:

    EXEC AssocHours @StartDate = '2008-01-01', @EndDate = '2008-12-31'

    Func_NameManagerAssociateProcessedProductive_HrsPaid_Hrs

    Preptest2Lisa Taylor03.99

    TestagainRony47.92

    -------------------------------------------------- (2 row(s) affected)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply