Solved

remove 1 row of the duplicate row

Posted on 2011-03-19
14
246 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

'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 …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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