Solved

I have an Append Query that is adding the data twice. Why would this be?

Posted on 2003-12-07
44
805 Views
Last Modified: 2012-06-21
I have an append query to update a table with certian information which the user chooses via form. When you run the query it adds the data twice. I cannot see why. Any ideas?
INSERT INTO Training ( EmplID, CertID, CompCatID )
SELECT Employee.EmplID, PositionCompetency.CertID, PositionCompetency.CompCatID
FROM Employee INNER JOIN (PositionCompetency INNER JOIN Certification ON PositionCompetency.CertID = Certification.CertID) ON Employee.PosID = PositionCompetency.PosID
WHERE (((PositionCompetency.CertID)=[Enter CertID to Add]) AND ((PositionCompetency.PosID)=[Enter PosID to Update]));


Code on click event of button of form to run the query.
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

    Dim stDocName As String

    stDocName = "UpdateCerttoTrain"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
   
End Sub

Cheers
Jill

0
Comment
Question by:Jillbo
  • 22
  • 22
44 Comments
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi JillBo,

Your Select must be returnining duplicate records, try using Select Distinct

SELECT Distinct Employee.EmplID, PositionCompetency.CertID, PositionCompetency.CompCatID
FROM Employee INNER JOIN (PositionCompetency INNER JOIN Certification ON PositionCompetency.CertID = Certification.CertID) ON Employee.PosID = PositionCompetency.PosID

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
I thought yeh this is easy but it never is, is it????
Still wants to duplicate the records.
Any other ideas??
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi jillbo,

Did you remember to remove the duplicates created by the first offending update?

Do you need the nested Inner Join, you dont seem to be referencing any fields in table Certification?
Or is table Certification an in between table?

INNER JOIN Certification ON PositionCompetency.CertID = Certification.CertID ??

Perhaps the Select distinct will work without this nesting.

Get it working as a select query before converting to update query.


SELECT Distinct
  Employee.EmplID,
  PositionCompetency.CertID,
  PositionCompetency.CompCatID
FROM Employee

INNER JOIN (PositionCompetency  ON Employee.PosID = PositionCompetency.PosID

WHERE (((PositionCompetency.CertID)=[Enter CertID to Add])
  AND ((PositionCompetency.PosID)=[Enter PosID to Update]));

Alan :)

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Jillbo,

If employees have more than one certification I would expect it to return more than one record for those employees even when using Distinct

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi alan,
Good to have you on board again.
I was testing new data and so the duplicated were all new.
I have just tried you SELECT code and there is a syntax error in JOIN and SQL keeps highlighting the ON part of the statement. There was an additional space which I have removed but it is still doing it.
Any ideas?
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jillbo,

Happy to have a quick look for you, my mail is in my profile, zip please as server will block mdb's, I only have acc2k version  here.

Might have to do a sub select, what is the policy regarding multiple certifications?

Alan :)
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jullbo,

Does this look right?

SELECT Employee.EmplID, PositionCompetency.CertID, PositionCompetency.CompCatID
FROM ([Position] INNER JOIN Employee ON Position.PosID = Employee.PosID) INNER JOIN PositionCompetency ON Position.PosID = PositionCompetency.PosID
WHERE (((PositionCompetency.CertID)=[Enter CertID to Add]) AND ((PositionCompetency.PosID)=[Enter PosID to Update]))
ORDER BY Employee.EmplID;

Paramater Inputs  CR8 , 3

Returns:
EmplID      Certification      Category
3      Abrasive Wheels      Technical
58      Abrasive Wheels      Technical
182      Abrasive Wheels      Technical
192      Abrasive Wheels      Technical
214      Abrasive Wheels      Technical
246      Abrasive Wheels      Technical


Append sql:
add the following line - line one.

INSERT INTO Training ( EmplID, CertID, CompCatID )


Alan  :)

0
 

Author Comment

by:Jillbo
Comment Utility
Sorry Alan,
What is the final SQL required to get the result that you just got?
Cheers
Jill

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jillbo,

About to head off home now,

Will pick it up there, think the problem is being caused by the lookup fields at table level, PosID and CertID


INSERT INTO Training ( EmplID, CertID, CompCatID )
SELECT Employee.EmplID, PositionCompetency.CertID, PositionCompetency.CompCatID
FROM ([Position] INNER JOIN Employee ON Position.PosID = Employee.PosID) INNER JOIN PositionCompetency ON Position.PosID = PositionCompetency.PosID
WHERE (((PositionCompetency.CertID)=[Enter CertID to Add]) AND ((PositionCompetency.PosID)=[Enter PosID to Update]))
ORDER BY Employee.EmplID;

alan :)
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jillbo,

Back on line, where we up to?

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
That was quick.
Check your email. It does work but there is no check to see if the Certification has already been added to the training table. So we need to put a check in there to stop duplicating the certifications.
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Cant get work mail at home, just added my home address to my profile temporarily.

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
This was all I sent.
So if you want to remove your personal email address please do.
Hi Alan,
It does work as long as the certification is not already in there.
I think what we need is a msg box that flashes up to say that the certification already exists for that position.

Could you help me with the code for this.
Along the lines of:
IF CertID and PosID already exist in table training Then
MSgBox "The certification already exists for this position"
Else
DoCmd.OpenQuery etc etc
But if it runs before the query how does it know? and if you run it after the
query it is too late unless you can do an UNDO cmd as the Else????
Your input on this would be greatly appreciated.
Cheers
Jill



0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Which form shall we use to do this?

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
The AddCerttoTrain Form is where the user adss the info.
Jill
0
 

Author Comment

by:Jillbo
Comment Utility
Is this going to work?
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Not sure yet Jill, it's got me a bit gazumped at the moment, hmm... :)

I'm trying to get my head around the process.

In english pseudocode:

Find all the employees who are competent to train in a given category and if their competency is not listed in the traning table then add it to the list. Does that sound correct to you?

Alan :)


0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Sorry I was unable to get this working last night.

Shoud be able to create a Find Unmatched Query but it does n't seem to work for some un-explicable reason.

This is as far as I got with this:

Input at parameter prompts CR8 and 3

Returns msg about to append 6 records

INSERT INTO Training ( EmplID, CertID, CompCatID )
SELECT Employee.EmplID, PositionCompetency.CertID, PositionCompetency.CompCatID
FROM (([Position] INNER JOIN Employee ON Position.PosID = Employee.PosID) INNER JOIN PositionCompetency ON Position.PosID = PositionCompetency.PosID) LEFT JOIN Training ON PositionCompetency.PosCompID = Training.CompCatID
WHERE (((PositionCompetency.CertID)=[Enter CertID to Add]) AND ((PositionCompetency.PosID)=[Enter PosID to Update]) AND ((Training.CompCatID) Is Null))
ORDER BY Employee.EmplID;



Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
I have a day off today so I will be back on this tomorrow. Thanks for your help ad catch up tomorrow.
Cheers
Jill


0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
The query runs fine as long as the certification does not already exist for a position. The idea of a message box is that if the certification already exists for a position then we need a msgbox to say that.

So what we are hoping for is :
A position requires certifications, a user needs to add the certifications to the position. If that certification already exists for a position and a user tries to add it again, then message box "Certification already exists for position, Please Undo"

Any clearer?
Cheers
Jill
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
Any chance of getting this cleared up so that I can close the question. I have increased the points to hopefully entice you.
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Just stuck a note in my Autoexec.bat

Sorry this had slipped my mind.
Gotta go out for a little while, will have another go at it later.
Have rebuilt my system since we last communicated, hope I still have the data.

Alan :)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
Thanks for coming back to me!!! If you need me to send anything then just let me know.
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Got up an hour early this morn to try and sort this out, the hour has gone, I'm still not there yet.

Help me get my head around this process. I'm feeling a bit like I have been looking at something, knowing I have to fix it but I cant find what is broken. As soon as I get the picture, I'll fix it. Sorry for being so thick, I just cannot see it at the moment Jill, dont want to dive in and start fixing things that aren't broken.


1... Switchboard
2... Choose Add new Cert to Positions and Training
3... Select a certification name from combo to populate the sub form
4... On the subform, scroll to bottom and add new position and category and check mandatory y/n

Part 4 is where the problem lies, is this correct.????
If the position and category are already in the subform resultset, then the operation needs to be cancelled and the user should be alerted that the chosen position and category are already in the list for the current CertID, maybe even cancel the update and move to the matching record for the user.

Yes ?
sorta kinda ?
missed the point completely ?

Hope to clear this up soon, I have to go to work now.
My previous attempts have been centred around a bulk update, but I think this may not be what you want. I saw the big Add Cert to training Button and thought that is where the code must go.

All things are possible.

Will pick it up again tonight.

Alan :)


0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
Thanks for your dedication! Sorta got it right. If the user tries to add a CertID to a position that already has it, when they press the update to training button there should be a message box that flags that it already exists and to undo the entry. The problem we were having that it is in the middle of an append query that we need the message to flag. (I think this is what we were trying to achieve - I have got a bit woolly on it now aswell!)

Here if you need any help - not that I am much good - but I try!
Cheers
jill

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

I'll get it to you by Monday or die trying!

Alan :)

Thought For Today - 1/9/2004
Any man can work when every stroke of his hand brings down the fruit rattling from the tree to the ground; but to labor in season and out of season, under every discouragement, by the power of truth -- that requires a heroism which is transcendent. -- Henry Ward Beecher

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

In my verion of the data there are 64 duplicate records in Table!Training, they have been causing problems with my checksum when appending non existent records. Suggest you make a backup copy of the backend and then do something with the duplicates. This query will isolate them for you. Please note some of the duplicates have extra info in the fields that are not compared for duplicity.


SELECT Training.EmplID, Training.CertID, Training.CompCatID, Training.TrainingID, Training.DateAssessed, Training.HardCopyVerified, Training.HardCopy, Training.RefresherDate, Training.CourseBookedDate, Training.Comments
FROM Training
WHERE (((Training.EmplID) In (SELECT [EmplID] FROM [Training] As Tmp GROUP BY [EmplID],[CertID],[CompCatID] HAVING Count(*)>1  And [CertID] = [Training].[CertID] And [CompCatID] = [Training].[CompCatID])))
ORDER BY Training.EmplID, Training.CertID, Training.CompCatID;

Just workin on he bulk append of all records that should be in Table!Training but are not, some 4121 records though this figure may be a little off because of duplicity issues.

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
Thanks for this I will give it a test run on Monday. Cheers
Jill

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

First let me express my gratitude to you for asking this question, I learn something that is going to change the way I think JET SQL from now on.

I found out that you can alias multiple sub-selects in on sql script and then JOIN on the sub-selects, this is so cool Jill :)

The following will bring your table Training into sync with each individuals actual competencies. suggest you make a copy of the back-end before running this script.

Alan :)



Insert Into Training (EmplID, CertID, CompCatID)
Select sqlOne.EmplID, sqlOne.CertID, sqlOne.CompCatID From
(SELECT DISTINCT
  E.EmplID,
  P.CertID,
  P.CompCatID
FROM Employee AS E
  INNER JOIN (PositionCompetency AS P
  INNER JOIN Certification AS C ON P.CertID=C.CertID) ON E.PosID=P.PosID) as sqlOne
 
 LEFT JOIN (Select Distinct EmplID, CertID, CompCatID From Training) as sqlTwo
    ON  (SQLOne.CertID = SQLTwo.CertID)
    And  (SQLOne.EmplID = SQLTwo.EmplID)

WHERE (((SQLTwo.EmplID) Is Null) AND ((SQLTwo.CertID) Is Null) AND ((SQLTwo.CompCatID) Is Null))


0
 

Author Comment

by:Jillbo
Comment Utility
Hi alan,
I ran the above SQL query and it added 12 records to my table. Now as I said before I am very fuzzy on where we were with this so my question is -
Was this to replace the update append query on the form or was this just to get all the records straight?
Sorry for being vague
Cheers
Jill
0
 

Author Comment

by:Jillbo
Comment Utility
Also very strangely everytime I try and save this query Access crashes and won't save it. I will try on another computer and let you know the outcome.
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Not getting any crashes here, however enlisted some assistance with your append query from Hans & Nico and this is what we have come up with to bring your training table into sync. This should be ran after an new competency is added.



INSERT INTO Training ( EmplID, CertID, CompCatID )
SELECT sqlOne.EmplID, sqlOne.CertID, sqlOne.CompCatID
FROM [SELECT DISTINCT
  E.EmplID,
  P.CertID,
  P.CompCatID
FROM Employee AS E
  INNER JOIN (PositionCompetency AS P
  INNER JOIN Certification AS C ON P.CertID=C.CertID) ON E.PosID=P.PosID]. AS sqlOne LEFT JOIN [Select Distinct EmplID, CertID, CompCatID From Training]. AS sqlTwo ON (sqlOne.EmplID = sqlTwo.EmplID) AND (sqlOne.CertID = sqlTwo.CertID) AND (sqlOne.CompCatID = sqlTwo.CompCatID)
WHERE (((sqlTwo.EmplID) Is Null) AND ((sqlTwo.CertID) Is Null) AND ((sqlTwo.CompCatID) Is Null));


Regarding the issue of alerting the user that they have entered a duplicate value. Without knowing the EmplID, we have no way of confirming this. Best we can do is to run the FindDuplicates query I posted previously.

Hope you are having some success with your crashing problem.


Alan :)

0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan, Sorry I have been away. Just tried above query and it crashes aswell!
I will keep working on that problem.
Now remind me what is the above query trying to achieve???
I have so many questions on this database it is going to drive me nuts aswell as you!!!
Cheers
Jill
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
So far I have added the duplication query to the form so that when the user adds a certification they need to run the query to check it has not duplicated. Not the best solution but it is working.
If you can explain the other query that you guys all worked on I will close this question and go straight onto the next one!
Cheers
Jill
0
 

Author Comment

by:Jillbo
Comment Utility
Me again!!
Just to let you know the original append query is still acting strange.
If I add a Certification to a position and append to the training table it says you are about to append 4 records. Great.
However if you then go into it and add the same details it says you are about to append 8 records. Then run the query to  highlight the dupicates and it shows the 8 records so you delete them.
However if you run it again it says you are about to append 16 records even though you have deleted all of the duplicates! I guess this is where we started!!
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Jill,

Bit caught up with some stuff at the moment, will have to get back to you.

the idea was when a record is added using your subform ([AddCerttoTrain Subform]) that Training should be automatically synchronised with the new data in tables( Employee, PositionCompetency and  Certification).



([AddCerttoTrain Subform]) After Update

Private Sub Form_AfterUpdate()
Dim sql As String
sql = "INSERT INTO Training ( EmplID, CertID, CompCatID )"
sql = sql & " SELECT sqlOne.EmplID, sqlOne.CertID, sqlOne.CompCatID"
sql = sql & " FROM [SELECT DISTINCT E.EmplID, P.CertID, P.CompCatID FROM Employee AS E"
sql = sql & " INNER JOIN (PositionCompetency AS P"
sql = sql & " INNER JOIN Certification AS C ON P.CertID=C.CertID) ON E.PosID=P.PosID]. AS sqlOne"
sql = sql & " LEFT JOIN [Select Distinct EmplID, CertID, CompCatID From Training]. AS sqlTwo"
sql = sql & " ON (sqlOne.EmplID = sqlTwo.EmplID) AND (sqlOne.CertID = sqlTwo.CertID)"
sql = sql & " AND (sqlOne.CompCatID = sqlTwo.CompCatID)"
sql = sql & " WHERE (((sqlTwo.EmplID) Is Null)"
sql = sql & " AND ((sqlTwo.CertID) Is Null)"
sql = sql & " AND ((sqlTwo.CompCatID) Is Null))"

DoCmd.RunSQL sql

' Next step was to check if this caused a duplicate to occur and remove it if it did then delete the last record added in the subform.





'Notes
'===================================================
' Build this sql
'INSERT INTO Training ( EmplID, CertID, CompCatID )
'SELECT sqlOne.EmplID, sqlOne.CertID, sqlOne.CompCatID
'FROM [SELECT DISTINCT E.EmplID, P.CertID, P.CompCatID FROM Employee AS E
'  INNER JOIN (PositionCompetency AS P
'  INNER JOIN Certification AS C ON P.CertID=C.CertID) ON E.PosID=P.PosID]. AS sqlOne LEFT JOIN [Select Distinct EmplID, CertID, CompCatID From Training]. AS sqlTwo ON (sqlOne.EmplID = sqlTwo.EmplID) AND (sqlOne.CertID = sqlTwo.CertID) AND (sqlOne.CompCatID = sqlTwo.CompCatID)
'WHERE (((sqlTwo.EmplID) Is Null) AND ((sqlTwo.CertID) Is Null) AND ((sqlTwo.CompCatID) Is Null));




End Sub
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
Thanks for coming back to this, what do I do now? Which stuff should I be using this last code or the previous stuff???
Cheers
Jill
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hey Jill,

gotcha at last we meet.
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Jill,
 couple of questions...

Did you sort out the duplicates?
What have you currently got in the afterupdate event for AddCerttoTrain Subform?

Alan :)
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Jill,


Your mail seems to be taking a long while to get to you.

If you are using Outlook:

Tools > Options > MailSetup Tab > Send/Receive button > Schedule automatic send/receive every 1 minutes

Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
Sorry I am not at work and so running around the house doing other stuff at same time. I need to look at the db which is at work to know what is on the event.
The duplicate code does work it is the append query that doesn't.
Cheers
Jill
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
I tried your code in the afterupdate event of the form and it was doing some very strange stuff. I only added as far as the notes. Should I have more???

Also what am I supposed to do with the code that you 3 amigos came up with??

Confused now!!!
Cheers
Jil
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
Comment Utility
Hi Jill,

You should have this in the AfterUpdate Event for AddCerttoTrain Form
Using the data you sent me, this process appends some thousands of records. You should make a backup of the backend data before running this.

Private Sub Form_AfterUpdate()
Dim sql As String
sql = "INSERT INTO Training ( EmplID, CertID, CompCatID )"
sql = sql & " SELECT sqlOne.EmplID, sqlOne.CertID, sqlOne.CompCatID"
sql = sql & " FROM [SELECT DISTINCT E.EmplID, P.CertID, P.CompCatID FROM Employee AS E"
sql = sql & " INNER JOIN (PositionCompetency AS P"
sql = sql & " INNER JOIN Certification AS C ON P.CertID=C.CertID) ON E.PosID=P.PosID]. AS sqlOne"
sql = sql & " LEFT JOIN [Select Distinct EmplID, CertID, CompCatID From Training]. AS sqlTwo"
sql = sql & " ON (sqlOne.EmplID = sqlTwo.EmplID) AND (sqlOne.CertID = sqlTwo.CertID)"
sql = sql & " AND (sqlOne.CompCatID = sqlTwo.CompCatID)"
sql = sql & " WHERE (((sqlTwo.EmplID) Is Null)"
sql = sql & " AND ((sqlTwo.CertID) Is Null)"
sql = sql & " AND ((sqlTwo.CompCatID) Is Null))"

DoCmd.RunSQL sql

End Sub

Strange things?

' Next step was to check if this caused a duplicate to occur and remove it if it did then delete the last record added in the subform.

' When we are sure that no duplicates exist, we can then open a recordset to check if the update we just ran caused a duplicate entry. If it did we can delete it.

' We cannot implement the checking for duplicates as a test until there are no duplicates to begin with.


Alan :)
0
 

Author Comment

by:Jillbo
Comment Utility
Hi Alan,
The code appended 46 records. I checked for duplicates and nothing came up. I guess this means that code is all good.

I then ran some new tests with new certs etc etc and these seem to work also.
If the Cert already exists it justs says 0 records to append.
Gonna put in a custom msgbox instead of the access one now.
Thanks for all of our help Alan, and fingers crossed I can now close this form and get on to the next difficult one (and believe me I have many!!!)

Cheers to you.
Jill
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now