Update Query Issue while splitting a field.

[aaronvb]

Hi there,

I'm currently trying to fix up a database that has had many different
people work on it and therefore is confusing me no end.

Currently i am trying to update a field, [Person ID] in the table
[CAST] .

[Person ID] is a numeric field and is linked to the [Personnel Tbl]
(its the PK)
this needs to be updated according to another field [By Whom] in the
[CAST] table, which is a text field.

[By Whom] is in the format of Surname, First Name

so what i need to know is how do i write an update query that will
update [Person ID] to be a reference of the [Personnel Tbl] according
to the surname in the [By Whom] field.

I hope this makes sense and appreciate any help i can get, my head is
getting sore!

Cheers, Aaron

[Tom van Stiphout]

On 5 Dec 2006 19:56:28 -0800, aa*****@aapt.net.au wrote:

Your explanation makes sense, but it also shows a VERY BAD database
design.
ByWhom should be replaced by a PersonID. This works towards the power
of relational databases. Work on that first. Other issues will start
falling into place.

-Tom.

>Hi there,

I'm currently trying to fix up a database that has had many different
people work on it and therefore is confusing me no end.

Currently i am trying to update a field, [Person ID] in the table
[CAST] .

[Person ID] is a numeric field and is linked to the [Personnel Tbl]
(its the PK)
this needs to be updated according to another field [By Whom] in the
[CAST] table, which is a text field.

[By Whom] is in the format of Surname, First Name

so what i need to know is how do i write an update query that will
update [Person ID] to be a reference of the [Personnel Tbl] according
to the surname in the [By Whom] field.

I hope this makes sense and appreciate any help i can get, my head is
getting sore!

Cheers, Aaron

[aaronvb]

Your explanation makes sense, but it also shows a VERY BAD database

design.
ByWhom should be replaced by a PersonID. This works towards the power
of relational databases. Work on that first. Other issues will start
falling into place.

Thanks for the reply Tom,
You're dead right, it is a wizard nightmare!

The main issue is that this database system is used on several
different sites
(all separate data) and the solution has to be automated. There is
heaps of
data as well. I have fixed it up so that all new data is written to
PersonID,
but need to get the old data from ByWhom to PersonID, which is hard
because one
is numerical and the other text.

I have tried changing PersonID to a text field and running a simple
update
query, this looks like it works but none of the reports work because
the
updated data is in text not a numerical reference to the PersonnelTbl.

I then tried a left split formula to separate the surname and insert
the PersonnelID according to the surname but this comes up with an
error message ( I can't remember what it is, will post in an hour or
so).

Any ideas would be great, i'm running in circles

Cheers Aaron.

[aaronvb]

Heres the SQL I've been playing with

UPDATE [Corrective Action Subform Table]

INNER JOIN [Personnel Tbl] ON [Corrective Action Subform Table].[Person
ID] = [Personnel Tbl].[Person ID]

SET [Corrective Action Subform Table].[Person ID] = [Personnel
Tbl].[Person ID]

WHERE ((([Corrective Action Subform Table].[Person ID])=0) AND
((Left([By Whom],InStr([By Whom]," ")-1))=[Personnel Tbl].[Surname]));

This for some reason returns 0 updates, if i remove the Person ID = 0
criteria I then get a message "Invalid Procedure Call".