How to run update query routine

  • Thread starter Thread starter postman
  • Start date Start date
P

postman

Is it possible to run an Update query on all records in a table
automatically.

I have a database with about 120,000 entries with 4 sets of prices. I wish
to run a price matrix (update query) against this database. The price matrix
has 4 columns:
1.autonumber field.
2.query criteria:Between xxx And xxx to filter the records
3.3 columns by 50 rows (records) of prices to be applied in the update.
Otherwise will have to make a form with a combobox and manually apply the
query 50 times.
Is there a way to automate this process with a stepped
routine that will "blip" it in one go.
Thanks.
 
Hi,

From what you say it seems that the query criteria field contains string
values such as
Between 1 And 99

If that's the case I think you'll need to open a recordset on the table
containing the price matrix, and iterate through it. For each record in
the matrix, build and execute a SQL UPDATE statement that updates the
records in the main table that meet that criterion.

If there were two numeric fields in the price matrix, e.g.
1, 99
I'm pretty sure it would be possible to do the whole thing with one
query; the clever guys in microsoft.public.access.queries would know.
 
I'll check it out.
Thanks


John Nurick said:
Hi,

From what you say it seems that the query criteria field contains string
values such as
Between 1 And 99

If that's the case I think you'll need to open a recordset on the table
containing the price matrix, and iterate through it. For each record in
the matrix, build and execute a SQL UPDATE statement that updates the
records in the main table that meet that criterion.

If there were two numeric fields in the price matrix, e.g.
1, 99
I'm pretty sure it would be possible to do the whole thing with one
query; the clever guys in microsoft.public.access.queries would know.
Click to expand...
 
Back
Top