SQL for Opening Recordset

[Carmen]

Hi,

I currently have a table with the last column being PRIORITY. I've created
about 26 update queries to populate this field based on different criteria.
It's a nightmare to make changes or explain this to a person with little
Access knowledge. THus I'm trying to write some code to populate the table
instead.

I have little knowledge on recordsets, but I know to open the recordset I
have to provide an sql statement. Can this sql statement be replaced with
the sql from the update queyr? I'm having problems with the replacement. In
the update query sql, it shows that I have "update....left
join....on......as.....Set....Where...." I understand that this sql will at
the same time populate the table so I'm not sure if it can be used in the
open recordset.

I have many many tables to prioritize and each one has its own criteria. My
initial thought was to set up an array for each report that will capture the
priority numbers, and the sqls to open and update (at the same time). This
is then run through a generic procedure.

Please let me know if I'm just too optimistic and can't be done this way.

Thanks,
Carmen

 

[Van T. Dinh]

Sorry, no. Creating a Recordset requires a SELECT Query, not an UPDATE SQL
since Recordset can be thought of as a "virtual Table" in the computer
memory.

I can't say that I understand what you are trying to achieve from the
description, though ..., especially that the description involves Table,
Update Query, Recordset an then Report. Perhaps, you should analyze and
break the whole process into smaller steps and tackle them one at a time ...

 

[Carmen]

I'll try to reword what I've said. I currently have 3 tables with data that
needs to be prioritized (ie those that meet criteria A is priority 1, those
that meet B are 2, and so forth). I have been using update queries to do the
prioritization. However, each prioritization is specific to a table so I saw
myself creating about 20+ update queries to prioritize each of the tables.
Also, since my priorities are in order (ie 1, 2,3,4....if I want to change
the order I basically have to rename all the updates as well as change the
text to be updated. This is a hassle.

I'm hoping I can store the text to be updated, and the criteria for each of
the tables in arrays and somehow run though a procedure that will populate
the table.

Example
Sub PopulateReport01Array()
arrayReport01(1,1)="priority 1"
arrayReport01(1,2) ="Update....."
arrayReport01(2,1)="priority 2"
arrayReport01(2,2) = "Update...."
End Sub

then I run a procedure that will go update the entire table based on
arrayReport01. Since I must use select query, that means that my array
should be 3D (priority, select for open recordset, update sql to perform the
update).

I'm new with SQL, but I know I can view my update query in sql view. Can I
easily split this sql into the desired portions above or how would you
suggest I should do?

Thanks,
Carmen