Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007

    Question Unanswered: Convert Query from SQL Server to MS Access

    I have to admit I am just plain stuck on this one. I am setting up a Passthrough query. The back end Database is SQL Server. Below is the query as it runs on SQL Server. I need it to run on MS Access. the problems I am running into seem to be related to the date conversion functions. Any MS Access SQL Gurus able to help?

    SELECT r.MktgURL, r.exclude, r.BizRes, r.LOB AS Segment, 'Monthly' AS DataType, 
    substring(cast(d.YearMonth as varchar(6)),1,4) + '-' + substring(cast(d.YearMonth as varchar(6)),5,2)   AS ResponseDate,
    r.Owner, r.PromoCode, r.CodeUse, Case when CodeUse='URL' Or CodeUse='Domain' Then r.URL Else '' End AS VanityURL, 
    r.Media, r.LeadOffer, r.StartDate, r.EndDate, Sum(r.Responses) AS Responses, Sum(r.FiOS) AS FiOS, Sum(r.FiosTV) AS FiosTV, 
    Sum(r.HSI) AS HSI, Sum(r.Bundles) AS Bundles, Sum(r.DTV) AS DTV, Sum(r.Phone) AS Phone, Sum(r.Wireless) AS Wireless, 
    r.PromoZone AS PromoZone, r.CodeType, SUM(r.FTVQuals) AS FTVQuals
    FROM tblURLResponseData_Day AS r INNER JOIN tblDays AS d ON r.ResponseDate = d.WkDate 
    WHERE r.ResponseDate >= '6/1/2012' 
    --and r.ResponseDate >= Dateadd(month, -4, getdate())
    AND PIRFcampaigntype in ('SMB ACQ Awareness', 'SMB ACQ Direct Response','SMB Portal Awareness',
    'SMB Portal Direct Response','Direct Response')
    GROUP BY r.MktgURL, r.BizRes, r.LOB, d.YearMonth, r.Owner, r.PromoCode, r.CodeUse, 
    Case when CodeUse='URL' Or CodeUse='Domain' Then r.URL Else '' End, r.Media, r.exclude, r.LeadOffer, r.StartDate, 
    r.EndDate, r.PromoZone, r.CodeType) A

  2. #2
    Join Date
    Jun 2007
    I figured it out. SORRY...

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Glad that you figured it out. Was the closing ") A" the only problem?

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2007
    Well, the ") A" was a problem for sure. Not sure where that even came from...

    But I decided to use the SQL "as-is" in a Pass-Through query. I didn't have to change a thing. As a bonus, I don't have to have the linked tables in my DB.

    The data is not static all the way back to the beginning of the year so I will have a VBA module to delete all rows from the table in my DB and then I believe I can use the Pass-Through query to repopulate it..

    I will use VBA to

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