Update a table with details from another

[lactaman]

Hi,

I have not used Access for over 10 years! I am using Access 2000 to look at tracking cow weight over time!

There are 2 tables:
- main table
- weight table

I want to set the main table's January weight to a value from the Animal weight table (logical expression):

UPDATE [main table]
SET [main table].Jan07 = [Animal weight].[Kg]
WHERE [main table].[Line no])=[Animal Weight].[Line no] AND
[Animal weight].[weigh date]= 10/01/2007;

I have tried several times to get this working, but it tends to prompt for the weight to be given as an input.

Any ideas?

many thanks

Lactaman

 

[Dennisk]

your tables do not appear to be joined, so the query use the text in square brackets as a prompt for input.

 

[georgedwilkinson]

Your design is not normalized. Also, see Dennis' comment--the query doesn't know what "Animal Weight" is because you didn't tell it.

 

[neileg]

Plus, you don't need to store data that already exists in the database. Just retrieve it with a select query when you need it.

 

[lactaman]

Cheers

Many thanks, back up and running now after joining the tables.