Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

remove 1 row of the duplicate row

Posted on 2011-03-19
14
Medium Priority
?
252 Views
Last Modified: 2012-05-11
I have some duplicate rows in a table how do I remove the duplicates but leave 1 of the rows intact in the table?

The attached code tells me I have 12 [mvris code] that are duplicated.

the rows are only duplicated twice so in my case with the data in the table I have 24 rows of which i need to remove 1 row for each [mvris code].
SELECT First(TblSMMTDataDifferences.[MVRIS CODE]) AS [MVRIS CODE Field], Count(TblSMMTDataDifferences.[MVRIS CODE]) AS NumberOfDups, TblSMMTDataDifferences.Introdiff, TblSMMTDataDifferences.MarqueDiff, TblSMMTDataDifferences.ModelRangeDiff, TblSMMTDataDifferences.RangeSeriesDiff, TblSMMTDataDifferences.CCDiff, TblSMMTDataDifferences.NomCCDiff, TblSMMTDataDifferences.DoorsDiff, TblSMMTDataDifferences.BodyTypeDiff, TblSMMTDataDifferences.TransmissionDiff, TblSMMTDataDifferences.FuelDiff, TblSMMTDataDifferences.AspirationDiff, TblSMMTDataDifferences.DriveTypeDiff, TblSMMTDataDifferences.DrivingAxleDiff, TblSMMTDataDifferences.ForwardGearsDiff, TblSMMTDataDifferences.SeatsDiff, TblSMMTDataDifferences.EngineModelDiff, TblSMMTDataDifferences.NoCylDiff, TblSMMTDataDifferences.ValvespercylDiff, TblSMMTDataDifferences.valvegearDiff, TblSMMTDataDifferences.powerkwDiff, TblSMMTDataDifferences.calcbhpDiff, TblSMMTDataDifferences.gvwDiff, TblSMMTDataDifferences.cabDiff, TblSMMTDataDifferences.vanroofDiff, TblSMMTDataDifferences.wheelbasetypeDiff
FROM TblSMMTDataDifferences
GROUP BY TblSMMTDataDifferences.Introdiff, TblSMMTDataDifferences.MarqueDiff, TblSMMTDataDifferences.ModelRangeDiff, TblSMMTDataDifferences.RangeSeriesDiff, TblSMMTDataDifferences.CCDiff, TblSMMTDataDifferences.NomCCDiff, TblSMMTDataDifferences.DoorsDiff, TblSMMTDataDifferences.BodyTypeDiff, TblSMMTDataDifferences.TransmissionDiff, TblSMMTDataDifferences.FuelDiff, TblSMMTDataDifferences.AspirationDiff, TblSMMTDataDifferences.DriveTypeDiff, TblSMMTDataDifferences.DrivingAxleDiff, TblSMMTDataDifferences.ForwardGearsDiff, TblSMMTDataDifferences.SeatsDiff, TblSMMTDataDifferences.EngineModelDiff, TblSMMTDataDifferences.NoCylDiff, TblSMMTDataDifferences.ValvespercylDiff, TblSMMTDataDifferences.valvegearDiff, TblSMMTDataDifferences.powerkwDiff, TblSMMTDataDifferences.calcbhpDiff, TblSMMTDataDifferences.gvwDiff, TblSMMTDataDifferences.cabDiff, TblSMMTDataDifferences.vanroofDiff, TblSMMTDataDifferences.wheelbasetypeDiff, TblSMMTDataDifferences.[MVRIS CODE]
HAVING (((Count(TblSMMTDataDifferences.[MVRIS CODE]))>1));

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 8
  • 6
14 Comments
 

Author Comment

by:PeterBaileyUk
ID: 35171117
I am working in access 2010 query grid
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171124
for the duplicate row, [MVRIS CODE] is the same or different?
0
 

Author Comment

by:PeterBaileyUk
ID: 35171133
the same! I thought you might respond gives chance for more points with two different questions, the last query has made the two rows the same exactly so now need to get rid of 1.

the spreadsheet attached shows two lots of dupes with same mvris code, the only difference was the variant diff field which has now been corrected. so i have identicle rows.


ee-ex.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171135
Actually, my question is, for the duplicate record, is there still some field different? For ex, you may have IDENTITY field which will be different.
Or this is the table without identity (auto increase number) column?
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171136
another question, this is one time duplicate cleanup task, or this might have to do over and over from time to time?
0
 

Author Comment

by:PeterBaileyUk
ID: 35171141
its done each month. could i create a temp table with pk of [mvris code] then append to it and the duplicate rows will be ignored? then delete rows from original table and then append the temp table to the original table.

0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 total points
ID: 35171161
if you try to insert row 1 by 1, then it's ok, the duplicate one will error (and ignore)
but if try to insert all rows at once, then whole insert will error.

but, since you are willing to create new table, that should make thing easier
1. new create table, make PK of AUTONUMBER instead of [MVRIS CODE]
    let say this PK column name is PKID
2. perform insert into new created table for duplicate record.
INSERT INTO NewTable (
[MVRIS CODE], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff
)
SELECT
[MVRIS CODE], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff
FROM TblSMMTDataDifferences
GROUP BY
[MVRIS CODE], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff
HAVING COUNT(*) > 1

Open in new window


3. delete all duplicate record from original table
DELETE FROM TblSMMTDataDifferences
WHERE [MVRIS CODE] IN (
	SELECT [MVRIS CODE]
	FROM NewTable
)

Open in new window


4. insert back the data, but will insert only 1 row from each
INSERT INTO TblSMMTDataDifferences (
[MVRIS CODE], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff
)
SELECT 
[MVRIS CODE], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff
FROM NewTable
WHERE PKID IN (SELECT MIN(PKID) FROM NewTable GROUP BY [MVRIS CODE])

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171176
However, as you mention job have to call once a month.
so, my question is, Is it ok to add 1 autonumber column to table TblSMMTDataDifferences?

if your answer is YES, and assume that new field name is "AutoID"
you can run below query to cleanup (may have to run few times, if can had more than "2 rows duplicate")

DELETE FROM TblSMMTDataDifferences
WHERE AutoID IN (
	SELECT MAX(AutoID)
	FROM TblSMMTDataDifferences
	GROUP BY [MVRIS CODE], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff
	HAVING COUNT([MVRIS CODE]) > 1
)

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 35171215
got two stages done just having parenthesis issues with the last statement (as you can see there are many columns)
INSERT INTO TblSMMTDataDifferences (
[INTRO DATE], [Previous Intro Date], MARQUE, [Previous Marque], [MODEL RANGE], [Previous Model Range], [RANGE SERIES], [Previous Range Series], VARIANT, [Previous Variant], CC, [Previous CC], [NOM CC], [Previous Nom CC], DOORS, [Previous DOORS], [BODY TYPE], [Previous BODY TYPE], TRANSMISSION, [Previous TRANSMISSION], FUEL, [Previous FUEL], ASPIRATION, [Previous ASPIRATION], [DRIVE TYPE], [Previous DRIVE TYPE], [DRIVING AXLE], [Previous DRIVING AXLE], [FORWARD GEARS], [Previous FORWARD GEARS], SEATS, [Previous SEATS], [ENGINE MODEL], [Previous ENGINE MODEL], Expr1034, [NO CYLINDERS], [Previous NO CYLINDERS], [VALVES PER CYLINDER], [Previous VALVES PER CYLINDER], [VALVE GEAR], [Previous VALVE GEAR], [POWER KW], [Previous POWER KW], [CALC BHP], [Previous CALC BHP], WHEELBASE, [Previous WHEELBASE], GVW, [Previous GVW], [CAB TYPE], [Previous CAB TYPE], [VAN ROOF CONFIG], [Previous VAN ROOF CONFIG], [WHEELBASE TYPE], [Previous WHEELBASE TYPE], CalcNomCC, [Previous CalcNomCC], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff, [MVRIS CODE], variantDiff, AbiMatchedStr2, AbiMatchedStr, CapMatchedStr2, CapMatchedStr, ContinentalMatchedStr, ContinentalMatchedStr2, GlassMatchedStr, GlassMatchedStr2, HalfordMatchedStr, HalfordMatchedStr2, IDSMatchedStr, IDSMatchedStr2, MichelinMatchedStr, MichelinMatchedStr2, TechDocMatchedStr, TechDocMatchedStr2, VividMatchedStr, VividMatchedStr2, SMMTComparitorDate, ComparitorType )
)
SELECT 
[INTRO DATE], [Previous Intro Date], MARQUE, [Previous Marque], [MODEL RANGE], [Previous Model Range], [RANGE SERIES], [Previous Range Series], VARIANT, [Previous Variant], CC, [Previous CC], [NOM CC], [Previous Nom CC], DOORS, [Previous DOORS], [BODY TYPE], [Previous BODY TYPE], TRANSMISSION, [Previous TRANSMISSION], FUEL, [Previous FUEL], ASPIRATION, [Previous ASPIRATION], [DRIVE TYPE], [Previous DRIVE TYPE], [DRIVING AXLE], [Previous DRIVING AXLE], [FORWARD GEARS], [Previous FORWARD GEARS], SEATS, [Previous SEATS], [ENGINE MODEL], [Previous ENGINE MODEL], Expr1034, [NO CYLINDERS], [Previous NO CYLINDERS], [VALVES PER CYLINDER], [Previous VALVES PER CYLINDER], [VALVE GEAR], [Previous VALVE GEAR], [POWER KW], [Previous POWER KW], [CALC BHP], [Previous CALC BHP], WHEELBASE, [Previous WHEELBASE], GVW, [Previous GVW], [CAB TYPE], [Previous CAB TYPE], [VAN ROOF CONFIG], [Previous VAN ROOF CONFIG], [WHEELBASE TYPE], [Previous WHEELBASE TYPE], CalcNomCC, [Previous CalcNomCC], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff, DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff, [MVRIS CODE], variantDiff, AbiMatchedStr2, AbiMatchedStr, CapMatchedStr2, CapMatchedStr, ContinentalMatchedStr, ContinentalMatchedStr2, GlassMatchedStr, GlassMatchedStr2, HalfordMatchedStr, HalfordMatchedStr2, IDSMatchedStr, IDSMatchedStr2, MichelinMatchedStr, MichelinMatchedStr2, TechDocMatchedStr, TechDocMatchedStr2, VividMatchedStr, VividMatchedStr2, SMMTComparitorDate, ComparitorType
FROM TempTblSMMTDataDifferences
WHERE autoID IN (SELECT MIN(autoID) FROM TempTblSMMTDataDifferences GROUP BY [MVRIS CODE])

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 35171216
found it! removed the parenthesis now working a treat
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 35171217
thank you!
0
 

Author Comment

by:PeterBaileyUk
ID: 35206216
Have similar problem but different table name but cannot get the query sql right
my attempt
DoCmd.RunSQL "INSERT INTO TempTblAbiDataDifferences (abiCode, MODEL_DESCRIPTION, [Previous MODEL DESCRIPTION], MANUFACTURED_FROM, [Previous MANUFACTURED FROM], MANUFACTURED_TO, [Previous MANUFACTURED TO], SERIES, [Previous SERIES], ENGINE_CC, [Previous ENGINE CC], abiDOORS, [Previous abiDOORS], BODY_TYPE, [Previous BODY TYPE], ENGINE_TYPE, [Previous ENGINE TYPE], TRANSMISSION_TYPE, [Previous TRANSMISSION TYPE], Gross_Kg, [Previous Gross Kg], ModelDescdiff, ManfFromdiff, ManfTodiff, Seriesdiff, EngineCCdiff, AbiDoorsdiff, AbiBodydiff, EngineTypediff, AbiTransmissiondiff, Grosskgdiff, AbiMatched, searchwordbyword, ComparitorTypeAbi)" _
& " SELECT TblAbiDataDifferences.abiCode, TblAbiDataDifferences.MODEL_DESCRIPTION, TblAbiDataDifferences.[Previous MODEL DESCRIPTION], TblAbiDataDifferences.MANUFACTURED_FROM, TblAbiDataDifferences.[Previous MANUFACTURED FROM], TblAbiDataDifferences.MANUFACTURED_TO, TblAbiDataDifferences.[Previous MANUFACTURED TO], TblAbiDataDifferences.SERIES, TblAbiDataDifferences.[Previous SERIES], TblAbiDataDifferences.ENGINE_CC, TblAbiDataDifferences.[Previous ENGINE CC], " _
& " TblAbiDataDifferences.abiDOORS, TblAbiDataDifferences.[Previous abiDOORS], TblAbiDataDifferences.BODY_TYPE, TblAbiDataDifferences.[Previous BODY TYPE], TblAbiDataDifferences.ENGINE_TYPE, TblAbiDataDifferences.[Previous ENGINE TYPE], TblAbiDataDifferences.TRANSMISSION_TYPE, TblAbiDataDifferences.[Previous TRANSMISSION TYPE], TblAbiDataDifferences.Gross_Kg, TblAbiDataDifferences.[Previous Gross Kg], TblAbiDataDifferences.ModelDescdiff, TblAbiDataDifferences.ManfFromdiff, " _
& " TblAbiDataDifferences.ManfTodiff, TblAbiDataDifferences.Seriesdiff, TblAbiDataDifferences.EngineCCdiff, TblAbiDataDifferences.AbiDoorsdiff, TblAbiDataDifferences.AbiBodydiff, TblAbiDataDifferences.EngineTypediff, TblAbiDataDifferences.AbiTransmissiondiff, TblAbiDataDifferences.Grosskgdiff, TblAbiDataDifferences.AbiMatched, TblAbiDataDifferences.searchwordbyword, TblAbiDataDifferences.ComparitorTypeAbi" _
& " FROM TempTblAbiDataDifferences; " _
& " GROUP BY " _
& " TblAbiDataDifferences.abiCode, TblAbiDataDifferences.MODEL_DESCRIPTION, TblAbiDataDifferences.[Previous MODEL DESCRIPTION], TblAbiDataDifferences.MANUFACTURED_FROM, TblAbiDataDifferences.[Previous MANUFACTURED FROM], TblAbiDataDifferences.MANUFACTURED_TO, TblAbiDataDifferences.[Previous MANUFACTURED TO], TblAbiDataDifferences.SERIES, TblAbiDataDifferences.[Previous SERIES], TblAbiDataDifferences.ENGINE_CC, TblAbiDataDifferences.[Previous ENGINE CC], " _
& " TblAbiDataDifferences.abiDOORS, TblAbiDataDifferences.[Previous abiDOORS], TblAbiDataDifferences.BODY_TYPE, TblAbiDataDifferences.[Previous BODY TYPE], TblAbiDataDifferences.ENGINE_TYPE, TblAbiDataDifferences.[Previous ENGINE TYPE], TblAbiDataDifferences.TRANSMISSION_TYPE, TblAbiDataDifferences.[Previous TRANSMISSION TYPE], TblAbiDataDifferences.Gross_Kg, TblAbiDataDifferences.[Previous Gross Kg], TblAbiDataDifferences.ModelDescdiff, TblAbiDataDifferences.ManfFromdiff, " _
& " TblAbiDataDifferences.ManfTodiff, TblAbiDataDifferences.Seriesdiff, TblAbiDataDifferences.EngineCCdiff, TblAbiDataDifferences.AbiDoorsdiff, TblAbiDataDifferences.AbiBodydiff, TblAbiDataDifferences.EngineTypediff, TblAbiDataDifferences.AbiTransmissiondiff, TblAbiDataDifferences.Grosskgdiff, TblAbiDataDifferences.AbiMatched, TblAbiDataDifferences.searchwordbyword, TblAbiDataDifferences.ComparitorTypeAbi" _
& " HAVING (((Count(*))>1));"



from other db this works:
DoCmd.RunSQL "INSERT INTO TempTblSMMTDataDifferences (abiCode, MODEL_DESCRIPTION, [Previous MODEL DESCRIPTION], MANUFACTURED_FROM, [Previous MANUFACTURED FROM], MANUFACTURED_TO, [Previous MANUFACTURED TO], SERIES, [Previous SERIES], ENGINE_CC, [Previous ENGINE CC], abiDOORS, [Previous abiDOORS], BODY_TYPE, [Previous BODY TYPE], ENGINE_TYPE, [Previous ENGINE TYPE], TRANSMISSION_TYPE, [Previous TRANSMISSION TYPE], Gross_Kg, [Previous Gross Kg], ModelDescdiff, ManfFromdiff, ManfTodiff, Seriesdiff, EngineCCdiff, AbiDoorsdiff, AbiBodydiff, EngineTypediff, AbiTransmissiondiff, Grosskgdiff, AbiMatched, searchwordbyword, ComparitorTypeAbi)" _
& " SELECT TblAbiDataDifferences.abiCode, TblAbiDataDifferences.MODEL_DESCRIPTION, TblAbiDataDifferences.[Previous MODEL DESCRIPTION], TblAbiDataDifferences.MANUFACTURED_FROM, TblAbiDataDifferences.[Previous MANUFACTURED FROM], TblAbiDataDifferences.MANUFACTURED_TO, TblAbiDataDifferences.[Previous MANUFACTURED TO], TblAbiDataDifferences.SERIES, TblAbiDataDifferences.[Previous SERIES], TblAbiDataDifferences.ENGINE_CC, TblAbiDataDifferences.[Previous ENGINE CC], " _
& " TblAbiDataDifferences.abiDOORS, TblAbiDataDifferences.[Previous abiDOORS], TblAbiDataDifferences.BODY_TYPE, TblAbiDataDifferences.[Previous BODY TYPE], TblAbiDataDifferences.ENGINE_TYPE, TblAbiDataDifferences.[Previous ENGINE TYPE], TblAbiDataDifferences.TRANSMISSION_TYPE, TblAbiDataDifferences.[Previous TRANSMISSION TYPE], TblAbiDataDifferences.Gross_Kg, TblAbiDataDifferences.[Previous Gross Kg], TblAbiDataDifferences.ModelDescdiff, TblAbiDataDifferences.ManfFromdiff, " _
& " TblAbiDataDifferences.ManfTodiff, TblAbiDataDifferences.Seriesdiff, TblAbiDataDifferences.EngineCCdiff, TblAbiDataDifferences.AbiDoorsdiff, TblAbiDataDifferences.AbiBodydiff, TblAbiDataDifferences.EngineTypediff, TblAbiDataDifferences.AbiTransmissiondiff, TblAbiDataDifferences.Grosskgdiff, TblAbiDataDifferences.AbiMatched, TblAbiDataDifferences.searchwordbyword, TblAbiDataDifferences.ComparitorTypeAbi" _
& " FROM TempTblAbiDataDifferences; " _
& " GROUP BY " _
& " TblAbiDataDifferences.abiCode, TblAbiDataDifferences.MODEL_DESCRIPTION, TblAbiDataDifferences.[Previous MODEL DESCRIPTION], TblAbiDataDifferences.MANUFACTURED_FROM, TblAbiDataDifferences.[Previous MANUFACTURED FROM], TblAbiDataDifferences.MANUFACTURED_TO, TblAbiDataDifferences.[Previous MANUFACTURED TO], TblAbiDataDifferences.SERIES, TblAbiDataDifferences.[Previous SERIES], TblAbiDataDifferences.ENGINE_CC, TblAbiDataDifferences.[Previous ENGINE CC], " _
& " TblAbiDataDifferences.abiDOORS, TblAbiDataDifferences.[Previous abiDOORS], TblAbiDataDifferences.BODY_TYPE, TblAbiDataDifferences.[Previous BODY TYPE], TblAbiDataDifferences.ENGINE_TYPE, TblAbiDataDifferences.[Previous ENGINE TYPE], TblAbiDataDifferences.TRANSMISSION_TYPE, TblAbiDataDifferences.[Previous TRANSMISSION TYPE], TblAbiDataDifferences.Gross_Kg, TblAbiDataDifferences.[Previous Gross Kg], TblAbiDataDifferences.ModelDescdiff, TblAbiDataDifferences.ManfFromdiff, " _
& " TblAbiDataDifferences.ManfTodiff, TblAbiDataDifferences.Seriesdiff, TblAbiDataDifferences.EngineCCdiff, TblAbiDataDifferences.AbiDoorsdiff, TblAbiDataDifferences.AbiBodydiff, TblAbiDataDifferences.EngineTypediff, TblAbiDataDifferences.AbiTransmissiondiff, TblAbiDataDifferences.Grosskgdiff, TblAbiDataDifferences.AbiMatched, TblAbiDataDifferences.searchwordbyword, TblAbiDataDifferences.ComparitorTypeAbi" _
& " HAVING (((Count(*))>1));"

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35206427
FROM TempTblAbiDataDifferences;  <--

remove ;

that will do it
0
 

Author Comment

by:PeterBaileyUk
ID: 35206503
spotted it eventually! my eyes go  weird looking at sql all day :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Loops Section Overview
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question