Append Query, duplicates

[Scott12345]

Hi, here is my situation, I have a DB that tracks machine downtime (30 machines) per day. Several users will update this through the day. I created an append query that creates 30 dummy values and then opens up a form that displays all 30 values. The user can then go through these and change the ones they are responsible for. After they have updated this form, i have a delete query that searches for the dummy values and removes them.

My problem is that when a second user comes along and runs the append query it will create duplicate values, for example, if the first user changed 5 machines, when the append query is executed and the form is brought up, there will be 35 values, ie, 5 machines will be listed twice once with the previous users input and once with the dummy values. I would like the append query to search the table for existing values and add new ones only.

My main table is "Data", it contains all the information.

I also have a master table "Machines" that lists all the machines.

My SQL Code for my append query is:

INSERT INTO DATA ( [Machine ID], [Date], [Machine Inuse?], [Downtime Code 1], [Downtime Code 2], [Downtime Code 3], [Downtime Code 4] )
SELECT Machines.Machine, Forms![Enter Data Form]!Text16 AS Expr1, False AS InUse, 0 AS DT1, 0 AS DT2, 0 AS DT3, 0 AS DT4
FROM Machines
ORDER BY Machines.Machine;

My SQL Code for my delete query is:

DELETE Data.[Machine Inuse?], Data.[Downtime Code 1], Data.[Downtime Code 2], Data.[Downtime Code 3], Data.[Downtime Code 4], Data.[Downtime Code 5], Data.Comments
FROM Data
WHERE (((Data.[Machine Inuse?])=False) AND ((Data.[Downtime Code 1])=0) AND ((Data.[Downtime Code 2])=0) AND ((Data.[Downtime Code 3])=0) AND ((Data.[Downtime Code 4])=0) AND ((Data.[Downtime Code 5])=0) AND ((Data.Comments) Is Null));

Thanks for your help!

[MindBender77]

My main table is "Data", it contains all the information.

I also have a master table "Machines" that lists all the machines.

My SQL Code for my append query is:

INSERT INTO DATA ( [Machine ID], [Date], [Machine Inuse?], [Downtime Code 1], [Downtime Code 2], [Downtime Code 3], [Downtime Code 4] )
SELECT Machines.Machine, Forms![Enter Data Form]!Text16 AS Expr1, False AS InUse, 0 AS DT1, 0 AS DT2, 0 AS DT3, 0 AS DT4
FROM Machines
ORDER BY Machines.Machine;

My SQL Code for my delete query is:

DELETE Data.[Machine Inuse?], Data.[Downtime Code 1], Data.[Downtime Code 2], Data.[Downtime Code 3], Data.[Downtime Code 4], Data.[Downtime Code 5], Data.Comments
FROM Data
WHERE (((Data.[Machine Inuse?])=False) AND ((Data.[Downtime Code 1])=0) AND ((Data.[Downtime Code 2])=0) AND ((Data.[Downtime Code 3])=0) AND ((Data.[Downtime Code 4])=0) AND ((Data.[Downtime Code 5])=0) AND ((Data.Comments) Is Null));

Thanks for your help!

You could add criteria to your append query that will append only values that are not equal to dummy values.

Bender

[Scott12345]

I don't quite understand, if i were to only append values that are not equal to my dummy values wouldn't it append everything? because immediately after the form is updated i run a delete query to clear out the dummy values.

[Stewart Ross]

Hi Scott. Didn't we cover this in your previous thread - http://www.thescripts.com/forum/thread781299.html? If you let multiple users do the append you will end up with duplicates. If this proves a problem you will need to either stop using the append and just go back to adding the machine date lines manually, or use a more complex form of query which does have criteria to exclude such duplicates as Bender said, or set relationships on your tables that prevent duplicates from occurring.

-Stewart

[Scott12345]

Im looking for a query that searches through the table for machines that have data cooresponding to a specific date, the append query would then disclude that machine.