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.


ChNr Linie Datum Code 39 Stückzahl BHL1 BHL2 BMRH
582-064 L2.10 08.03.2005 02:30:00 FCAA 1482 17 0 1
582-064 L2.10 08.03.2005 02:30:00 FCAA 1487 17 0 1
582-114 L2.12 08.03.2005 01:00:00 FAC8 2750 12 4 0
582-114 L2.12 08.03.2005 01:00:00 FAC8 2744 12 4 0
582-114 L2.12 08.03.2005 01:00:00 FAC8 2750 12 4 0
582-094 L2.7 07.03.2005 19:45:00 FAE7 4323 4 8 1
582-094 L2.7 07.03.2005 19:45:00 FAE7 4489 4 8 1
582-094 L2.7 07.03.2005 19:45:00 FAE7 4489 4 8 1
581-294 L2.8 07.03.2005 18:20:00 FA8V 2658 2 2 1
581-294 L2.8 07.03.2005 18:20:00 FA8V 2652 2 2 1
581-294 L2.8 07.03.2005 18:20:00 FA8V 2658 2 2 1
582-114 L2.12 07.03.2005 17:45:00 FAAR 2072 12 3 6
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.com

re: Delete recordsets with same Date and Line

Julia Härtfelder via SQLMonster.com (forum@nospam.SQLMonster.com) writes:[color=blue]
> 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.[/color]

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/techinf...2000/books.asp

re: Delete recordsets with same Date and Line

On Tue, 17 May 2005 12:09:04 GMT, Julia Härtfelder via SQLMonster.com
wrote:
[color=blue]
>Hi All!
>
>I need help with a Statement!
>I am working with an Access2000 DB.
>I have the following Problem.[/color]
(snip)[color=blue]
>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.[/color]

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)

re: Delete recordsets with same Date and Line

That one really worked out!

Thank you so much. You are great!

Julia

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