Update Query

[2wistd]

I'm sure this has been asked before but I am trying to combine the fields from several different tables into one with the same key field. Can't figure out the criteria for it to update using the names

I have the update query set up

Field: Training
Table: permpartytraining
Update To: [ExternalTable].[CompletionData]
Criteria: [permpartytraining].[persName] = [ExternalTable].[persName]

this doesn't seem to work

 

[CJ_London]

you need to post your current query, not just the highlights

 

[2wistd]

Here is a image of the query.

 

[2wistd]

INSERT INTO permpartytraining ( ForceProtection )
SELECT ForceProtection.LastCompleteDate
FROM ForceProtection
WHERE (([ForceProtection].[persName]=[permpartytraining].[persName]));

Still doesn't do the trick.
It has no problem without the 'WHERE' to put the records in the table, but not aligned with names, just added to the bottom.

 

[CJ_London]

I'm somewhat confused - your image is for an update query but your code is for an insert query

Can you explain in simple language what you want to do

 

[2wistd]

Okay I'll try to do this as least confusing as possible.

I have one table (permpartytraining) which has:

User Name: CourseA: CourseB:
Jane Doe
 
then I have another table labeled CourseA which has
User Name:     DateCompleted (and many more fields)
Jane Doe         Jan 14, 2014
Joe Doe           Feb 1, 2014
 
I also have the same for CourseB
User Name:       DateCompleted (and many more fields)
Jane Doe           Dec 18, 2013
Joe Doe            Nov 13, 2013
 
I want the query to add the CourseA and CourseB (and many more later) to the permpartytraining table.
User Name:            CourseA:               CourseB:
Jane Doe                Jan 14, 2014         Dec 18, 2013
Joe Doe                  Feb 1, 2014          Nov 13, 2013

I hope the formating isn't killed when I post this!

 

[CJ_London]

Well the first problem is your db is not constructed very well which makes any solution difficult.

And why do you need to add it to a table? - you are duplicating data and it goes against the ethos of a database - you can just have a query.

You also have separate tables for couse A and course B, they should be one table with an additional column for the course

tblCourses
CourseName    UserName    DateCompleted
CourseA       Jane Doe         Jan 14, 2014
CourseA      Joe Doe           Feb 1, 2014
CourseB       Jane Doe         Dec 18, 2013
CourseB       Joe Doe            Nov 13, 2013

Then all you need is a simply crosstab query with coursename as the column heading, username as the row heading and (first) datecompleted as the value

 

[2wistd]

I see what you are saying about the query.

The reason why I have different tables for each course is because they come from Excel Spreadsheets that I have no control over. It then goes through a few queries to set up the names, dates etc to match my existing data.

I might be able to add a courseName field to each import to create one table. just more programming to do!

 

[2wistd]

courseName = "CourseA"
 
CourseNameUpdate = "UPDATE CombinedTraining SET CombinedTraining.courseName = " & [COLOR=red]courseName[/COLOR] & " WHERE (((CombinedTraining.courseName) Is Null))"

currently this shows up as

UPDATE CombinedTraining SET CombinedTraining.courseName = [COLOR=red]CourseA[/COLOR] WHERE (((CombinedTraining.courseName) Is Null))"

How can I have it show up as

UPDATE CombinedTraining SET CombinedTraining.courseName = [COLOR=red]"CourseA"[/COLOR] WHERE (((CombinedTraining.courseName) Is Null))"

With the quotes, otherwise it doesn't fill anything in, or asks what "CourseA" is.

 

[CJ_London]

.... = [COLOR=magenta]'"[/COLOR] & courseName & [COLOR=magenta]"'[/COLOR] WHERE ....

add single quotes

 

[2wistd]

.... = [COLOR=magenta]'"[/COLOR] & courseName & [COLOR=magenta]"'[/COLOR] WHERE ....

add single quotes

Thanks I reversed the single quotes!