Translating SQL server query to Access SQL query

S

SimonSezz

Registered User.
Local time
Today, 10:31
Joined
Jun 19, 2008
Messages
30
I'm converting an old database to an Access program and there are a bunch of SQL queries that need to be imported into Access. I'm no SQL expert but I can create simple queries. The old program used an SQL server and here is an example SQL query it used:

Code: 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Sproc_GenerateEmployerSetRateInvoices]
@sQuery varchar(1000),
@CurrentDate datetime,
@AccountingPeriodID int
AS
BEGIN TRY
 BEGIN TRAN
  Begin
   Declare @BatchID int
   Insert Into TAC_InvoiceBatch(RegenerationBatch)
   values(2)
  set @BatchID = Scope_Identity()
  End 
  BEGIN
     CREATE TABLE #InvoiceDetailTmp (
   InvoiceDetailIDTmp int not null Identity(1,1),
   GenerationDateID int null,   
   EmployerID   int,
   PlanID    int,
   EmployeeID    int,
   GenerationDate datetime,
   PeriodtoBillDate datetime,
   Rate money,
   AccountingPeriodID int,
   DateActive datetime,
   BillingCycleID int,
   ContractID int,
   PlanBenWWaitID int,
   SetRateAtEmployee int,
   PlanRateID Int,
   InitialFee int,
   GroupID varchar(255)
    )
   CREATE TABLE [#InvoiceTmp](
   [InvoiceIDTmp] [int] IDENTITY(1,1) NOT NULL,
   [InvoiceBatchID] [int] NULL,
   [EmployerID] [int] NULL,
   [InvoiceBalance] [int] NULL,
   [GenerationDate] [datetime] NULL,
   [Balance] [money] NULL,
   [PaidAmount] [money] NULL,
   [TotalAmount] [money] NULL,
   [DueDate] [datetime] NULL,
   [UpdateDate] [datetime] NULL,
   [AccountingPeriodID] [int] NULL,
   [BillingCycleID] [int] NULL
    )
  END
  BEGIN
   BEGIN
   --This section Creates all detail Invoice Items   
       Insert Into #InvoiceDetailTmp(GenerationDateID,EmployerID,PlanID,EmployeeID,GenerationDate,PeriodToBillDate,Rate,DateActive,ContractID,PlanBenWWaitID,BillingCycleID,SetRateAtEmployee,PlanRateID,InitialFee)
    select  a.GenerationDateID,
      c.EmployerID,
      b.PlanID, 
      e.EmployeeID,
      a.GenerationDate,
      dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) as PeriodToBill,
      c.Rate,
      dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) as DateActive,
      f.ContractID,
      f.PlanBenWWaitID,
      d.BillingCycleID,
      d.SetRateAtEmployee,
      c.EmployerPlanRateID,
      2
    From TAC_GenerationDate a inner join TMI_Plan b
    on a.BenefitTypeID = b.BenefitTypeID inner join
    tmi_EmployerPlanrate c on b.PlanID = c.PlanID  inner join
    TMI_BenefitType d on a.BenefitTypeID = d.BenefitTypeID inner join
    TMI_EmployeePlan e on c.PlanID = e.PlanID and e.EmployerID = c.EmployerID inner join
    TMI_EmployerPlanBenWWait f on c.ContractID = f.ContractID and b.BenefitTypeID = f.BenefitTypeID inner join
    TMI_Employer g on c.EmployerID = g.EmployerID inner join TMI_Provider h
    on g.ProvID = h.ProvID inner join TMI_ProviderPlan i on h.ProvID = i.ProvID and e.PlanID = i.planID
    inner join TMI_ProviderBenefitType j on h.ProvID = j.ProvID and d.BenefitTypeID = j.BenefitTypeID
    where d.SetRateAtEmployee = 2 and c.EmployerPlanRateID =
    (Select Top 1 a1.EmployerPlanRateID From TMI_EmployerPlanRate a1 where a1.EffectiveDate <= dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) and a1.EmployerID = c.EmployerID and a1.PlanID = c.PlanID order by a1.EffectiveDate desc)
    and d.BillingTypeID = 1
    and (
    ( --BIll by daterange employer = 2 bill by daterange employee = 2
     d.BillByDateRangeEmployer = 2 and d.BillByDateRangeEmployee =2
     and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
        (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or
    ( --BIll by daterange employer = 1 BypassStatus = 2
     d.BillByDateRangeEmployer = 1 and d.BypassStatus =2
     and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  f.startdate and f.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or 
    ( --BIll by daterange employee = 1 BypassStatus = 2
     d.BillByDateRangeEmployee = 1 and d.BypassStatus =2
     and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  e.startdate and e.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or
    ( --BIll by daterange employer = 1 BypassStatus = 1
     d.BillByDateRangeEmployer = 1 and d.BypassStatus =1
     --and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  f.startdate and f.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or
    ( --BIll by daterange employee = 1 BypassStatus = 1
     d.BillByDateRangeEmployee = 1 and d.BypassStatus =1
     --and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  e.startdate and e.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
     )
    ) -- Close out entire section
    union all
    select  a.GenerationDateID,
      c.EmployerID,
      b.PlanID, 
      e.EmployeeID,
      a.GenerationDate,
      dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) as PeriodToBill,
      c.Rate,
      dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) as DateActive,
      f.ContractID,
      f.PlanBenWWaitID,
      d.BillingCycleID,
      d.SetRateAtEmployee,
      c.EmployeePlanRateID,
      2
    From TAC_GenerationDate a inner join TMI_Plan b
    on a.BenefitTypeID = b.BenefitTypeID inner join
    tmi_EmployeePlanrate c on b.PlanID = c.PlanID  inner join
    TMI_BenefitType d on a.BenefitTypeID = d.BenefitTypeID inner join
    TMI_EmployeePlan e on c.PlanID = e.PlanID and e.EmployerID = c.EmployerID inner join
    TMI_EmployerPlanBenWWait f on f.ContractID = (Select Top 1 aa.ContractID from TMI_EmployerContract aa inner join 
    TMI_EmployerPlanBenWWait ab on aa.ContractID = ab.ContractID 
    where aa.ContractStartdate <= c.EffectiveDate and ab.BenefitTypeID = a.BenefitTypeID and ab.EmployerID = c.EmployerID order by aa.ContractStartDate Desc)
    and b.BenefitTypeID = f.BenefitTypeID inner join
    TMI_Employer g on c.EmployerID = g.EmployerID inner join TMI_Provider h
    on g.ProvID = h.ProvID inner join TMI_ProviderPlan i on h.ProvID = i.ProvID and e.PlanID = i.planID
    inner join TMI_ProviderBenefitType j on h.ProvID = j.ProvID and d.BenefitTypeID = j.BenefitTypeID
    where d.SetRateAtEmployee = 1 and c.EmployeePlanRateID =
    (Select Top 1 a1.EmployeePlanRateID From TMI_EmployeePlanRate a1 where a1.EffectiveDate <= dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) and a1.EmployeeID = c.EmployeeID and a1.PlanID = c.PlanID order by a1.EffectiveDate desc)
    and d.BillingTypeID = 1
    and (
    ( --BIll by daterange employer = 2 bill by daterange employee = 2
     d.BillByDateRangeEmployer = 2 and d.BillByDateRangeEmployee =2
     and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
        (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or
    ( --BIll by daterange employer = 1 BypassStatus = 2
     d.BillByDateRangeEmployer = 1 and d.BypassStatus =2
     and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  f.startdate and f.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or 
    ( --BIll by daterange employee = 1 BypassStatus = 2
     d.BillByDateRangeEmployee = 1 and d.BypassStatus =2
     and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  e.startdate and e.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or
    ( --BIll by daterange employer = 1 BypassStatus = 1
     d.BillByDateRangeEmployer = 1 and d.BypassStatus =1
     --and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  f.startdate and f.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
    )
    or
    ( --BIll by daterange employee = 1 BypassStatus = 1
     d.BillByDateRangeEmployee = 1 and d.BypassStatus =1
     --and not dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) is null
     and not dbo.EmployerActive(c.EmployerID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)) is null
     and h.ProvStatusID = 1
     and i.statusid = 1
     and c.statusID = 1
     and j.statusID = 1
     and dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) between  e.startdate and e.Enddate 
     and((f.BillCurrent = 1 and 
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+0, 0)))
     or
     (f.BillCurrent = 2 and
     (dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)>= DATEADD(mm, DATEDIFF(mm, 0, dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )))+1, 0))))
     )
    ) -- Close out entire section
    order by e.EmployeeID,b.PlanID,a.generationDate
   END
   BEGIN
   --THIS Section Creates the Invoice Item
   select a.*,c.fee from 
   #InvoiceDetailTmp a left join TAR_InvoiceDetail b
   on a.EmployeeID = b.EmployeeID and b.PlanID = b.PlanID
   inner join Tmi_EmployerPlanRate c on a.PlanRateID = c.EmployerPlanRateID
   where a.SetRateAtEmployee = 2 and b.EmployeeID is null and b.PlanID is null
   union
   select a.*,c.fee from 
   #InvoiceDetailTmp a left join TAR_InvoiceDetail b
   on a.EmployeeID = b.EmployeeID and b.PlanID = b.PlanID
   inner join Tmi_EmployeePlanRate c on a.PlanRateID = c.EmployeePlanRateID
   where a.SetRateAtEmployee = 1 and b.EmployeeID is null and b.PlanID is null
   END
  END
 Commit Tran
/*Make sure to update TAC_GeneratedDates.Generated from 2 to 1*/
return 1
END TRY
BEGIN CATCH
 IF (XACT_STATE()) <> 0 ROLLBACK
 DECLARE @ErrMsg varchar(4000), @ErrSeverity int
 set @ErrMsg = ERROR_MESSAGE()
 Set @ErrSeverity = ERROR_SEVERITY()
 RAISERROR(@ErrMsg, @ErrSeverity, @ErrSeverity) 
 return 0
END CATCH
/*select * from TAC_GenerationDate
Select ROW_NUMBER() OVER (ORDER BY EmployeePlanID) as Rownum,EmployeePlanID from TMI_EmployeePlan 
Select * from TMI_EmployeeStatus
select * from TMI_EmployerPlanRate
select * from TMI_BenefitType
select * from TMI_EmployerPlanRate
select * from TMI_Plan where PlanID in (106,108)
update TMI_EmployeeStatus
set EffectiveDate = dateadd(month,-5,getdate())
where EffectiveDate = '07/31/06'
declare @Var int
set @Var = 3
Select CASE @Var
WHEN 1 THEN DATEADD(month, 1, '01/01/2005')
WHEN 2 THEN DATEADD(week, 1, '01/01/2005')
WHEN 3 THEN DATEADD(week, 1 * 2, '01/01/2005')
WHEN 4 THEN DATEADD(quarter,1, '01/01/2005')
WHEN 5 THEN DATEADD(quarter, 1 * 2, '01/01/2005')
WHEN 6 THEN DATEADD(year, 1, '01/01/2005')
END 
 
Declare @Tbl Table 
(
i int,
j int IDENTITY(1,1)
)
Insert @Tbl 
Select 3333 Union All 
Select 3333 Union All 
Select 1234 Union All 
Select 1309 Union All 
Select 1309 Union All 
Select 1309 Union All 
Select 1309 union all
Select 1309 union all
Select 1309 union all
Select 3332 Union All 
Select 3332 Union All
Select 3332 
Select (Select Count(1) From @Tbl t1 Where t1.i= t.i and t1.j<=t.j  ) , i From @Tbl t 
 
*/

There are a bunch of SQL files such as this and they are lengthy. My first question is, what is the best way to go about converting this into my new Access program?

Also, I think the "CREATE TABLE" line in SQL server cannot be used in Access SQL, so is there any way to create temporary tables and then delete them in a query or should I just have a table that is emptied every time the query is run?
 
Access can be used as a front-end for sql Server. So if sql server is your backend, there is no need to rewrite the queries. But I take it that sql server is no longer existent for you at all. In that case, yes, you will have to translate.

I don't know sql server, but it looks to me you've got an entire stored procedure above, not just a query. A SP can execute multiple commands one after another. You can't do that in an Access query, strictly speaking, but you can accomplish the same thing using VBA (very much like VB6). Hope you know a little programming. There are at least two ways to run a query from VBA. You can first paste it into SQL view and then save it as a query object (sort of like saving an SP or a View) and then run it from VBA like this:
CurrentDb.Execute "qryUPDATE"

or you can type it all out:

CurrentDb.Execute "UPDATE Customers SET LastName = 'Smith'"

Here's a sample Create Table query in Access (warning, abridged). Note that "boolean" columns are called YesNo columns. Memo fields are for long strings/texts. Autonumber columns are type "Counter". Multicolumn primary key. "Long" is a long integer.

CREATE TABLE Trans
(
NextActivityDate DATE,
AcctNo TEXT (50),
TranID Long,
clientNo Long,
ExtractedID Text(12),
Comments Memo,
Unworkable YesNo,
Amount Currency,
AutoNum Counter,
StartDate Date,
Primary Key(ClientNo, TranID)
)

If you're new to VBA, create a new form without wizards (i.e. choose DesignView) and drop a command button from the Toolbox (cancel the wizards). Right-click the button > Properties > Event > OnClick, choose "Event Procedure" from the drop down and click the ellipsis. This opens up a code block:

Private Sub Command0_Click()
'put your VBA code here
Msgbox "Finished."
End Sub
This code will run whenever the button is mouse-clicked. In VBA you create variables using the DIM keyword instead of the Declare keyword - and don't use the @-sign.
Dim message as string
message = "hello world"
msgbox message

Access doesn't have temp tables, although you can, of course, use a regular table as a temporary table. It won't be unique to the current user, though, if you have multiple users.

You can't do a TRAN from within an Access query. However, you can do it in VBA using the ADO object model. (I don't know if DAO has transactions). For example:
Public Sub HowToRollback()
On Error Resume Next
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.BeginTrans
'
' manipulate data here
'
if err.Number <> 0 Then
con.RollbackTrans
Else con.CommitTrans
end if

End Sub

I'm not sure how much to write here, because I don't know how much VBA programming you already know. My advice is, any time you get stuck on a VBA issue, create a new thread in the "Modules and VBA" section.
 
Last edited:
Another thing I should have mentioned. When doing multiple inner joins, you have to use parantheses as follows. End each Inner join in a closing parantheses. Cluster all the opening parantheses after the FROM keyword, like this:

SELECT t1.* FROM (((((((((t1
INNER JOIN t2 on t1.ID = t2.ID)
INNER JOIN t3 on t3.id = t2.ID)
INNER JOIN t4 on t4.id = t3.id)
INNER JOIN t5 on t5.id = t4.id)
INNER JOIN t6 on t6.id = t5.id)
INNER JOIN t7 on t7.id = t6.id)
INNER JOIN t8 on t8.id = t7.id)
INNER JOIN t9 on t9.id = t8.id)
INNER JOIN t10 on t10.id = t9.id)
 
OK, Thanks for the help. I use a lot of VBA but I have never written queries in VBA yet. I'll read up on it a bit and I'll start working on it.
 
You must log in or register to reply here.

Similar threads

T
Solved row number in subform
Replies
4
Views
215
TipsyWolf
T
AOB
Solved Parameterised query requires me to re-cast parameter in order to run (?)
Replies
6
Views
267
AOB
AOB
E
Solved Union Query Average Grouped
Replies
5
Views
262
E9-Tech
E
C
Query return on alias fields
Replies
8
Views
332
June7
June7
P
SQL Hangs
2
Replies
30
Views
746
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom