I think I need an Append query but not sure.

[kdahlin]

I am in the process of renormalizing an Access 2010 database while trying to not lose any data. I know you have all been there! They used [Client].[case] to store a case number for a period of time and then delete it the next year. This would then have the effect of "forgetting" the client ever had a service. So..


I have a database for casework.

A new table:
/ClientHistory/
[ClientHistory].[ID] - autonumber PK
[ClientHistory].[[ClientSSN] -Int FK
[ClientHistory].[ServiceID] - Int FK
[ClientHistory].[ServiceDate] -Date

and

/Client/
[Client].[SSN] -Int PK
[Client].[Case] -Int
[Client].[ClientName]
etc.



I want to select all records from [Client] where [Client].[Case] is not null and then
for each record in the recordset create a new record in the [Client History] table using the FK [SSN]
Setting :
[Client].[SSN] = Recordset[SSN], [ClientHistory].[ServiceID] = 1, [ClientHistory].[ServiceDate] = #12/30/2012#

Since I am making new records in an existing table it has to be an append query right? But all the tutorial for append don't seem to show you able to do this for each record in a Query. (sorry I used to do this with VB on .ASP pages. I'd be doing this in a loop. Working direct in access is still difficult for me)


or should it be an update query like:

UPDATE [Clients] INNER JOIN [Clients] ON [Clients].SSN = [Clients History].SSN SET [Clients History].SSN = [Clients].[SSN], [Clients History].AgencyID = 1, [Clients History].SERVICEID = 28, [Clients History].DATESERV = #12/30/2012#
WHERE ((([Clients].CASE) Is Not Null));

but this doesn't look right because it is assuming that there must be records with the SSN in the ClientsHistory table and there might not be. This might be the first one...

Please put me back on the right track.

 

[kdahlin]

I couldn't find an elegant solution that worked with a complex query so I went back to my roots and programmed it in VB with two separate SQL statements.

Sub InsertIntoClientHistory()
' this makes an entry in the client history for everyone accepted into angel tree
' program before the distribution case numbers are wiped and set back to zero.
Dim dbs As Database
Dim strSQL As String
Dim strSQL2 As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set dbs = CurrentDb

strSQL = "SELECT [Clients information1].SSN, [Clients information1].LASTNAME, Distribution.Case " _
& "FROM [Clients information1] INNER JOIN Distribution ON [Clients information1].SSN = Distribution.SSN " _
& "WHERE ([Distribution.Accept]=True);"
Set rs = dbs.OpenRecordset(strSQL)
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True
strSQL2 = "INSERT INTO [Client History1] " _
& "(SSN,AgencyID, ServiceID, DATESERV) VALUES " _
& "(" & rs("SSN") & ", '1', '28', #12/29/2012#);"

'dbs.Execute strSQL2
Debug.Print strSQL2
rs.MoveNext
Loop
Else

MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
'rs.Close

End Sub