SQL Help

[bradsr0138]

I am trying to get data from one table to another. What i have so far is

Private Sub btnTransfer_Click()

Dim db As DAO.database, sSQL As String

Set db = CurrentDb

aSQL = "UPDATE Projects RIGHT JOIN InspectionReports ON Projects.ProjectNumber = InspectionReports.ProjectNumber SET Projects.ProjectNumber = InspectionReports.ProjectNumber

DoCmd.SetWarnings False
DoCmd.RunSQL (aSQL)
DoCmd.SetWarnings True

End Sub

and that works fine for 1 column of data, but I want to copy multiple coulumns from the original table to the new table and also keep the information tied together, if that makes sense.

Any help will be greatly appreciated.

 

[WayneRyan]

Brad,

I don't understand joining on ProjectNumber and then setting it, but the
syntax for SET allows:

Set TableA.Field1 = TableB.Field1,
    TableA.Field2 = TableB.Field2,
    TableA.Field3 = TableB.Field3

Wayne

 

[bradsr0138]

Wayne,

Thank you for the reply. What I have is this, I have a small windows app that batch processes word forms and puts each form field into the db. What I end up with is one tale with all the information. Much of it is repeated, so it is not a very efficient db, so what I am trying to do is pull the info out of that one table and split it up into multiple tables while keeping all related data together. If that makes sense.

 

[WayneRyan]

Brad,

I'd need more detailed information.

If the "new" data is just getting split up into multiple tables,
you could do this with append queries.

But, if they may have a relationship to existing data, then those are
updates (which seems to be the way you're heading).

Wayne

 

[bradsr0138]

Wayne,

Thanks so much for the speedy reply and for your help. I think I have the problem solved. The information does have a relationship to the existing data. I think this is going to work for me:

Private Sub btnTransfer_Click()

Dim db As DAO.database, sSQL As String

Set db = CurrentDb
sSQL = "Update Projects RIGHT JOIN InspectionReports " & _
"ON Projects.ProjectNumber = InspectionReports.ProjectNumber " & _
"Set Projects.ProjectNumber = InspectionReports.ProjectNumber, " & _
"Projects.ProjectName = InspectionReports.ProjectName"

DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True

End Sub

Once again, thank you so much for the help. Its nice to have people like you that are generous with their knowledge.

Brad