Results 1 to 2 of 2
  1. #1
    dskysmine is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14

    Appending Columns

    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

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. appending results to a text box
    By michaelinitaly in forum Forms
    Replies: 3
    Last Post: 09-11-2011, 11:35 AM
  2. Key violation when appending records
    By slaterino in forum Programming
    Replies: 10
    Last Post: 08-26-2010, 08:25 AM
  3. Need appending help?
    By asilva in forum Access
    Replies: 6
    Last Post: 02-17-2010, 03:53 PM
  4. Replies: 3
    Last Post: 02-08-2010, 09:00 AM
  5. Appending to a Combo Box
    By cotri in forum Forms
    Replies: 5
    Last Post: 01-28-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums