Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: Combining 3 3-column tables into 1 3-column table

    I have 3 tables with the same schema, Column A is a unique ID, Column B is a Date, and Column C is a text field (length=100). Now, is there a way to collapse the table so that I can have the same schema, but have all the dates lined up in one table? I want to create a report with chronology by date. I'm guessing I need to build a query in Access to be able to do this.

    The second enhancement: I'd like to have this kind of table collapsing for reports - is there a way to collapse these and then color code each entry based on which table they came from?

    Thanks to all in advance

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A UNION query will pull the 3 together:

    SELECT ColumnA, ColumnB, ColumnC, "Table1" AS Source
    FROM Table1
    UNION ALL
    SELECT ColumnA, ColumnB, ColumnC, "Table2" AS Source
    FROM Table2
    UNION ALL
    SELECT ColumnA, ColumnB, ColumnC, "Table3" AS Source
    FROM Table3

    You can use conditional formatting and that "Source" field I added to get your colors on the report.
    Paul

  3. #3
    Join Date
    Sep 2003
    Posts
    35
    Outstanding. Thanks. The join worked.

    For the coloring, I have gotten to the conditional formatting dialogue. There are only two choices though, a Field Value and an Expression. I basically want to say if Source="A", then color all three columns red, if "B", then all three columns blue. Can this be done?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure. Highlight all 3 controls, then go into conditional formatting. You can have up to 3 conditions, which fortunately is all you need. You'll want "Expression Is", and the first expression would be:

    [Source] = "Table1"

    and set the appropriate color. Then click Add at the bottom and do the second condition:

    [Source] = "Table2"

    and set the color for that. I assume you can see where to go from here?
    Paul

  5. #5
    Join Date
    Sep 2003
    Posts
    35
    Excellent. Works like a charm. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •