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

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

JillboAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
Sorry Alan,
What is the final SQL required to get the result that you just got?
Cheers
Jill

0
Alan WarrenApplications DeveloperCommented:
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
Alan WarrenApplications DeveloperCommented:
Hi Jillbo,

Back on line, where we up to?

Alan :)
0
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
Hi Jill,

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

Alan :)
0
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
Hi Jill,

Which form shall we use to do this?

Alan :)
0
JillboAuthor Commented:
The AddCerttoTrain Form is where the user adss the info.
Jill
0
JillboAuthor Commented:
Is this going to work?
Cheers
Jill
0
Alan WarrenApplications DeveloperCommented:
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
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
JillboAuthor Commented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
Hi Alan,
Thanks for coming back to me!!! If you need me to send anything then just let me know.
Cheers
Jill
0
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
Hi Alan,
Thanks for this I will give it a test run on Monday. Cheers
Jill

0
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
JillboAuthor Commented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
Hey Jill,

gotcha at last we meet.
0
Alan WarrenApplications DeveloperCommented:
Jill,
 couple of questions...

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

Alan :)
0
Alan WarrenApplications DeveloperCommented:
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
JillboAuthor Commented:
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
JillboAuthor Commented:
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
Alan WarrenApplications DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JillboAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.