I am attempting to create a reasonably complex SQL query within MS Access. I'll explain what i'm trying to achieve first, including table and column names, then i'll show you the non-functioning query I have:
I have 2 tables. One called StudentStatus which contains non-primary columns; BuiltDate (Date/Time) and BuildNo (Number). The other table is called dbo_builds_progress_tbl and has the columns serial_num (Number) and a Date/Time column called end_d.
I wish to take values from end_d and put them into or update BuiltDate where a value in BuildNo is equal to a value in serial_num. So the only values that will change will be BuildNo within StudentStatus and only if there is a match between the BuildNo in StudentStatus and the serial_num in dbo_builds_progress_tbl - in which case it will update the BuiltDate to the corresponding end_d
I explained that twice, I know.
I have produced this query to do it:
UPDATE StudentStatus, dbo_builds_progress_tbl SET StudentStatus.BuiltDate = dbo_builds_progress_tbl.end_d
WHERE StudentStatus.BuildNo = dbo_builds_progress_tbl.serial_num;
This produces the error "Type mismatch in expression"
Help appreciated
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.