By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

Append Query, duplicates

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO DATA ( [Machine ID], [Date], [Machine Inuse?], [Downtime Code 1], [Downtime Code 2], [Downtime Code 3], [Downtime Code 4] )
  2. 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
  3. FROM Machines
  4. ORDER BY Machines.Machine;
My SQL Code for my delete query is:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Data
  3. 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!
Mar 14 '08 #1
Share this Question
Share on Google+
4 Replies


MindBender77
100+
P: 234
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
Mar 14 '08 #2

P: 9
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.
Mar 14 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Mar 14 '08 #4

P: 9
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.
Mar 14 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.