Update specifc Field in a table based on other fields in table...

[MattRichardsUK]

Hi I'm either trying to over think this as I think it should be quite simple.

I have 2 tables The first with an Action Table which is a list of records and date for each eg.(RecordNumber, PDate). The second table is a history table with the list of records and 6 date columns eg.(RecordNumber, PDate1, PDate2, PDate3 ..etc) .

My problem is im trying to Update the date in the history table. the Logic should be as follows. For matching "Recordnumbers" update the PDate1 field with the Pdate from the Action table. If PDate1 is already populated update PDate2 instead, if PDate2 is populated update PDate3 and so on until PDate6.

I know this is possible but think I need a fresh pair of eyes to look at it.

Thanks for your help

 

[MajP]

The first question everyone will ask is why do you need six separate fields to store the same information? If you want to keep just a history of the last six dates it still would be easier to have a table like.
RecordNumber
pdDate_History 'the date
pdDateNumber 'so this could be 1 to 6. Or if they keep scrolling it would be x to x+5

If you just keep scrolling the history you can always keep adding records and do something like
Select Top 6 pdDate_History from tableHistory....

 

[MattRichardsUK]

It is an Old syem that has become critical to the business and any major change to data structure has to be sanctioned the above method is the only way the data will be allowed to be stored as making the change will to the structure will take too much time to obtain the signatures.

 

[Andrzejek]

Yo may try something like this:
(recordset approach)

Dim i as Integer

rst.Open "SELECT * FROM HistoryTable WHERE RecordNumber = 1234", DBConnection
[green]
'Find first empty NULL field[/green]
For i = 1 to 6
    If IsNull(rst.Fields("PDate" & i).Value) Then
        Exit For
    End If
Next i
[green]
'If i > 6 do something here because all 6 fields have dates
[/green]
DBConnection.Execute "UPDATE HistoryTable Set PDate" & i & " WHERE RecordNumber = 1234"

Code not tested, just an idea...

I totally agree with MajP, but I feel your pain. I have to deal with the same nonsense in my work

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

Public Sub UpdatePDdate()
  Dim strSql As String
  Dim RSread As DAO.Recordset
  Dim RSwrite As DAO.Recordset
  Dim I As Integer
  'insert any new records not yet in history table
  strSql = " INSERT INTO TableHistory ( recordNumber, pdDate1 ) "
  strSql = strSql & "SELECT Table1.recordNumber, Table1.pdDate FROM Table1 "
  strSql = strSql & "LEFT JOIN TableHistory ON Table1.recordNumber = TableHistory.recordNumber "
  strSql = strSql & " WHERE TableHistory.recordNumber Is Null"
  'Now update existing records
  CurrentDb.Execute strSql
  Set RSread = CurrentDb.OpenRecordset("Select * from table1")
  
  Do While Not RSread.EOF
    strSql = "Select * from tableHistory where recordNumber = " & RSread!recordnumber
    Set RSwrite = CurrentDb.OpenRecordset(strSql)
    For I = 1 To 5
      'if the date is already in history then no need to add it
      If RSwrite.Fields(I).Value = RSread!pdDate Then Exit For
      If IsNull(RSwrite.Fields(I).Value) Then
        RSwrite.Edit
         RSwrite.Fields(I).Value = RSread!pdDate
        RSwrite.Update
        Exit For
      End If
    Next I
    RSread.MoveNext
  Loop
End Sub

 

[MattRichardsUK]

Thanks for your replies, when i am back in the office in the morning. I will try them out and feedback.