Complex Update Query 1

[ad2]

Hi,

I have over 500 records to update. Tere are 3 fields that will need to be updated, different values in one field per Subject ID. Must I run a separate update query for each record or is there a way to write the query to have them all updated?

Currently I have;

UPDATE patient SET patient.mom = "Y", patient.dad = "Y", patient.IGE = "54" WHERE (((patient.id)="1011"));

Is there a way to have something like:

UPDATE patient SET patient.mom = "Y", patient.dad = "Y", patient.IGE = "54" WHERE (((patient.id)="1011")) AND UPDATE patient SET patient.mom = "Y", patient.dad = "Y", patient.IGE = "32" WHERE (((patient.id)="0718"));

Thanks
AD2

 

[PWise]

from where are you getting the input information

 

[dhookom]

You could try something like the following.

UPDATE patient 
SET patient.mom = "Y", patient.dad = "Y", 
patient.IGE = IIf(ID="1011","54","32")
WHERE id IN ("1011","0718");

This might be ok for a one time requirement. If you plan on doing this frequently or changing the ID and IGE pairs, you might want to build a table to lookup the IGE based on the ID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ad2]

Thanks!

 

[ad2]

Hi dhookom,

Using your suggestion I can update two records at a time, whick is great. Would you know how I could update all 500 at once? Or at least in larger batches?

Ad2

 

[dhookom]

As per my suggestion "If you plan on doing this frequently or changing the ID and IGE pairs, you might want to build a table to lookup the IGE based on the ID."

You would need a table with a primary key of the ID and another field for the IGE values. You can then create an update query based on the two tables.

You might want to backup your database prior to performing any mass updates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ad2]

Oh yes, I see. Got it now. Thanks for all the help.

 

[ad2]

Dhookom,

I've just completed the query using the update table. Worked great. Thanks again for the suggestion, it was quite a time saver.

Ad2