codequery error?

[Jen0dorf]

Hi

progressing on the project .

Now trying to update a foreign key using the code below

UPDATE tblDeceased SET fk_GraveID = tblGrave.GraveID
WHERE tblDeceased.GraveNo=tblGrave.GraveNo AND tblDeceased.Plot=tblGrave.Plot;

So I'm updating the field fk_GraveID in the table tblDeceased with the value from the table tblGrave and the field GraveID

The criteria is that GraveNo and Plot fields must match as each plot can have graves no 1-200 but each plot is designated by a specific letter ie you can have plot A grave 2 and then PLot B grave 2

when I run the query I get the error

Enter Perameter Value
tblGrave.GraveID



InHTML I would suspect a typo but can see nothing

is the AND not valid?

thanks

Ian

[qa12dx]

there should be a 'join' statement in your sql between
tblDeceased and tblGrave

[orange]

As qa12dx has said, when you are designing a query with multiple tables, you will have joins between PK and FK field in related table.
Can you show us your latest relationships window showing tables and relationships?

[John_G]

You have to include the table tblGrave after the UPDATE as well:

UPDATE tblDeceased, tblgrave SET fk_GraveID = tblGrave.GraveID
WHERE tblDeceased.GraveNo=tblGrave.GraveNo AND tblDeceased.Plot=tblGrave.Plot;

You don't need an explicit JOIN in this case because your WHERE clause does the same thing.

[Jen0dorf]

Hi

as usual thanks for all the help.

I've spent the morning trying to understand the update code and replicate it. I now have some understanding.

However I do have one area of confusion.

The code kindly supplied and the one I created both work - a step forward.

However when I update I get the message:

You are about to update 55977 row(s)

table tblDeceased has 39201 records

table tblGrave has 21542 records

(this is because graves can have multiple occupents.)

So in the table tbl graves GraveId records go from 0 to 21542 as expected as an autonumber

So as in the table tblDeceased I have 39201 records surely I would expect 39201 records to be updates?

Why do I see updating 55977 rows

Thought about ignoring it and seeing if it did update but I'd liketo understand what is happening.

I've attached a dump of the relationship map, at the moment I'm only concerned with tblgraves and tbldeceased.

thanks

Ian

[John_G]

Within your table tblGrave, is the combination of Plot+GraveNo unique? If so, you don't need to worry about the join in your UPDATE statement - a DLookup() would work just as well, and be easier to understand IMO.

You shouldn't have Plot and GraveNo in tblDeceased. They are redundant (and violate the normalization rules). Since the same information is in tblGrave and is "pointed to" by fk_GraveID, fk_GraveID is all you need in tblDeceased.

[Jen0dorf]

HI

thanks for the hints,

In reality I've had terrible issues importing data from one table into 4 and over the past weeks have added fields to try to sort the issues out. I realise I willhave to delete the duplicated fields.

Thanks to a kind post yesterday which I cant see on the forum all my problems - OK current problems -are solved. It seemed I added a lookup to the table field at some point which meant my updates didn't show. I delteted the updates and now I am making progress.

Once all the data is in the tables and it's checked I'm deleting the duplicated fields.

Yes the Plot and GraveNo make a unique combination so I will lookup Dlook()

thanks

Ian