Post reply

Access Queries conversion to Views problem

  • March 2, 2009 at 2:49 pm

    #206477

    Hi,

    We are just converting an Access 2002 database to SQL Server 2005. The access database has over 100 queries and a number of them use a calculation result from one column in another i.e. a+b=c c/a=e etc.

    Having now read a number of various items on the web, I am begining to think this is not possible using SQL Server, this would mean a complete re-write of a very large website.

    Please let me know if there is a way around this an some example code if possible.

    Thanks.

  • March 2, 2009 at 3:26 pm

    #952148

    I can assure you that SQL Server can handle this type of calculation. Please post an example so we can see what you are up against.

    Kind regards,

    Lee

  • March 2, 2009 at 3:46 pm

    #952160

    Hi Lee,

    Example as requested (does not contain whole query but gives the idea):

    SELECT VHCWorkTable.id, VHCSalesTable.IDfromWorkTable, VHCSalesTable.AccountNumber, VHCWorkTable.AccountNumber, VHCWorkTable.RetailLabRate, VHCWorkTable.VATRate, VHCWorkTable.Other1Time, VHCWorkTable.Other2Time, VHCWorkTable.Other3Time, VHCWorkTable.Other4Time, VHCWorkTable.Other5Time, VHCWorkTable.Other6Time, VHCWorkTable.Other7Time, VHCWorkTable.Other8Time, VHCWorkTable.Other9Time, VHCWorkTable.Other10Time, ([Other1Time]*[VHCWorkTable.RetailLabRate]) AS Other1TotalLab, ([Other1TotalLab]*[VHCWorkTable.VATRate]/100)+([Other1TotalLab]) AS Other1TotalLabincVAT, ([Other2Time]*[VHCWorkTable.RetailLabRate]) AS Other2TotalLab, ([Other2TotalLab]*[VHCWorkTable.VATRate]/100)+([Other2TotalLab]) AS Other2TotalLabincVAT, ([Other3Time]*[VHCWorkTable.RetailLabRate]) AS Other3TotalLab, ([Other3TotalLab]*[VHCWorkTable.VATRate]/100)+([Other3TotalLab]) AS Other3TotalLabincVAT, ([Other4Time]*[VHCWorkTable.RetailLabRate]) AS Other4TotalLab, ([Other4TotalLab]*[VHCWorkTable.VATRate]/100)+([Other4TotalLab]) AS Other4TotalLabincVAT, ([Other5Time]*[VHCWorkTable.RetailLabRate]) AS Other5TotalLab, ([Other5TotalLab]*[VHCWorkTable.VATRate]/100)+([Other5TotalLab]) AS Other5TotalLabincVAT, ([Other6Time]*[VHCWorkTable.RetailLabRate]) AS Other6TotalLab, ([Other6TotalLab]*[VHCWorkTable.VATRate]/100)+([Other6TotalLab]) AS Other6TotalLabincVAT

    FROM VHCWorkTable INNER JOIN VHCSalesTable ON VHCWorkTable.id = VHCSalesTable.IDfromWorkTable;

    Thanks,

    Colin

  • March 3, 2009 at 2:38 am

    #952269

    Colin,

    Rather than repeatedly use the column alias 'Other1TotalLab' in the next column, just repeat the calculation. (Maybe a performance guru will shout at me here!) Also if you add 1 to the vat calculation the multiplication becomes something like * 1.175 (assume 17.5 vat ) so you don't need to add Other1TotalLab back in again.

    eg

    select

    ([Other1Time]*[VHCWorkTable.RetailLabRate]) AS Other1TotalLab,

    ([Other1Time]*[VHCWorkTable.RetailLabRate]*((VHCWorkTable.VATRate/100) + 1)) AS Other1TotalLabincVAT

    etc etc

    Not tested this but I think it is along the right lines.

    Tony

  • March 3, 2009 at 9:22 am

    #952499

    This is correct. You CANNOT create an alias in one column and use it in the next. Limey has the right idea. you will need to replace it with the complete calcuation.

    Cheers,

    Brian

  • March 3, 2009 at 9:33 am

    #952509

    Absolutely correct - you will have a few things to rewrite, and this is one of them. Macros, DLOOKUP, etc. are other things that you might run into...there won't be a 100% direct replacement for some things such as these, but you wouldn't think that everything ports without some work. I have converted several and the query you mention shouldn't be a deal killer. You'll be much happier on SQL Server once you get past some of the conversion pains.

    Best of luck!

  • March 3, 2009 at 10:01 am

    #952548

    What you CAN do is use what is called a CTE (Common Table Expression), which allows you to create reusable expressions.

    So - you'd have to build a CTE that holds the items that are reused by others, and the outer query then reuses those items.

    Something like:

    ;With InnerCTE as

    (

    select

    ([Other1Time]*[VHCWorkTable.RetailLabRate]) AS Other1TotalLab,

    etc...)

    SELECT

    [Other1TotalLab]*((VHCWorkTable.VATRate/100) + 1) AS Other1TotalLabincVAT

    --other fields here

    from InnerCTE

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 5, 2009 at 2:56 am

    #953867

    Thanks All,

    I guess I have some writing to do!

    Regards,

    Colin

Viewing 8 posts - 1 through 7 (of 7 total)

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