Update query has broken

[eskapades]

Hi all,

I hope someone might be able to help me! I'm still quite basic with my Access skills. I have pasted the SQL versions of my queries - although I do not build them this way - so if you could help out a layman, it would be much appreciated.



I have given you all the information below – just in case something is off in addition to the update query itself. Sorry if it's too long!


I manage information an elearning course with MS Access 2003. We report on each user's completion status every quarter.

Each user record is stored on our 'Master List' and includes:

• User email address
• The code for the last module they completed
• The title of the last module they completed
• The date of the last module they completed

We receive the user learning statuses as 'raw data' spreadsheet. This data gives us ALL the modules completed by the user, not just the highest completed module e.g. module 1, 2, 3 4 & 5 rather than simply module 5.

My steps

1) I import this raw data into a new table (Raw Data) on the database.

2) I then user a select query to find the 'Highest Module Complete' for each user record.

· The user email is the unique identifier.
· The 'Learning Object' is the individual code for each separate module e.g. CODE1, CODE2, CODE3....
· The 'Sco Status' is the learning status: Complete, Incomplete, Not Started.

The criteria is the MAX OF 'Learning Object' and the 'Sco Status' of “Complete”:

SELECT [Raw Data].Email, Max([Raw Data].[Learning Object]) AS [MaxOfLearning Object], [Raw Data].[Sco Status]
FROM [Raw Data]
GROUP BY [Raw Data].Email, [Raw Data].[Sco Status]
HAVING ((([Raw Data].[Sco Status])="Complete"));


3) I then use another select query. This adds the module title and date completed to each user record.

I link the ‘Email’ from the first query to the ‘Email’ on the Raw Data table.
I like the ‘MaxofLearning Object’ to the ‘Learning Object’ on the Raw Data table.


SELECT [Highest Module Complete].Email, [Highest Module Complete].[MaxOfLearning Object], [Highest Module Complete].[Sco Status], [Raw Data].Title, [Raw Data].[Last Studied]
FROM [Raw Data] INNER JOIN [Highest Module Complete] ON ([Raw Data].[Learning Object] = [Highest Module Complete].[MaxOfLearning Object]) AND ([Raw Data].Email = [Highest Module Complete].Email);



4) I then create a make table with the data from the the 'Highest Module Complete with Title and Date' select query.

5) To update the ‘User Master List’ table records, I then run an update query.

I have a rule:

The user record can only be updated if the ‘Learning Object’ on the make table, is higher than the code already on the user record.

The two tables used in the query are linked by the field 'Email':


UPDATE [Make Table Highest Module Title and Date] INNER JOIN [User Master List] ON [Make Table Highest Module Title and Date].Email = [User Master List].Email SET [User Master List].last_module_completed_code = [Make Table Highest Module Title and Date]![MaxOfLearning Object], [User Master List].last_module_completed = [Make Table Highest Module Title and Date]!Title, [User Master List].last_module_completed_date = [ Make Table Highest Module Title and Date]![Last Studied]
WHERE ((([Make Table Highest Module Title and Date]![MaxOfLearning Object])>[User Master List]![last_module_completed_code]) AND (([User Master List].Email)=[Make Table Highest Module Title and Date]![Email]));



On our last reporting cycle, the query did not work. Although there were definitely records that needed updating, it said 'You are now about to update 0 rows'. There was no error message.

Can anyone see any missteps that I have made?

Any help you could give would be much appreciated. Thank you!

[orange]

I have not looked at your code thoroughly. I did reduce it to the following format

UPDATE [TBL1] INNER JOIN [TB2]
ON [TBL1].Email = [TB2].Email
SET
[TB2].last_module_completed_code = [TBL1]![MaxOfLearning Object]
, [TB2].last_module_completed = [TBL1]!Title
, [TB2].last_module_completed_date = [ TBL1]![Last Studied]
WHERE
((([TBL1]![MaxOfLearning Object])>[TB2]![last_module_completed_code])
AND
(([TB2].Email)=[TBL1]![Email]));

My feeling is that the Table to be updated (TBL2 in my sample) should be the first named table.
Also, it seems that your names are not consistent across tables -- this is not an issue provided you know which fields and data types are involved. It would be confusing for someone not intimately familiar with your data and table structures.

I haven't tried it but this might work

UPDATE [TBL2] INNER JOIN [TB1]
ON [TBL2].Email = [TB1].Email
SET
[TB2].last_module_completed_code = [TBL1]![MaxOfLearning Object]
, [TB2].last_module_completed = [TBL1]!Title
, [TB2].last_module_completed_date = [ TBL1]![Last Studied]
WHERE
((([TBL1]![MaxOfLearning Object])>[TB2]![last_module_completed_code])
AND
(([TB2].Email)=[TBL1]![Email]));

However, UPDATE queries change data, and if you are not clear on exactly what you are doing, you could make permanent changes. A good technique is to BACK UP your data before running UPDATEs.