Creating Table from column data in another table.

[Clifford86]

Hello,



Is it at all possible to have Access take data from one table's column and use the data in a new table as its columns based on the data from the first table?

Example:

tbl_ criteria:
ID
Type
-data1
-data2
-data3
-data4
-data5
-data6
-data7
Description

tbl_updated_table:
ID
data1
data2
data3
data4
data5
data6
data7

The tbl_criteria column will not have a lot of data in the type column and the cell data will not be changing much if at all. Though the tbl_updated_table will be getting new data all the time in the different columns.

I know that I can go and manually update the table columns. Though it would be a lot easier to set it up to automatically update, by running a macro or something to update the table. As most systems will just be running the Access Runtime and not the full Access software. So I will not always be able to quickly update the tbl_updated_table.

Best regards,
Clifford86

[madpiet]

What problem are you trying to solve?
Sure you could create a query that returns the data you want (from the source table) and then subtract (well, in terms of sets... use an outer join to get the records that are in this query but not in the final destination table) and then append. But why? Do that set of records participate in relationships that the full set doesn't?

[ArviLaanemets]

I suspect this is the case of bad excel table structure applied to Access database!

[Bulzie]

Provide an example with sample data from your 2 tables and what you want it to do/look like. Hard to tell what it is exactly you are asking from your description.

[CJ_London]

if I had to guess, I think the OP is trying to pivot the data

tbl_updated_table:
ID data1 data2 data3 data4 data5 data6 data7

if so - a poor design for a table

[Clifford86]

Thanks everyone for the reply. I got it to work other than one part. In the end, yes, it is a bad design. Just because the concept in one's head works, does not mean it works in reality. I have gone back to the drawing board to figure out another way to try and go about it. In simple terms I wanted to take the tbl_criteria table that I had a form for and then create another table to use the records in the tbl_criteria table as columns for the other table. Then as I updated the tbl_criteria table I wanted the columns in the other table to be updated or the new columns added from the new record that was added in the tbl_criteria table. Using vba, I was able to get the new table to add the columns. I just could not get it to update columns that had the information in the tbl_criteria updated. As it just kept adding the update as a new column instead of updating the old column. LOL Anyway, back to the drawing board.

[June7]

Why do you want to save data in non-normalized structure?

Run a CROSSTAB query when needed to output in this structure.

[Clifford86]

Why do you want to save data in non-normalized structure?

Run a CROSSTAB query when needed to output in this structure.

I do not know much about CROSSTAB query or how to even go about setting it up to achieve this.

[Welshgasman]

That is when Google is your friend.

Plus the wizard does it for you?

[June7]

Access query builder has a wizard for CROSSTAB. It will help you learn and understand until you won't need it. Also review http://allenbrowne.com/ser-67.html