Query Help Needed

[Kirsti]

Hi, I have a table which contains 3 fields.



[Field1] is a persons name, [Field2] and [Field3] are both numbers.

There is a row for each name where [Field2] and [Field3] both contain zero.

However, there are also rows where [Field2 and [Field3] contain numbers.

If that is the case, I want to delete the row where [Field2] and [Field3] both contain zeros for that person.

I don't know how to do this without deleting the rows for people that do not have a second row against their name.

Clear as mud?

Thanks, Kirsti

[alansidman]

Firstly, create a backup of the table you wish to do the deletion against in case of an error.
Now in QBE, for Field2 and Field3, in the criteria, type IsNull. If I understand correctly, you want this to happen when both fields are null. Run the query to examine your results. If you are happy with with the selection, then open the query once again in design view and change it to a delete query. Run it. If you are happy with the results in your table, then delete the backup (or save it for a rainy day.)

[Kirsti]

Thanks for your help, the issue I have is that will delete all rows where there is a zero value in Field2 and Field3. If there is not an additional row of data with positive values in Field2 and Field3 against the persons name, the zero row needs to be displayed.

So my table looks something like this:

Field1 Field2 Field3
John 0 0
John 5 3
David 0 0
Mary 0 0

Therefore, because David and Mary don't have any subsequent row with valid data against their name, the zero row is valid for them. However for John, I need to delete the row with zeros in Field2 and Field3.

Does that explain it better?

Thanks, Kirsti

[jzwp11]

You could create a query that includes a count of the number of records for each person. You can do this with a subquery or with the DCount() function. The query would look like this:

query name: query3
SELECT Table1.field1, Table1.field2, Table1.field3, dcount("*","table1","field1='" & field1 & "'") AS RecordCount
FROM Table1;


Then you could create your delete query based on the above query:

DELETE Query3.field1, Query3.field2, Query3.field3, Query3.RecordCount
FROM Query3
WHERE (((Query3.field2)=0) AND ((Query3.field3)=0) AND ((Query3.RecordCount)>1));

If a person can have many records in your table that describes a one-to-many relationship, so technically speaking the people should be records in another table and then joined via a foreign key to the table from which you want to delete the records. With this setup you can delete the records in your table without impacting the basic information about the person. That structure would look like this:


tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

YourTable
-pkRecID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-field2
-field3

[Kirsti]

Hi again,

jzwp - thanks for your response. I couldn't get your DCount query working, but when I backtracked through my queries, I realised that I do have my tables structured as per your recommendation, however the problem has been caused through a UNION query.

Is there a way to write a union query that includes an Outer Join? If so, what would the syntax look like?

[Kirsti]

Ok - I have been over-complicating things for myself! I deleted the union query and created a select query with an outer join & got the result I needed. Thanks for all your help.

[jzwp11]

Glad to hear that you got it worked out! Good luck with your project.