Solved

remove 1 row of the duplicate row

Posted on 2011-03-19
14
244 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
Comment Utility
I am working in access 2010 query grid
0
 
LVL 11

Expert Comment

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

Author Comment

by:PeterBaileyUk
Comment Utility
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
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
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
Comment Utility
found it! removed the parenthesis now working a treat
0
 

Author Closing Comment

by:PeterBaileyUk
Comment Utility
thank you!
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
FROM TempTblAbiDataDifferences;  <--

remove ;

that will do it
0
 

Author Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now