Insert data from one table to another table

[adamdaban]

Hi everyone I have one question if you can help me to solve my problem, my question is
I have a table name (tbl1) which contains three fields
ID Name Result
------ -------- ---------
2343 Jime 235
2453 Adam 657
2898 Rose 876
2098 Jime 987
2987 Adam 987

and My second table names (tbl2) which include four fields
ID Jime Adam Rose Result
---- ------ -------- ------- --------

so here is my question, I want to get value from tbl1 into tbl2 by (VBA), and the result be like this:

ID Jime Adame Rose Result
--- ----- ------- ------ --------
2343 Jime 235 =====> Jime should add under the field name is (Jime) also for others
2453 Adam 657
2898 Rose 876
2098 Jime 987
2987 Adam 987

Thanks for any help.....

 

[Minty]

Your result data makes no sense - you would use a crosstab query for that sort result - you wouldn't store it in another table.

 

[adamdaban]

Your result data makes no sense - you would use a crosstab query for that sort result - you wouldn't store it in another table.

before I used Dlookup function it can but the problem is only first record can add it if you check my code

Private Sub Command9_Click()

Me.ID.Value = DLookup("Result", "tblTestResults", "Resut= 'ID' ")
Me.Jame.Value = DLookup("Result", "tblTestResults", "Name= 'Jame' ")
Me.Adam.Value = DLookup("Result", "tblTestResults", "Name= 'Adam' ")
Me.Result.Value = DLookup("Result", "tblTestResults", "Resut= 'Result' ")
DoCmd.GoToRecord , , acNewRec
----------------------------
problem is only add first record i want also add another records because for example we have 3 data is belong to Jame

 

[Minty]

Please post up the actual desired results - your sample is not telling me anything. A picture of an excel sheet will do.

Include the original data and what you actually expect to see.

 

[adamdaban]

Please post up the actual desired results - your sample is not telling me anything. A picture of an excel sheet will do.

Include the original data and what you expect to see.

===============================================
Thanks for your reply, here is image description what's my problem and I want it

 

[Minty]

Okay - I can see now that, but it makes no real sense to store it like that, in fact it's against all normalisation rules.

What would be the purpose of copying the same information to this format, when you could easily create a query to produce this type of output on demand.

 

[adamdaban]

Okay - I can see now that, but it makes no real sense to store it like that, in fact it's against all normalisation rules.

What would be the purpose of copying the same information to this format, when you could easily create a query to produce this type of output on demand.

==========================================
yes Minty, by VBA I guess much better because I have many fields but first I need to get the idea from you after that I will manage others, this issue is so important for me because I have one table information look like tb1 and the result I need to be like tbl2.

 

[adamdaban]

Okay - I can see now that, but it makes no real sense to store it like that, in fact it's against all normalisation rules.

What would be the purpose of copying the same information to this format, when you could easily create a query to produce this type of output on demand.

========================================
one thing more why data in tbl1 look like this because I have text file when I uploaded to my database will store like this.

 

[Minty]

Table 1 looks exactly how you should store this information, table 2 looks like some spreadsheet abomination that has no place in any database.

You should never have a field named after a specific piece of data. So fields called Jim, June, Fred and Harry should NEVER exist.

Please read up about normalisation. It is essential to good database design.

 

[adamdaban]

Table 1 looks exactly how you should store this information, table 2 looks like some spreadsheet abomination that has no place in any database.

You should never have a field named after a specific piece of data. So fields called Jim, June, Fred and Harry should NEVER exist.

Please read up about normalisation. It is essential to good database design.

=============================
Yes I noe but only this table like this is uniqe I really need it to be like this

 

[Minty]

Well, the only way you will do that is to rebuild the table every time you get new data, as each time a new Fred , Tom , Dick or Harry arrives your table will need to have a column added.

Can you explain the final purpose of this table? I would be willing to bet you don't really need it.

 

[adamdaban]

Well, the only way you will do that is to rebuild the table every time you get new data, as each time a new Fred , Tom , Dick or Harry arrives your table will need to have a column added.

Can you explain the final purpose of this table? I would be willing to bet you don't really need it.

Thank you so much only, I need it how I can add these value from tb1 to tb2.

 

[Minty]

You can't just "add" them.
As I said in the previous post, the structure of the table will change every time there is a new person in the list.

This is why you wouldn't put these values in a table, but query them to produce a list in the format you want that is dynamic based on the data in table 1.

I'll try again - and "I need it" won't be a sufficient answer - what is the purpose of the table 2 layout / data? How will you use it?

 

[adamdaban]

You can't just "add" them.
As I said in the previous post, the structure of the table will change every time there is a new person in the list.

This is why you wouldn't put these values in a table, but query them to produce a list in the format you want that is dynamic based on the data in table 1.

I'll try again - and "I need it" won't be a sufficient answer - what is the purpose of the table 2 layout / data? How will you use it?

tbl2 for getting data from tbl1 and each record going to under specific field, hope is clear my meaning

 

[Minty]

I'm afraid not. I fully understand that your table2 is based on data from table1. What I don't understand is why.

Why do you THINK you need this data stored in this format. Is it for a report? A score card, a list of lottery numbers....

I'll post up an example shortly of what I think you probably are aiming at.

 

[adamdaban]

I'm afraid not. I fully understand that your table2 is based on data from table1. What I don't understand is why.

Why do you THINK you need this data stored in this format. Is it for a report? A score card, a list of lottery numbers....

I'll post up an example shortly of what I think you probably are aiming at.

=================
Sir, why I want to be like this because I have one text file and I will import into my access table which is tbl1 after import process data will be stored into my tbl1 like this so again I want to make reorganize them so that's why I want it.

 

[Minty]

Okay - so please take some advice, the data in table 1 is stored in a very useable and standard format. Simply create table 2 in exactly the same format, and append (add) the new data from table 1 every time it is updated.

Then you can query the data to get your strange layout without needing to re-invent the wheel. I say that - to be honest I'm not sure you could query it easily to get that layout... but I might have a try.

 

[adamdaban]

Okay - so please take some advice, the data in table 1 is stored in a very useable and standard format. Simply create table 2 in exactly the same format, and append (add) the new data from table 1 every time it is updated.

Then you can query the data to get your strange layout without needing to re-invent the wheel. I say that - to be honest I'm not sure you could query it easily to get that layout... but I might have a try.

Sir Can you give me a solution by VBA and I try in my database

 

[Minty]

Can you do want you want by using a pivot table in Excel? I just tried and I couldn't, Mainly because the layout makes no sense.

Why would have the name repeated under the the name in the column heading, it's pointless. From your data you can easily produce this;

But not the layout you suggested. And as I stated before you can't and shouldn't store this, because each new name would require the table to be deleted and recreated.

 

[adamdaban]

Can you do want you want by using a pivot table in Excel? I just tried and I couldn't, Mainly because the layout makes no sense.

Why would have the name repeated under the the name in the column heading, it's pointless. From your data you can easily produce this;

But not the layout you suggested. And as I stated before you can't and shouldn't store this, because each new name would require the table to be deleted and recreated.

yes I can do it because the name is standard not change once i define it