Update or Append Query - Help me please! (1 Viewer)

[SarahO]

Hi,
I am working on a scheduling database and have got stuck with what I thought would be a simple update or append query (It probably is very simple for non-newbies).
I am trying to create an update query to update the "Cell_ID" field in tbl_ScheduledAssays with the "Cell_ID" field from tbl_Machines.

The tbl_ScheduledAssays stores each assay that needs to be run. The tbl_Machines stores both the "Machine_ID" on which the assays are run and which "Cell_ID" that machine belongs to.

Some assays have a "Machine_ID" assigned up front as they have to be run on a certain machine, however other assays can be run on any machine so are assigned "Machine_ID" 51 which is a blank.

For all records in the tbl_ScheduledAssays table where the machine ID is <>51 I want the Query to check the "Machine_ID" and read from the tbl_Machines which "Cell_ID" that machine is in and then add that "Cell_ID" to the "Cell_ID" in the tbl_ScheduledAssays table.

I hope this makes sense! I have tried to search the forum but everthing I have tried so far has failed. Have also tried to do an append query to no avail.

 

[GohDiamond]

Append queries are for appending <NEW> records to an existing table. Update Queries are used to change/edit/update existing records in thoses tables. This was a confusion when I got started on databases as well.

For Example:
I have a record for James Bond ID#007, but this record is not complete since he has added several specialties in handguns and other advanced technologies. Do I Append the missing information or Update it.

Looking at my first statement I see that I already have the RECORD in the table so I guess I'm going to Update the fields that indicate his increased proficiencies.

On the other hand I don't yet have a record for Austin Powers, so I would append any information I have about him to the table.

Make sure there is a related field between the two tables, use that to link the tables in a query;

This may help in deciding which process to perform in future.


You want to update the table Scheduled Assays joined with the tbl_Machine based on the Machine ID, set the tbl_ScheduledAssays' Cell ID to the tbl_Machines' Cell ID where the value of the tbl_ScheduledAssays' Machine ID is not "51"

the SQL looks like this:

UPDATE tbl_ScheduledAssays INNER JOIN tbl_Machines ON tbl_ScheduledAssays.[Machine ID] = tbl_Machines.MachineID SET tbl_ScheduledAssays.[Cell ID] = [tbl_Machines].[cell id]
WHERE (((tbl_ScheduledAssays.[Machine ID])<>"51"));


I hope that I made the right sense out of your explanation.

Goh

 

[SarahO]

Thanks for explaining the difference between the append & update queries. This makes sense now. I'll give the SQL a go and let you know how it goes.
Cheers
SarahO