Update Query

[LOgle0917]

I have 2 tables I need to update from my PosNum Table to my EmpStats Table
the position number. My tables are joined by the EmpID number. When I run
the query it says I am about to update 52 records. but then when I go into my
EmpStats table the Pos Number field is blank. My Pos Number table has data
in the Pos Number field. Why is this happening and how can I fix it?

 

[Guest]

Post your query SQL statement.

 

[LOgle0917]

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EMPSTATS].[Pos_Num];

Now it is saying Type Mismatch in expression

 

[LOgle0917]

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EmpStats].[pos_num];

I changed the PosNUM EmpID to match the EMPStats.EMP Id as far as Text, 10
char length. Now using the Update Qry it says I am about to update 52 records
and then there is nothing in the records with the above SQL Statement.

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EMPSTATS].[Pos_Num];

Now it is saying Type Mismatch in expression

Post your query SQL statement.

[quoted text clipped - 3 lines]

 

[LOgle0917]

Still trying to do this also when it says it is updating my EMPSTATS.Pos_Num
table it takes the data from my PosNumUpdateTable and leaves the Pos Num
field blank.

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EmpStats].[pos_num];

I changed the PosNUM EmpID to match the EMPStats.EMP Id as far as Text, 10
char length. Now using the Update Qry it says I am about to update 52 records
and then there is nothing in the records with the above SQL Statement.

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EMPSTATS].[Pos_Num];

[quoted text clipped - 6 lines]

 

[John Vinson]

Still trying to do this also when it says it is updating my EMPSTATS.Pos_Num
table it takes the data from my PosNumUpdateTable and leaves the Pos Num
field blank.

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EmpStats].[pos_num];

I changed the PosNUM EmpID to match the EMPStats.EMP Id as far as Text, 10
char length. Now using the Update Qry it says I am about to update 52 records
and then there is nothing in the records with the above SQL Statement.

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID = POSNUMUPDATE.
EMPID SET POSNUMUPDATE.POSNUM = [EMPSTATS].[Pos_Num];

[quoted text clipped - 6 lines]

EmpStats table the Pos Number field is blank. My Pos Number table has data
in the Pos Number field. Why is this happening and how can I fix it?

It sounds lke you want to update EMPSTATS - but your SET clause is
updating POSNUMUPDATE.

If you want to update the Pos_Num field in Empstats to the value found
in POSNUMUPDATE, you need to reverse the SET clause: it sets the
*lefthand* field to the value to the right of the equals:

UPDATE EMPSTATS INNER JOIN POSNUMUPDATE ON EMPSTATS.EMPID =
POSNUMUPDATE.EMPID SET [EMPSTATS].[Pos_Num] = POSNUMUPDATE.POSNUM;


John W. Vinson[MVP]