Hello

I have a table 'tempallreview' that is populated and refreshed using a piece of code which I have pasted at the bottom of the thread. The code is necessary to create this table as it is actually a series of seperate tables (all with identical structures) concatenated.

I use Access to 'interrogate' an off the shelf product so I cannot change any underlying table structure.

The table 'tempallreview' is then queried for a number of reports etc. Fields in this table are as follows:

(name) tempallreview: ObjectID | SearchNo | DateSearched | Consultant | Status | Job

with the exception of the field 'Job' each field takes it's data straight from the parent tables (those tables concatenated by the code below) The parent tables each have names along the lines of J000001, J000002, J000003 ..... J000nnn etc and a structure of:

ObjectID | SearchNo | DateSearched | Consultant | Status

(and several extra but irrelevant fields)

The 'Job' field in 'tempallreview' records the name of the parent table for each piece of data.

I query the table 'tempallreview' to identify specific people I wish to contact. Once I have contacted everyone in 'queryX' I want to update their status. Altering the status field in 'tempallreview' would be relatively easy with an update query but useless as it doesn't cascade through to the parent data. I really need to alter the status in the underlying parent table ie. the table whose name is defined in the 'Job' field of 'tempallreview'.

I am wondering what the best way to achieve this is ?

Any help appreciated.


the code to refresh tempallreview:

Private Sub Command5_Click()
CurrentDb.Execute "Delete * from tempallreview"
Dim db As Database
Dim rsRjobs As Recordset
Dim rsRapps As Recordset
Dim LengthofUnionSQL As Long
Dim sql As String
Dim UnionSQL As String
Set db = CurrentDb
Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
Do While Not rsRjobs.EOF
UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, Status, """ & rsRjobs!JobID & """ AS Job from [" & rsRjobs!JobID & "] Union "
rsRjobs.MoveNext
Loop
'following two lines are to remove the trailing word Union from the string unionsql
LengthofUnionSQL = Len(UnionSQL)
UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7)
' Now variable Unionsql2 will hold the value something like
' Select ObjectID, SearchNo, DateSearched, Consultant from J000145
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147

Set db = CurrentDb
Dim rsUnionquery As Recordset
Dim rstempallreview As Recordset
Set rstempallreview = db.OpenRecordset("tempallreview", dbOpenDynaset, dbSeeChanges)
Set rsUnionquery = db.OpenRecordset(UnionSQL)
Do While Not rsUnionquery.EOF
rstempallreview.AddNew
rstempallreview!ObjectID = rsUnionquery!ObjectID
rstempallreview!SearchNo = rsUnionquery!SearchNo
rstempallreview!DateSearched = rsUnionquery!DateSearched
rstempallreview!Consultant = rsUnionquery!Consultant
rstempallreview!Status = rsUnionquery!Status
rstempallreview!Job = rsUnionquery!Job

rstempallreview.Update
rsUnionquery.MoveNext
Loop

End Sub

in case anyone else has a similar requirement the answer is the code below. I appreciate that this probably should have gone in a forum other than Queries.

This code basically searches through the temporary table for records of a certain status (in the example bloew the status is 'Y')and then goes to the parent table (as identified in the temp table tempallreview by field 'Job')and updates the status there (in the example below to status 'X').

Public Function RefreshRejectedStatus()
Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String

Set db = CurrentDb

'Setup Recordset, Single Jobs Only!
SQL = "SELECT Job " & _
"FROM tempallreview " & _
"GROUP BY Job;"
Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)

'Parse thru tables & update status to "B"
Do Until rstName.EOF
SQL = "UPDATE " & rstName!Job & " " & _
"SET Status = 'X' " & _
"WHERE ([ObjectID] In (SELECT OBjectID " & _
"FROM tempallreview " & _
"WHERE [Job] = '" & rstName!Job & "' AND [Status] = 'Y'));"
db.Execute SQL, dbFailOnError
rstName.MoveNext
Loop

Set rstName = Nothing
Set db = Nothing
End Function