SQL to Access conversion

  • Hi All,

    I want to convert the following SQL statement into Access 2010 SQL so i can run the query in Access 2010

    CREATE TABLE [dbo].[tab1](

    [NUM] [int] NULL,

    [bdDQLinkID] [nvarchar](255) NULL,

    [bdDQID] [nvarchar](255) NULL,

    [bdID] [int] NOT NULL,

    [bdSalutation] [nvarchar](255) NULL,

    [bdFirstName] [nvarchar](255) NULL,

    [bdMiddleName] [nvarchar](255) NULL,

    [bdSurname] [nvarchar](255) NULL,

    [bdFullName] [nvarchar](255) NULL,

    [bdTitle] [nvarchar](255) NULL,

    [bdPhoneNumber] [nvarchar](255) NULL,

    [bdEmail] [nvarchar](255) NULL,

    [bdWebAddress] [nvarchar](255) NULL,

    [bdOrganisation] [nvarchar](255) NULL,

    [bdAddress1] [nvarchar](255) NULL,

    [bdAddress2] [nvarchar](255) NULL,

    [bdAddress3] [nvarchar](255) NULL,

    [bdAddress4] [nvarchar](255) NULL,

    [bdPostTown] [nvarchar](255) NULL,

    [bdCounty] [nvarchar](255) NULL,

    [bdPostcode] [nvarchar](255) NULL,

    [bdCountry] [nvarchar](255) NULL,

    [bdInvoiceNo] [nvarchar](255) NULL,

    [bdInvtot] [nvarchar](255) NULL,

    [bdVehicleRegNo] [nvarchar](255) NULL,

    [bdPlant] [nvarchar](255) NULL,

    [bdCreatedOn] [datetime] NULL,

    [bdCarMan] [nvarchar](255) NULL,

    [bdCarMod] [nvarchar](255) NULL,

    [bdCarManYear] [nvarchar](255) NULL,

    [bdMileage] [nvarchar](255) NULL,

    [bdCustomer] [nvarchar](255) NULL,

    [bdBranchCode] [nvarchar](255) NULL,

    [bdURNNo] [nvarchar](255) NULL,

    [bdSuccess] [nvarchar](255) NULL,

    [bdBranchType] [nvarchar](255) NULL,

    [bdMonth] [nvarchar](255) NULL,

    [bdMarked] [nvarchar](255) NULL,

    [bdDQFlag] [nvarchar](255) NULL,

    [mdDQID] [nvarchar](255) NULL,

    [mdID] [int] NOT NULL,

    [mdSalutation] [nvarchar](255) NULL,

    [mdFirstName] [nvarchar](255) NULL,

    [mdMiddleName] [nvarchar](255) NULL,

    [mdSurname] [nvarchar](255) NULL,

    [mdFullName] [nvarchar](255) NULL,

    [mdTitle] [nvarchar](255) NULL,

    [mdPhoneNumber] [nvarchar](255) NULL,

    [mdEmail] [nvarchar](255) NULL,

    [mdWebAddress] [nvarchar](255) NULL,

    [mdOrganisation] [nvarchar](255) NULL,

    [mdAddress1] [nvarchar](255) NULL,

    [mdAddress2] [nvarchar](255) NULL,

    [mdAddress3] [nvarchar](255) NULL,

    [mdAddress4] [nvarchar](255) NULL,

    [mdPostTown] [nvarchar](255) NULL,

    [mdCounty] [nvarchar](255) NULL,

    [mdPostCode] [nvarchar](255) NULL,

    [mdCountry] [nvarchar](255) NULL,

    [mdInvoiceNo] [nvarchar](255) NULL,

    [mdInvtot] [nvarchar](255) NULL,

    [mdVehicleRegNo] [nvarchar](255) NULL,

    [mdPlant] [nvarchar](255) NULL,

    [mdCreatedOn] [datetime] NULL,

    [mdCarMan] [nvarchar](255) NULL,

    [mdCarMod] [nvarchar](255) NULL,

    [mdCarManYear] [nvarchar](255) NULL,

    [msMileage] [nvarchar](255) NULL,

    [mdCustomer] [nvarchar](255) NULL,

    [mdBranchCode] [nvarchar](255) NULL,

    [mdURNNo] [nvarchar](255) NULL,

    [mdSuccess] [nvarchar](255) NULL,

    [mdBranchType] [nvarchar](255) NULL,

    [mdMonth] [nvarchar](255) NULL,

    [mdMarked] [nvarchar](255) NULL,

    [mdDQFlag] [nvarchar](255) NULL,

    [mdDQLinkID] [nvarchar](255) NULL

    ) ON [PRIMARY]

    GO

    DECLARE @Table TABLE (Num INT, ID INT)

    INSERT INTO @Table

    SELECT

    COUNT(bd.DQLinkID) AS Num,

    bd.DQLinkID as ID

    FROM

    [Branch data 2009 - 2012] AS bd

    GROUP BY

    bd.DQLinkID

    DECLARE @num INT, @a INT = 1

    SELECT @num = MAX(num) FROM @table

    WHILE @a <= @num

    BEGIN

    INSERT INTO tab1

    SELECT

    @a as NUM,

    bd.DQLinkID as bdDQLinkID,

    bd.DQID as bdDQID,

    bd.ID as bdID,

    bd.Salutation as bdSalutation,

    bd.[First Name] as bdFirstName,

    bd.[Middle Name] as bdMiddleName,

    bd.Surname as bdSurname,

    bd.[Full Name] as bdFullName,

    bd.Title as bdTitle,

    bd.[Phone number] as bdPhoneNumber,

    bd.Email as bdEmail,

    bd.[Web Address] as bdWebAddress,

    bd.Organisation as bdOrganisation,

    bd.[Address Line 1] as bdAddress1,

    bd.[Address Line 2] as bdAddress2,

    bd.[Address Line 3] as bdAddress3,

    bd.[Address Line 4] as bdAddress4,

    bd.[Post Town] as bdPostTown,

    bd.County as bdCounty,

    bd.Postcode as bdPostcode,

    bd.Country as bdCountry,

    bd.[Invoice No] as bdInvoiceNo,

    bd.Invtot as bdInvtot,

    bd.VehicleRegNo as bdVehicleRegNo,

    bd.Plant as bdPlant,

    bd.[Created on] as bdCreatedOn,

    bd.CarManufacturer as bdCarMan,

    bd.[Car model] as bdCarMod,

    bd.CarManufactYear as bdCarManYear,

    bd.Mileage as bdMileage,

    bd.Customer as bdCustomer,

    bd.[branch code] as bdBranchCode,

    bd.[urn no] as bdURNNo,

    bd.success as bdSuccess,

    bd.[branch type] as bdBranchType,

    bd.[Month] as bdMonth,

    bd.Marked as bdMarked,

    bd.DQFlag as bdDQFlag,

    md.DQID as mdDQID,

    md.ID as mdID,

    md.Salutation as mdSalutation,

    md.[First Name] as mdFirstName,

    md.[Middle Name] as mdMiddleName,

    md.Surname as mdSurname,

    md.[Full Name] as mdFullName,

    md.Title as mdTitle,

    md.[Phone number] as mdPhoneNumber,

    md.Email as mdEmail,

    md.[Web Address] as mdWebAddress,

    md.Organisation as mdOrganisation,

    md.[Address Line 1] as mdAddress1,

    md.[Address Line 2] as mdAddress2,

    md.[Address Line 3] as mdAddress3,

    md.[Address Line 4] as mdAddress4,

    md.[Post Town] as mdPostTown,

    md.County as mdCounty,

    md.Postcode as mdPostCode,

    md.Country as mdCountry,

    md.[Invoice No] as mdInvoiceNo,

    md.Invtot as mdInvtot,

    md.VehicleRegNo as mdVehicleRegNo,

    md.Plant as mdPlant,

    md.[Created on] as mdCreatedOn,

    md.CarManufacturer as mdCarMan,

    md.[Car model] as mdCarMod,

    md.CarManufactYear as mdCarManYear,

    md.Mileage as msMileage,

    md.Customer as mdCustomer,

    md.[branch code] as mdBranchCode,

    md.[urn no] as mdURNNo,

    md.success as mdSuccess,

    md.[branch type] as mdBranchType,

    md.[month] as mdMonth,

    md.Marked as mdMarked,

    md.DQFlag as mdDQFlag,

    md.DQLinkID as mdDQLinkID

    FROM

    [Branch data 2009 - 2012] AS bd

    INNER JOIN

    [main mailing data] AS md

    ON

    bd.dqlinkid = md.dqlinkid

    WHERE

    bd.DQLinkID IN (SELECT ID FROM @Table WHERE Num = @a)

    ORDER BY 2,1

    SET @a = @a + 1

    END

    GO

    SELECT * FROM tab1 WHERE num = ? --Where ? is the number of dupes you want, 1,2,3,4,5,6,7,8,9,10,11,12,13 etc etc

    GO

    DROP TABLE tab1

    GO

    all replies are much appreciated, thanks

  • for your sample data can you post hard coded inserts. we do not have your underlying tables so SELECT * INTO #BLah FROM BlaBla will not work in our testing environment. please see the link in my signature if you would like some reference material on how to generate the hard coded inserts.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I don't see that the temp table, table variable, or calculations are actually doing anything.

    Seems to me the above can be rewritten as

    SELECT

    @a as NUM,

    bd.DQLinkID as bdDQLinkID,

    bd.DQID as bdDQID,

    bd.ID as bdID,

    bd.Salutation as bdSalutation,

    bd.[First Name] as bdFirstName,

    bd.[Middle Name] as bdMiddleName,

    bd.Surname as bdSurname,

    bd.[Full Name] as bdFullName,

    bd.Title as bdTitle,

    bd.[Phone number] as bdPhoneNumber,

    bd.Email as bdEmail,

    bd.[Web Address] as bdWebAddress,

    bd.Organisation as bdOrganisation,

    bd.[Address Line 1] as bdAddress1,

    bd.[Address Line 2] as bdAddress2,

    bd.[Address Line 3] as bdAddress3,

    bd.[Address Line 4] as bdAddress4,

    bd.[Post Town] as bdPostTown,

    bd.County as bdCounty,

    bd.Postcode as bdPostcode,

    bd.Country as bdCountry,

    bd.[Invoice No] as bdInvoiceNo,

    bd.Invtot as bdInvtot,

    bd.VehicleRegNo as bdVehicleRegNo,

    bd.Plant as bdPlant,

    bd.[Created on] as bdCreatedOn,

    bd.CarManufacturer as bdCarMan,

    bd.[Car model] as bdCarMod,

    bd.CarManufactYear as bdCarManYear,

    bd.Mileage as bdMileage,

    bd.Customer as bdCustomer,

    bd.[branch code] as bdBranchCode,

    bd.[urn no] as bdURNNo,

    bd.success as bdSuccess,

    bd.[branch type] as bdBranchType,

    bd.[Month] as bdMonth,

    bd.Marked as bdMarked,

    bd.DQFlag as bdDQFlag,

    md.DQID as mdDQID,

    md.ID as mdID,

    md.Salutation as mdSalutation,

    md.[First Name] as mdFirstName,

    md.[Middle Name] as mdMiddleName,

    md.Surname as mdSurname,

    md.[Full Name] as mdFullName,

    md.Title as mdTitle,

    md.[Phone number] as mdPhoneNumber,

    md.Email as mdEmail,

    md.[Web Address] as mdWebAddress,

    md.Organisation as mdOrganisation,

    md.[Address Line 1] as mdAddress1,

    md.[Address Line 2] as mdAddress2,

    md.[Address Line 3] as mdAddress3,

    md.[Address Line 4] as mdAddress4,

    md.[Post Town] as mdPostTown,

    md.County as mdCounty,

    md.Postcode as mdPostCode,

    md.Country as mdCountry,

    md.[Invoice No] as mdInvoiceNo,

    md.Invtot as mdInvtot,

    md.VehicleRegNo as mdVehicleRegNo,

    md.Plant as mdPlant,

    md.[Created on] as mdCreatedOn,

    md.CarManufacturer as mdCarMan,

    md.[Car model] as mdCarMod,

    md.CarManufactYear as mdCarManYear,

    md.Mileage as msMileage,

    md.Customer as mdCustomer,

    md.[branch code] as mdBranchCode,

    md.[urn no] as mdURNNo,

    md.success as mdSuccess,

    md.[branch type] as mdBranchType,

    md.[month] as mdMonth,

    md.Marked as mdMarked,

    md.DQFlag as mdDQFlag,

    md.DQLinkID as mdDQLinkID

    FROM

    [Branch data 2009 - 2012] AS bd

    INNER JOIN

    [main mailing data] AS md

    ON

    bd.dqlinkid = md.dqlinkid

    Which would clarify this and be ported to Access.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Viewing 3 posts - 1 through 2 (of 2 total)

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