Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Red face Unanswered: Access query to View convertion

    Hello all...

    I am converting a access database to sql through SSMA.
    after convertion i got that some queries are not converted. please help me to convert them

    SELECT DISTINCTROW Format$([Policies].[Date Paid],'mmm yy') AS [Month Paid], Policies.Consultant AS Name, Sum([Policies]![Consultant Commission]*[Currency]![GBP Conversion]) AS Consultant, Sum([Policies]![Reserve]*[Currency]![GBP Conversion]) AS Reserve, Sum([Policies]![100 %]*[Currency]![GBP Conversion]) AS [100%], Sum(([Total Commission]-[Consultant Commission]-[Sales Manager at 10%]-[Area Manager at 5%])*[Currency]![GBP Conversion]) AS GWM, Policies.[Date Paid]
    FROM [Currency] INNER JOIN Policies ON Currency.CurrencyID = Policies.[Payment Currency]
    GROUP BY Format$([Policies].[Date Paid],'mmm yy'), Policies.Consultant, Policies.[Policy Status], Policies.[Date Paid]
    HAVING (((Policies.[Policy Status])=2 Or (Policies.[Policy Status])=5) AND ((Policies.[Date Paid])>#8/1/2005#<#7/31/2006#))
    ORDER BY Policies.[Date Paid] DESC;

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    SELECT DISTINCT 
    	RIGHT(CONVERT(VARCHAR(9), [Policies].[Date Paid], 6), 6) AS [Month Paid], 
    	Policies.Consultant AS Name, 
    	Sum([Policies].[Consultant Commission]*[Currency].[GBP Conversion]) AS Consultant, 
    	Sum([Policies].[Reserve]*[Currency].[GBP Conversion]) AS Reserve, 
    	Sum([Policies].[100 %]*[Currency].[GBP Conversion]) AS [100%], 
    	Sum(([Total Commission]-[Consultant Commission]-[Sales Manager at 10%]-[Area Manager at 5%])*[Currency].[GBP Conversion]) AS GWM, 
    	Policies.[Date Paid]
    FROM [Currency] 
    	INNER JOIN Policies ON 
    		Currency.CurrencyID = Policies.[Payment Currency]
    GROUP BY RIGHT(CONVERT(VARCHAR(9), [Policies].[Date Paid], 6), 6), 
    	Policies.Consultant, 
    	Policies.[Policy Status], 
    	Policies.[Date Paid]
    HAVING	Policies.[Policy Status] IN (2, 5) AND
    	Policies.[Date Paid] BETWEEN '2006/31/07' AND '2005/01/08'
    ORDER BY Policies.[Date Paid] DESC;
    Last edited by Wim; 03-18-11 at 09:04.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I am pretty sure that you can move the HAVING conditions to WHERE.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by aflorin27 View Post
    I am pretty sure that you can move the HAVING conditions to WHERE.
    ..Indeed..
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2011
    Posts
    2

    Red face not working

    thanks for reply the query but after i run this view i got a error the " The convertion of a char data type to a datetime data type resulted in a out of range datetime value "

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    The folks on this board are going to be able to help you out a bit and point you in the right direction, but YOU are going to have to do SOME of the work yourself.

    Read that error statement again.

    A char data type is like a text data type in Access and a datetime data type is essentially the same between Sql Server and Access.

    So this error is saying: you have a text field with what-looks-like datetime data in it and I am trying to convert that data to datetime in SQL Server, but one or more of those pieces of data in that text field is not really a valid datetime.

    So, YOUR to do is to look at the Access data in one of your text fields and see if there is data there that is not a valid date.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •