Delete query using linked tables

[MatMac]

Hi Folks

I have a problem I just can't crack, although I'm sure the solution is straightforward to those in the know!

I have two tables - "Client" and "Assessment". Each assessment belongs to a specific client, with many assessments to one client. Each record in "Assessment" thus contains a numeric field "Client_ID" as a foreign key linking it to a particular "Client" record.

When I delete records from "Assessment", I want to also delete the corresponding "Client" records if and only if the "Assessement" table then contains no assessments for those clients.

To put it another way, I want to delete: All Clients that don't have any assessment records.

Many thanks.

 

[Len Boorman]

The way I do this is as follows
I will refer to master and child tables for your Client and Assessment

Add delete flag to master table, (yes/No whatever and set default.)

Union query

Select Master.PK
FROM Master

UNION ALL

Select Child.FK
FROM Child;

Now create a find duplicates over this query and tweak so that it is a find Non Duplicates (set count criteria in query to 1)

Set delete flag to True in Master where PK appears in Non Duplicates query

Run delete query where delete flag is true.

Bit of a round the houses but gets there.

Alt is to use non duplicates query as source for a form that would allow you to see them and set the delete flag manually.

HTH

Len B
.

 

[MatMac]

Delete Query

Len and Pat - Many thanks for your replies.

Len - I'll have a think about doing it that way.

Pat - Yes, this is the way I've been trying without success to do it. My problem is that I can get the subselect right. Can you please advise me on how to do that. Many thanks.

MatMac

 

[MatMac]

Thanks.

 

[JiTS]

Hi,

I have the same problem... kinda...

Problemsituation (see attachment):
I have a mainform (frmContracten) and a subform (fsubContractweken). These forms are connected to the tables tblContracten and tblContractweken.

The primary key in both tables is 'Contractnummer'.

Problem:
If I delete a contract in the mainform, that record will be deleted in the table tblContracten. But the information of that record in tblContractweken is still there.

Question:
How to link the tables with each other and how to delete a record with the same 'Contractnummer' in both tables?

Please a detailed answer or an example because queries are not my best 'thing' in Access.

Thanks in advance,

JiTS

 

[JiTS]

Pat,

Thanks for your reply.

It is a very good advice to apply in my database.
I am sure it will be useful for other users who are working with this forum.

Two thumbs up!


*SMiLE*
JiTS