Delete recordsets with same Date and Line

Hi All!

I need help with a Statement!
I am working with an Access2000 DB.
I have the following Problem.

ChNrLinieDatum Code 39 Stckzahl BHL1 BHL2 BMRH
582-064L2.1008.03.2005 02:30:00FCAA14821701
582-064L2.1008.03.2005 02:30:00FCAA14871701
582-114L2.1208.03.2005 01:00:00FAC827501240
582-114L2.1208.03.2005 01:00:00FAC827441240
582-114L2.1208.03.2005 01:00:00FAC827501240
582-094L2.707.03.2005 19:45:00FAE74323481
582-094L2.707.03.2005 19:45:00FAE74489481
582-094L2.707.03.2005 19:45:00FAE74489481
581-294L2.807.03.2005 18:20:00FA8V2658221
581-294L2.807.03.2005 18:20:00FA8V2652221
581-294L2.807.03.2005 18:20:00FA8V2658221
582-114L2.1207.03.2005 17:45:00FAAR20721236
As you can see I have a few recordsets that are double. The Thing is, there
is an ID that makes them different.

I need a Statement that deletes the surplus records where 'Datum' and
'Linie' are identical to another record. 1 record has to remain of course.
I thought of something like this.

DELETE FROM tbAuswert
WHERE EXISTS(
SELECT *
FROM tbAuswert
WHERE (Linie AND Datum)
IN (
SELECT Linie AND Datum AS Suchkrit
FROM tbAuswert
GROUP BY Suchkrit
HAVING ((Count(Suchkrit)>1)
)
)

But I get an error:
You wanted to execute a Query that did not have the following expression
'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction

Perhaps you ccan help me.

Thanks
Julia

--
Message posted via http://www.sqlmonster.comJulia Hrtfelder via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I need help with a Statement!
> I am working with an Access2000 DB.
>...
> I need a Statement that deletes the surplus records where 'Datum' and
> 'Linie' are identical to another record. 1 record has to remain of course.
> I thought of something like this.
> DELETE FROM tbAuswert
> WHERE EXISTS(
> SELECT *
> FROM tbAuswert
> WHERE (Linie AND Datum)
> IN (
> SELECT Linie AND Datum AS Suchkrit
> FROM tbAuswert
> GROUP BY Suchkrit
> HAVING ((Count(Suchkrit)>1)
> )
> )
> But I get an error:
> You wanted to execute a Query that did not have the following expression
> 'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction
> Perhaps you ccan help me.

If you don't have a primary that uniquely identifies each row, then
this will be very difficult, as SQL is designed to operate only from the
data in the tables.

It's possible that you can add a column that gives you a unique ID. Had
you been using SQL Server, I could have showed you how. However, since
you are using Access, you are better off posting your question to a
forum for Access. There are considerable differences between the SQL in
SQL Server and Access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 17 May 2005 12:09:04 GMT, Julia Hrtfelder via SQLMonster.com
wrote:

>Hi All!
>I need help with a Statement!
>I am working with an Access2000 DB.
>I have the following Problem.
(snip)
>As you can see I have a few recordsets that are double. The Thing is, there
>is an ID that makes them different.
>I need a Statement that deletes the surplus records where 'Datum' and
>'Linie' are identical to another record. 1 record has to remain of course.

Hi Julia,

The following will work for SQL Server. I'm not sure about Access though
(it deviates from the standard in other ways than SQL Server does <g>),
so test it first, and repost in an Access group if this doesn't work.

Note: I assume that the ID column is called "ID" and that you want to
retain the row with the lowest ID value.

DELETE FROM tbAuswert
WHERE EXISTS
(SELECT *
FROM tbAuswert AS a2
WHERE a2.Datum = tbAuswert.Datum
AND a2.Linie = tbAuswert.Linie
AND a2.ID > tbAuswert.ID)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||That one really worked out!

Thank you so much. You are great!

Julia

--
Message posted via http://www.sqlmonster.com