Delete query help

[Opal]

I have some questions about delete queries.

I have two tables where data is input from a bound form. I want
to be able to run a query whereby common data is written over
to a common table and the data in the two original tables is deleted.

For example, one shift has an issue with a part in a process, another
shift has a similar issue. Each records the issue to their shift
table
via the form. The query seeks out the common problem(s) and
writes the common problem to one table, deleting the data from
the original table. It may be days or weeks before each shift
shares the same problem, so the data needs to stay in the orignal
table until a commonality arises from running the query and it can
be deleted.

I thought that a delete query would solve the issue, but I can't quite
see how to make it work for my situation. Can anyone offer any
assistance?

 

[John Nurick]

Hi Opel,

It sounds as if you need three queries: an append query that joins the
two shift tables and writes the common records to the common table,
followed by two delete queries.

The first delete query would join the common table and one shift
table, and delete records from the latter that have counterparts in
the former (i.e. shift records that have been appended to the common
table). The second delete query would do the same with the other shift
table.

(Obviously I don't know all the circs, but your three-table design
seems unnecessary: normally one would put all the shift records into
one table - with a field to record which shift they belong to). Then
one could use what is in effect a Find Duplicates query to identify
'common' data without having to move it to another table or delete any
records.)

 

[Opal]

Hi John,

I had thought of that, each shift wanted their own unique auto-number
field
and two tables seemed to be the simpler way to go to achieve this
request.
I am still quite a newbie at a lot of this and sometimes still think
in "excel-
mode" when creating my databases.

I was trying to work out an SQL statement using DELETE DISTINCTROW
to achieve the desired results. I already have a query that joins the
two
tables with common data. This is brought into another form so that
the
users can "see" that an issue has come up that is common between
the two shifts. From there they issue a request to another group of
users to create a countermeasure for the common problem. So in
essence I do want to remove duplicate data....I will have to give this
some more thought.....