April 3, 2012 at 3:40 am
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
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
April 3, 2012 at 8:00 am
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 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]
April 3, 2012 at 9:32 am
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