Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

remove 1 row of the duplicate row

Posted on 2011-03-19
14
Medium Priority
?
251 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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

715 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