delete records

[basilyos]

hello

i have table named tbl_edbarat_1 contains 3 columns (id - pid - tid)
i want to delete the records
where the tid=0 and DCount("*", "tbl_Edbarat_1") > 1

i try this

If DCount("*", "tbl_Edbarat_1") > 1 Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_Delete_Empty_Edbarat_01_Records"
    DoCmd.SetWarnings True
Else
End If

but this will delete all records where tid = 0 because the query is set to delete all the records with tid = 0


any help???

 

[namliam]

Not sure why you have the DCOUnt in the where clause?
The query is doing what you are telling it to do isnt it?
So what is the problem?

 

[basilyos]

maybe i didnt explain my problem well

when i save a new client automaticly it will save a record in the above table with tid=0
then this table will be used in another form and the tid will be 1 then 2 then 3

so i want to delete the records where tid=0 if there is more than one record
if there is just one record so don't delete it

id - pid - tid
1 - 1 - 0
2 - 1 - 1
3 - 1 - 2
4 - 2 - 0
5 - 3 - 0
6 - 4 - 0
7 - 4 - 1

so after deleting the records where tid=0 and dcount(tid)>1

the records will be

id - pid - tid
2 - 1 - 1
3 - 1 - 2
4 - 2 - 0
5 - 3 - 0
6 - 4 - 1

 

[namliam]

Your sample seems kind off broken, why did 6-4-0 disappear and become 6-4-1
why does 1-1-0 disappear when 4-2-0 and 5-3-0 dont?

 

[basilyos]

6-4-0 i typed wrong it's 7-4-1

and 1-1-0 disappear
4-2-0 and 5-3-0 dont

that's what i want to do

because the client (pid) 1 have more than one records so i can delete the record 1-1-0
but the client(pid) 2 just have one record so i dont want to delete 4-2-0

when the client(pid) 2 get another record so for example
id - pid - tid
8-2-1

at that moment i want to delete the 4-2-0



the record with tid = 0 is empty but i needed because when i create an invoice for the client i want the invoice to take number 1 for him so it's (last tid =0)+1 then it will be (last tid=1+1) / (last tid=2+1)

so i dont want the record where tid = 0 when the client have more than one invoice it will be useless

if he dont have any invoice i need this record to be existed

so this is why 1-1-0 disappear and 4-2-0 / 5-3-0 dont


i hope that i explained well my problem

 

[namliam]

I would not use DCount, instead use two queries, or a nested query.

Dont think you understand DCount, or how it works... In this case a simple query is enough I think, something like

Delete from Yourtable
where tid = 0
and pid in (select pid from yourtable where tid > 0)

 

[basilyos]

ok thanks so much finally it works