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.
The combobox event procedure:
Private Sub Combo5_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
The UpdateQueries SQL for manual input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE = 10.99, dB_DVD_Prices.CBPRICE
= 5.7, dB_DVD_Prices.TBPRICE = 7
WHERE (((dB_DVD_Prices.NPRICE) Between 13 And 13.99));
The UpdateQueries SQL for Form Input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![SPRICE], dB_DVD_Prices.CBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![CBPRICE], dB_DVD_Prices.TBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![TPRICE]
WHERE (((dB_DVD_Prices.NPRICE)=[Forms]![Frm_NEW_PRICE_RANGE]![Combo5]));
The first above Manual Input works but I need to re-enter all the numbers to
match the table row's between xx And xxx lines in query design view then run
it. The numbers are scaler & depend on the between numbers, at moment I have
about 60 between criterias and 3 update field rows to match each between
criteria.
BUT when using the form the update query won't read the form's combobox
(between xxx And xxx)at all & throws the error
ata type mismatch in
criteria expression. Just won't pick it up. So I'm stuck at that point and
manually entering all these numbers.
I'll do as you suggest with the Form's AfterUpdate event code.
Do I replace the line:rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
With the on suggested by yourself, or add it in under?
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.
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.
The combobox event procedure:
Private Sub Combo5_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
The UpdateQueries SQL for manual input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE = 10.99, dB_DVD_Prices.CBPRICE
= 5.7, dB_DVD_Prices.TBPRICE = 7
WHERE (((dB_DVD_Prices.NPRICE) Between 13 And 13.99));
The UpdateQueries SQL for Form Input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![SPRICE], dB_DVD_Prices.CBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![CBPRICE], dB_DVD_Prices.TBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![TPRICE]
WHERE (((dB_DVD_Prices.NPRICE)=[Forms]![Frm_NEW_PRICE_RANGE]![Combo5]));
The first above Manual Input works but I need to re-enter all the numbers to
match the table row's between xx And xxx lines in query design view then run
it. The numbers are scaler & depend on the between numbers, at moment I have
about 60 between criterias and 3 update field rows to match each between
criteria.
BUT when using the form the update query won't read the form's combobox
(between xxx And xxx)at all & throws the error
ata type mismatch incriteria expression. Just won't pick it up. So I'm stuck at that point and
manually entering all these numbers.
I'll do as you suggest with the Form's AfterUpdate event code.
Do I replace the line:rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
With the on suggested by yourself, or add it in under?
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.