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
in case that is so, what data type is the field serial_num in the underlying database?