Update table with new data only

[artanis50]

Hi everyone, I'm really an access newbie, and I need some help!

I've got one table, let's call it "data", that has a set of data with a Measure Name, and the corresponding monthly data in columns. For example:

Measure Name Jan 2014 Feb 2014 Mar 2014
Calls Answered 10 15 25

I have a function that takes the data from the above format, and puts it in the following format:
Measure Name Period Score
Calls Answered Jan 2014 10
Calls Answered Feb 2014 15
Calls Answered Mar 2014 25

Here's the function I'm using (I've left out a lot of the fields I'm really using just to make the example easier.

Function TransposeSLA()
Dim rsMySet As DAO.Recordset
Dim strSQL As String
Dim i As Integer

'Open the original matrix-style dataset
Set rsMySet = CurrentDb.OpenRecordset("SLA_Data")

'Start the count at the position number of the first column-oriented field
'Remember that Recordsets start at 0
For i = 16 To rsMySet.Fields.Count - 1

'Use the recordset field.name property to build out the SQL string for the current field
strSQL = "INSERT INTO SLA_Transposed ([Account], [SBU Manager], [SLA Type], [SLAName], [Capability], [TargetDirection], [Target], [Period], [Score]) " & _
"SELECT [SLA_Data].[Account], [SLA_Data].[SBU Manager], [SLA_Data].[SLA Type], [SLA_Data].[SLAName], [SLA_Data].[Capability], [SLA_Data].[SLA Target Direction], [SLA_Data].[SLA Target], " & _
"'" & rsMySet.Fields(i).Name & "'" & " AS Month, " & _
"[" & rsMySet.Fields(i).Name & "] " & _
"FROM SLA_Data;"

'Execute the SQL string
CurrentDb.Execute strSQL

'Move to the next column-oriented field
Next i
End Function




The function above puts the data in a new table called "Transposed" in the format I need. The user accomplishes this by clicking a "button" on a form. The button also deletes the existing table / records from Transposed. I now need to change it so that only NEW data from the "data" table is transposed by the function INTO the table "Transposed". The reason for this is that my client decided they want to do some additional data entry in the transposed table. As it is right now, they can't, because the table is deleted each time they click the button on the form to add new data.

Any suggestions / help on how I can accomplish this is greatly appreciated!

[rpeare]

Why are you entering data in a non normalized structure only to try and put it back into a normalized structure? The way you have it you have to add a column to your original table for every new month which is not really a good design. Do you have any control over the original source?

If you only want to add new data you will have to determine what makes a 'unique' record in your data, I think MS access can handle up to a 6 or 7 field unique key or (preferably) you can compose a unique key based on the data (i.e. concantenate the account number and the period (yyyymm format date?)) and use that as a primary key. Then you would likely have to have a 2 step import, 1 that appends everything FIRST (as long as you have a primary key defined duplicate items will not be added) then updates any old data with new data.

[artanis50]

I do not have control over the original data source (table "Data"). The whole reason I created this database was to put it in the proper format so it can be read into another application that produces scorecards / dashboards. The client refuses to change their data entry format. <Arrrrrgggg!>

I added a primary key to both tables, and now it appears that only new data is being updated. AWESOME! However, doing this, broke my function and I'm NOT getting the Month / Score appended in the table "transposed".

[rpeare]

Also, try using a debug.print statement on your SQL statement to see what it's actually trying to execute then pasting that SQL to an actual query and see what results it gives you

I created a sample table and ran the code your way and a different way and both worked after I adapted the code so you may have a problem with data types or something else going on.