Appending Columns

[dskysmine]

Hey guys,
How are you doing?
I have an issue with "appending" columns in a table that is built dynamically.
If it were static I could just do an Inner join and use the common IDs in the tables but the thing is that the resulting query I have is built differently every week (it's the result of a crosstab query) so I cannot make a selection of the specific fields I want to add, I want to add only the non common fields.


Maybe an example will make it clearer.
This is an extract of the table in one scenario with headers (Where in "Data_Value #N", N can go from 1 to 12)
Node ID | Data_Value #1 | Data_Value #2 | ....
11111 | 33333 | 44444 | ...
22222 | 77777 | 98987 | ...
....
Then I have another Table with a similar structure for another scenario
Node ID | Data_Value #1 | Data_Value #2 | .... (Where in "Data_Value #N", N can go from 1 to 12)
11111 | 55555 | 99999 | ...
22222 | 12345 | 66666 | ...
....

Actually in the end I Have several tables but I want to put it all together into one table like:

Node ID | Data_Value #1 | Data_Value #1 | (include here all the Data_Value #1 from the other scenarios) | Data_Value #2 | Data_Value #2 |....
11111 | 33333 | 55555 | ... | 44444 | 99999 | ...
22222 | 77777 | 12345 | ... | 98987 | 66666 | ...
...

I tried using SUMPRODUCT in excel to build this but it takes so much time.
Does anybody have any ideas?

By the way I am using Access 2007 in case it is important.
These are only examples. There are actually more fields that are common for the different scenarios.

Thanks!
Dan

[Robeen]

You could create a Query in design view that has all the Tables you need - joined to each other by NodeID.
Then just pull in the fields you need.

If you HAVE to - you can then make the Query a 'Make Table' query and when you run it, it will create your new table according to your instructions.

But - you don't HAVE to have a Table with this duplicate data. You can just use the Query itself for whatever you're doing. It is a record source - just like a table - except that its data only exists when the query is actually run. This way you don't have more data in storage than you actually need.

Hope this helps!