How to move the data from a table to another

[pikkhuanloy]

Hi all Access Experts,

First of all, I am not a native english speaker, so please forgive me for my poor english.

My boss has assigned a task for me to move the details under a field name (eg:[Name]) in table 1 to table2 under another field name (eg:[InsuredName]).

Previously, the software I planned to use is Excel, but my boss says Excel cannot handle large data as the computer will be hanging.
So,he wants me to use Access to do the programming.
But I am definitely a newbie to Access.
I have some programming backgrounds (like C++) but it seems like I hardly understand how Access works! :banghead:
So can any Access expert here teach me how to do so that i won't get fired.

To get you the clear picture of what I want, I will paste some images here. I illustrate the process by using Excel but my job is making this work in Access.

Firstly, We will have the data like this.

Table1

Name    |    PolicyNo    |    Age   |    Benefit
--------+----------------+----------+-----------
Chris   |    123         |    21    |    Death
Jane    |    456         |    34    |    TPD
Adam    |    789         |    55    |    DD

Secondly, we let the other staffs to key in which column to be mapped with the other.
Manually Key in (both columns)
Mapping Table:

Table1        |    Table2
--------------+-------------------
Name          |    InsuredName
Policy No     |    Pol_No
Benefit       |    InsuredBenefit
Age           |    InsuredAge

Thirdly, the staff will run the VBA and result would be like this.

Table2

InsureName    |    Pol_No    |    InsuredBenefit    |    InsuredAge
--------------+--------------+----------------------+----------------
Chris         |    123       |    Death             |    21
Jane          |    456       |    TPD               |    34
Adam          |    789       |    DD                |    55

As you can see the new table(table2) , field number 3 is the same with the field number 4 in table1. There is something like copy and paste job, but user can decide how the arrangement for the new table is.

Any idea? I know this can be done in queries but as the boss wants it to be automated, what i can think of is VBA.

Appreciate if someone can help me on this.

 

[pikkhuanloy]

 

[shoji]

Here is the answer.

1. Create a Module and copy the following:

Sub TransferData()
Dim db As Database
Dim rs As Recordset
Dim strTable1Fields As String
Dim strTable2Fields As String
Dim strSQL As String

On Error Goto Err_TransferData

    Set db = CurrentDb
    Set rs = OpenRecordset("Mapping")
    Do While Not rs.EOF
      strTable1Fields = strTable1Fields & Nz(rs!Table1) & ","
      strTable2Fields = strTable2Fields & Nz(rs!Table2) & ","
      rs.MoveNext
    Loop
    rs.Close

    'Remove the last comma.
    strTable1Fields = Left(strTable1Fields, Len(strTable1Fields) - 1)
    strTable2Fields = Left(strTable2Fields, Len(strTable2Fields) - 1)

    strSQL = "INSERT INTO Table2 (" & strTable2Fields & ") SELECT " & strTable1Fields & " FROM Table1"
    db.Execute strSQL

    Set rs = Nothing
    Set db = Nothing

Exit_TransferData:
    Exit Sub
Err_TransferData:
    MsgBox Err.Description
    Resume Exit_TransferData
End Sub

2. Create a form and place a button.
3. From the property sheet of the form, go to Event and create an Event Procedure for On Click and write:

Private Sub Command0_Click()
    TansferData
    MsgBox "Done"
End Sub

Not tested. Good luck.

Shoji

 

[pikkhuanloy]

Hi Shoji Expert.

Thanks for your reply.

But it seems like doesnt work.

appreciate if you can explain the code for me , =)

 

[spikepl]

Why do you move data from one table to another? Normally this is hardly ever done in a database, so please explain.

Also, you normally do not store age because that is something which depends on when you look at the table. Age is normally calculated when needed from the date of birth, which is the value to store.

Google access reserved words for a list of words you should not use to name objects or fields. "Name" is one of them.

Note that "it doesn't work" is useless because it contains no information for people who are trying to help you. You need to say what you did, what you expected to happen, and what actually happened. See my signature.

 

[pikkhuanloy]

Why do you move data from one table to another? Normally this is hardly ever done in a database, so please explain.

Also, you normally do not store age because that is something which depends on when you look at the table. Age is normally calculated when needed from the date of birth, which is the value to store.

Google access reserved words for a list of words you should not use to name objects or fields. "Name" is one of them.

Note that "it doesn't work" is useless because it contains no information for people who are trying to help you. You need to say what you did, what you expected to happen, and what actually happened. See my signature.

Hi expert spikepl,

It's because when we receive data (Excel or Notepad) from clients, the layouts and the headers for each column are different from one to another (Eg: client A's first column is policy number but Client B's first column is Name).

So, my boss wants the data to be standardized and the data is with the standardized headers as well (eg: for policyholder's name, Client A's is Name, Client B's is FamilyName but we want it to be InsuredName). And, we want to remove the unused column through the process. So, We have to move the data into a new table

I know that we can actually copy and paste to get the same result , but it's very time-consuming. So, our choice would be going for VBA.
I know how to write it in Excel VBA but the VBA coding is abit different in Access. So, I have no choice but ask for the help from experts here.

The age is just an example. Would it be a bug to name the field as Age.
For the Name, I would try to change the headers as different name, thanks for this.

At last, I would tell you how the process go, I created tables (Table1,Table2,Mapping).
Table1 field names: Name(i will change another to test), PolicyNo,Age,Benefit
Table2 field names:InsuredName,Pol_No,InsuredBenefit,InsuredAge
Mapping field names: Table1, Table 2

When everything was done, I opened a module and pasted the coding in the module.
Then when I clicked run, the system showed error.

By the way, Expert, may you explain the meaning of the coding to me?
strTable1Fields = strTable1Fields & Nz(rs!Table1) & "," - Why we have to add a comma ,?
strTable2Fields = strTable2Fields & Nz(rs!Table2) & ","

strSQL = "INSERT INTO Table2 (" & strTable2Fields & ") SELECT " & strTable1Fields & " FROM Table1" - why we have to add " " to the code? I thought " " would make the code to be a string?

On a side note, I do really appreciate that you guys helping me. Thanks.

 

[shoji]

Hi pikkhuanloy,

spikepl is right. You can tell me exactly what happened when you ran the code. But I will add some explanations. I was assuming that your field names are just examples, not the real names, and you are dealing with more fields. So, my code tries to solve the problem more generally.

My understanding is that some one will come to the table, Mapping, and create a mapping between Table 1 and Table 2. Do you change this mapping occasionally? No matter, as long as the mapping table is correct.

Here is the code again, with comments.

Sub TransferData()

' I haven't used DAO for some time, but I understand DAO is now native to Access 2010. 
Dim db As Database
Dim rs As Recordset
Dim strTable1Fields As String    ' String of the field names for Table 1.
Dim strTable2Fields As String    ' String of the field names for Table 2.
Dim strSQL As String

On Error Goto Err_TransferData

'    First, open the mapping table, Mapping, and with that, create a string of 
'    the field names to use in the SQL statement.

    Set db = CurrentDb
    Set rs = OpenRecordset("Mapping")

    Do While Not rs.EOF

      ' If you were indeed using "name" as a field name, this is a big No No
      ' as spikepl pointed out.
      ' But if so, you should add [ ] to enclose a field name. This will ensure
      ' that the name is a field name, not a key word.

      ' In the line below, Nz is a special function on Access. Unless a field is
      ' null, you don't need it, but keep it there and you don't have to
      ' understand its meaning for now.
 
      strTable1Fields = strTable1Fields & "[" & Nz(rs!Table1) & "],"
      strTable2Fields = strTable2Fields & "[" & Nz(rs!Table2) & "],"
      rs.MoveNext
    Loop
    rs.Close

    'Remove the last comma.
    strTable1Fields = Left(strTable1Fields, Len(strTable1Fields) - 1)
    strTable2Fields = Left(strTable2Fields, Len(strTable2Fields) - 1)

'   When the loop above finishes, you should get strings like
'
'    strTable1Fields: "[Name],[PolicyNo],[Age],[Benefit]"
'    strTable2Fields: "[InsuredName],[Pol_No],[InsuredAge],[InsuredBenefit]"

    strSQL = "INSERT INTO Table2 (" & strTable2Fields & ") SELECT " & _
                 strTable1Fields & " FROM Table1"

'   So, this string should look like
'
'    "INSERT INTO Table2 ([InsuredName], [Pol_no],[InsuredAge],[InsuredBenefit]) 
     SELECT [Name],[PolicyNo],[Age],[Benefit] FROM Table1"

' Now I assume you are familiar with INSERT statement, but if not, 
' you should Google around for that and study SQL statements.
' The following Execute statement will transfer all the records from 
' Table 1 to Table 2. 

    db.Execute strSQL

    Set rs = Nothing
    Set db = Nothing

Exit_TransferData:
    Exit Sub
Err_TransferData:
    MsgBox Err.Description
    Resume Exit_TransferData
End Sub

If the mapping takes place only once, you can just write INSERT statement, omitting the loop above. Since you said someone else sets up the mapping, I assumed that the mapping changes. Therefore, a more involved code like above.

If you still have problems, let me know exactly what happens.

 

[shoji]

pikkhuanloy,

I just saw your reply. What does the error message say?

 

[pikkhuanloy]

Hi pikkhuanloy,

spikepl is right. You can tell me exactly what happened when you ran the code. But I will add some explanations. I was assuming that your field names are just examples, not the real names, and you are dealing with more fields. So, my code tries to solve the problem more generally.

My understanding is that some one will come to the table, Mapping, and create a mapping between Table 1 and Table 2. Do you change this mapping occasionally? No matter, as long as the mapping table is correct.

Here is the code again, with comments.

Sub TransferData()
 
' I haven't used DAO for some time, but I understand DAO is now native to Access 2010. 
Dim db As Database
Dim rs As Recordset
Dim strTable1Fields As String    ' String of the field names for Table 1.
Dim strTable2Fields As String    ' String of the field names for Table 2.
Dim strSQL As String
 
On Error Goto Err_TransferData
 
'    First, open the mapping table, Mapping, and with that, create a string of 
'    the field names to use in the SQL statement.
 
    Set db = CurrentDb
    Set rs = OpenRecordset("Mapping")
 
    Do While Not rs.EOF
 
      ' If you were indeed using "name" as a field name, this is a big No No
      ' as spikepl pointed out.
      ' But if so, you should add [ ] to enclose a field name. This will ensure
      ' that the name is a field name, not a key word.
 
      ' In the line below, Nz is a special function on Access. Unless a field is
      ' null, you don't need it, but keep it there and you don't have to
      ' understand its meaning for now.
 
      strTable1Fields = strTable1Fields & "[" & Nz(rs!Table1) & "],"
      strTable2Fields = strTable2Fields & "[" & Nz(rs!Table2) & "],"
      rs.MoveNext
    Loop
    rs.Close
 
    'Remove the last comma.
    strTable1Fields = Left(strTable1Fields, Len(strTable1Fields) - 1)
    strTable2Fields = Left(strTable2Fields, Len(strTable2Fields) - 1)
 
'   When the loop above finishes, you should get strings like
'
'    strTable1Fields: "[Name],[PolicyNo],[Age],[Benefit]"
'    strTable2Fields: "[InsuredName],[Pol_No],[InsuredAge],[InsuredBenefit]"
 
    strSQL = "INSERT INTO Table2 (" & strTable2Fields & ") SELECT " & _
                 strTable1Fields & " FROM Table1"
 
'   So, this string should look like
'
'    "INSERT INTO Table2 ([InsuredName], [Pol_no],[InsuredAge],[InsuredBenefit]) 
     SELECT [Name],[PolicyNo],[Age],[Benefit] FROM Table1"
 
' Now I assume you are familiar with INSERT statement, but if not, 
' you should Google around for that and study SQL statements.
' The following Execute statement will transfer all the records from 
' Table 1 to Table 2. 
 
    db.Execute strSQL
 
    Set rs = Nothing
    Set db = Nothing
 
Exit_TransferData:
    Exit Sub
Err_TransferData:
    MsgBox Err.Description
    Resume Exit_TransferData
End Sub

If the mapping takes place only once, you can just write INSERT statement, omitting the loop above. Since you said someone else sets up the mapping, I assumed that the mapping changes. Therefore, a more involved code like above.

If you still have problems, let me know exactly what happens.

Hi thanks for your reply, expert shoji.
here is the tables i created.






as you can see in the images, the system showed Compile Error.
Sub or Function not defined.

Is anything wrong with my table setup?

 

[shoji]

Oops. It should be like:

    Set db = CurrentDb
    Set rs = [COLOR="Red"]db.[/COLOR]OpenRecordset("Mapping")

 

[pikkhuanloy]

Oops. It should be like:

    Set db = CurrentDb
    Set rs = [COLOR=red]db.[/COLOR]OpenRecordset("Mapping")

Hi Shoji expert , you know what? it works! thanks a lot , i dont know how to express my feeling but really, thanks a lot!!

Regarding the coding above, i still cannot understand why you added the commas for each string and delete it after that.

Another question pops out, is it possible to work it out like
Table1
Insured SumAssured_Death SumAssured_TPD SumAssured_CI
Chris 5000 2000 1000
Jane 3000 500
Adam 2000 6000

Table2

Insured Benefit SumAssured
Chris Death 5000
Chris TPD 2000
Chris CI 1000
Jane Death 3000
Jane CI 500
Adam Death 2000
Adam TPD 6000

we transform the data to be grouped under another layout.
we still need another table "Mapping_Transform" to do it so that the users can actually determine what fields to move and the way they wanna transform the data into the new alyout and arrangement.

 

[shoji]

Hi Shoji expert , you know what? it works! thanks a lot , i dont know how to express my feeling but really, thanks a lot!!

Glad it finally worked.

Regarding the coding above, i still cannot understand why you added the commas for each string and delete it after that.

At the end of the loop, strTable1Fields will look like:

"[Name],[PolicyNo],[Age],[Benefit],"

Notice the comma at the very end of the string. If you use this string, the final SQL statement will be invalid. So you have to strip that comma. Generally, INSERT Statement should be

INSERT INTO <Destination Table> (D1, D2, D3) SELECT S1, S2, S3 FROM <Source Table>

If the table structure for both tables are identical, you can write

INSERT INTO <Destination Table> SELECT * FROM <Source Table>

Another question pops out, is it possible to work it out like
Table1
Insured SumAssured_Death SumAssured_TPD SumAssured_CI
Chris 5000 2000 1000
Jane 3000 500
Adam 2000 6000

Table2

Insured Benefit SumAssured
Chris Death 5000
Chris TPD 2000
Chris CI 1000
Jane Death 3000
Jane CI 500
Adam Death 2000
Adam TPD 6000

we transform the data to be grouped under another layout.
we still need another table "Mapping_Transform" to do it so that the users can actually determine what fields to move and the way they wanna transform the data into the new alyout and arrangement.

Explain more. You can combine 2 or more fields or process them before inserting them into Table 2. Looks like this is not just a straight forward transfer. Instead of using another Mapping table, you should establish the mapping rule so it will incorporate all the changes you need.

 

[pikkhuanloy]

Glad it finally worked.



At the end of the loop, strTable1Fields will look like:

"[Name],[PolicyNo],[Age],[Benefit],"

Notice the comma at the very end of the string. If you use this string, the final SQL statement will be invalid. So you have to strip that comma. Generally, INSERT Statement should be

INSERT INTO <Destination Table> (D1, D2, D3) SELECT S1, S2, S3 FROM <Source Table>

If the table structure for both tables are identical, you can write

INSERT INTO <Destination Table> SELECT * FROM <Source Table>



Explain more. You can combine 2 or more fields or process them before inserting them into Table 2. Looks like this is not just a straight forward transfer. Instead of using another Mapping table, you should establish the mapping rule so it will incorporate all the changes you need.

Hi expert Shoji, Sorry for the late reply as i was quite busy during the weekend.

Now my boss doesnt want me to take care of the transforming data part. But for the data reallocation like the first coding you wrote for me, He poped out some questions.

Can I have several data input tables and only 1 output table?
Eg:the data has several tabs in Excel such as 2014Q1,Q2,Q3.
Q1=1st Quarter.
The data having different layouts as illustrated below such as Different Headers' names. Is it possible to state all the mapping criteria in 1 table?

My idea is as below.

[FONT=Arial]output                  |    input1               |         input2                      |    input3[/FONT]
[FONT=Arial]---------------------------+--------------------------+--------------------------------------+---------------------------[/FONT]
[FONT=Arial]POLICY_NAME    |    IN_NAME         |    INSURED_NAME           |    P_NAME[/FONT]
[FONT=Arial]POLICY_AGE       |   IN-AGE             |                                        |    P_AGE[/FONT]
[FONT=Calibri][FONT=Arial]SUM_ASSURED  |    IN_SA              |    INSURED_SA                |    P_SA[/FONT]
[FONT=Calibri][FONT=Arial]PREMIUM            |                           |    INSURED_PREMIUM     |    P_PREMIUM[/FONT]
[FONT=Arial]BENEFIT             |                            |                                        |   [/FONT]
[FONT=Calibri][FONT=Arial]SEX                     |    IN_GENDER     |    INSURED_GENDER      |   P_GENDER[/FONT]
[/FONT][/FONT][/FONT]

And as you can see some of the cells contain no information as the input field is not available in certain tables. So, this actually create another problem as the string for output contains 6 fields in example above and 4 for input1,input2 & 5 for input3.

So, my thought for the solution of this problem is to create a temporary table for each mapping happen. the coding is like SELECT input1 where input1 <> empty and SELECT output where input1 <> empty and insert them into temp1 (temporary table 1).

[FONT=Arial]output                  |    input1             [/FONT]
[FONT=Arial]---------------------------+----------------------------[/FONT]
[FONT=Arial]POLICY_NAME     |    IN_NAME  [/FONT]
[FONT=Arial]POLICY_AGE        |   IN-AGE       [/FONT]
[FONT=Arial]SUM_ASSURED    |    IN_SA              [/FONT]
[FONT=Arial]SEX                      |    IN_GENDER[/FONT]

so the number of fields in both strings will be equal. then write a VBA to drop the temp1 after the process of copy&paste to the table output. and then drop temp1. For input2 & input3, same steps will be taken as input1.

Is it possible to do this? or you have any better ideas? Looking forward to hearing from you. Thanks a lot expert Shoji!

 

[shoji]

Hi pikkhuanloy,

If your boss does not want to use my codes, that's fine, but did you understand what goes on with my codes? If you did, you can apply them to other variations of codes.

The situations you described seem to be pretty chaotic. The import format is not standardized, and you should solve this issue soon if not immediately.

If input forms are not too many and each form has the fixed data format, you can create as many mapping tables as you need, and create as many buttons so you can start the different transfer by clicking each button. But if input forms keep changing, you got a problem. The golden rule here is that you should not try to solve the problem; try to avoid it. Namely, if you can standardize the import format, you don't have to make complicated codes. That is my last advice and good luck.

Shoji

 

[pikkhuanloy]

Hi pikkhuanloy,

If your boss does not want to use my codes, that's fine, but did you understand what goes on with my codes? If you did, you can apply them to other variations of codes.

The situations you described seem to be pretty chaotic. The import format is not standardized, and you should solve this issue soon if not immediately.

If input forms are not too many and each form has the fixed data format, you can create as many mapping tables as you need, and create as many buttons so you can start the different transfer by clicking each button. But if input forms keep changing, you got a problem. The golden rule here is that you should not try to solve the problem; try to avoid it. Namely, if you can standardize the import format, you don't have to make complicated codes. That is my last advice and good luck.

Shoji

Hi expert shoji, sorry for my bad english and making you feel that way. My boss needs your codes. it's just that he wants to make it to be more mappings in the table "Mapping". He needs multiple input tables to be transformed to the output table according to the criteria set in the table "Mapping".

[FONT=Arial]output                  |    input1               |         input2                      |    input3[/FONT]
[FONT=Arial]---------------------------+--------------------------+--------------------------------------+---------------------------[/FONT]
[FONT=Arial]POLICY_NAME    |    IN_NAME         |    INSURED_NAME           |    P_NAME[/FONT]
[FONT=Arial]POLICY_AGE       |   IN-AGE             |                                        |    P_AGE[/FONT]
[FONT=Calibri][FONT=Arial]SUM_ASSURED  |    IN_SA              |    INSURED_SA                |    P_SA[/FONT]
[FONT=Calibri][FONT=Arial]PREMIUM            |                           |    INSURED_PREMIUM     |    P_PREMIUM[/FONT]
[FONT=Arial]BENEFIT             |                            |                                        |   [/FONT]
[FONT=Calibri][FONT=Arial]SEX                     |    IN_GENDER     |    INSURED_GENDER      |   P_GENDER[/FONT]
[/FONT][/FONT][/FONT]

I need your help because i am so new to access, i've got the idea, but i dont know how to write the codes.

 

[Cowboy_BeBa]

i dont think you actually need any coding for this, a query should do the job just fine
go into access, in the CREATE tab select Query Design
in the DESIGN tab under Query Type youll notice that "Select" query is chosen by default. Change this to "Append", once you click append itll ask you to choose the table you want to copy the date to.

once youve done this, pick the table you want to copy data from
copy all the fields you need into the bottom section of the query designer
then under each field where it says Append To, just select the field you want to copy the date to

and thats it
to automate it you just need one line of VBA code
something like
docmd.runquery "queryname"

 

[pikkhuanloy]

Hi pikkhuanloy,

If your boss does not want to use my codes, that's fine, but did you understand what goes on with my codes? If you did, you can apply them to other variations of codes.

The situations you described seem to be pretty chaotic. The import format is not standardized, and you should solve this issue soon if not immediately.

If input forms are not too many and each form has the fixed data format, you can create as many mapping tables as you need, and create as many buttons so you can start the different transfer by clicking each button. But if input forms keep changing, you got a problem. The golden rule here is that you should not try to solve the problem; try to avoid it. Namely, if you can standardize the import format, you don't have to make complicated codes. That is my last advice and good luck.

Shoji

Hi Shoji,

What i meant was please ignore the email #12 as only that task my boss doesn't want me to do. but for the move the data to another table, he still needs it. it's just that now he wants multiple data to be transfered to 1 output table.

Looking forward to hearing from you soon.

THanks!