Solved

remove 1 row of the duplicate row

Posted on 2011-03-19
14
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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