Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: Access 2003: how to make a table that contains content of other tables.

    If my tables are named 2008t, 2007t, 2006t, and they all have 2 columns named Year and Name. How do I make a table named AllTbl that will have all the data of the previous three columns?

    say,

    2008t
    a: 08, aa
    b: 08, bb
    c: 08, cc

    2007t
    a: 07, qq
    b: 07, ww
    c: 07, ee

    2006t
    a: 06, ss
    b: 06, dd
    c: 06, ff

    I want AllTbl to be:

    a: 08, aa
    b: 08, bb
    c: 08, cc
    d: 07, qq
    e: 07, ww
    f: 07, ee
    g: 06, ss
    h: 06, dd
    i: 06, ff

    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    SELECT Field1, Field2
    FROM Table1
    UNION ALL
    SELECT Field1, Field2
    FROM Table2
    UNION ALL
    SELECT Field1, Field2
    FROM Table3
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Mind you, that doesn't create a table, but the result will be just as good as one
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2008
    Posts
    163
    sounds good to me... but, uh, where do I do that? I'm in design view for tables and I have no idea where to write that script. lol =D

    Anyway, while on the relatively same topic; with the same example above, is it possible to do the opposite? From the AllTbl, how does one extract all the rows with a Year value of, say, 07 into a separate table?

    I'm thinking that of having a Master List of all Units that only contains the Year, Unit code and Name. Then having separate tables per year containing year, unit code, unit name, unit details, etc.

    --

    Unrelated, if I have three fields, each of which is a list box, is there a way for the column on the left to narrow the options made available on the list box to the right?

    column1: 1, 2
    column2: 1a, 1b, 2a, 2b
    column3, 1a1, 1a2, 1b1, 1b2, 2a1, 2a2, 2b1, 2b2

    I was hoping that if 1 is chosen for column1, then only 1a and 1b will be made available on column2 instead of having all four choices.


    I'm actually still in semi-logic planning right now, since I'm new to Access, I'm trying to see what limitations there are in both Access' features and my own limited skills.
    Last edited by coffeecat; 06-26-08 at 03:35.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coffeecat - I've seen your post in Chat.

    Stop right now and read up on relational database design:
    http://r937.com/relational.html
    http://www.tonymarston.net/php-mysql...se-design.html
    It will seem like a drag now but will save you so much time in the long run.

    DO NOT have three tables here. You don't want to have three tables that you merge into one - just have the one table exactly as you have defined AllTbl. You should have data in columns - not in table names. The temporal data (2008, 2007 etc) should not determine how many tables you have and what their names are.

    Shame on you two for not telling him

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry, I wasn't psychic and didn't realise he was making a "three table system". I thought he was importing data from different sources in order to create a single table as you described... the AllTbl, which tbh, is a terrible name
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I also thought the goal was to normalize bad data. I agree with El Flump; one table.
    Paul

  8. #8
    Join Date
    Jun 2008
    Posts
    163
    Hey guys, thanks for the info. I just read Litwin's Relational Database Design article, I've yet to read the other link.

    @startrekker: Yep, it's a horrid name. Hope you didn't think that I would actually use it. =p

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ well... the thought crossed my mind
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jun 2008
    Posts
    163
    Haha. I do admit to being ignorant about databases. But using tacky filenames? =p

    ---

    Anyway, I finished reading about relational design. Seems like I was wrong to base the functionality of the db strictly to what can be made readily on tables. Just realized that a good combination of forms and queries will be what I need and that I should stop treating the tables as if they were my front-end.

    I spent the past couple of days putting lots of info in the tables, now I'm gonna go redraw the plans and normalize the existing tables for easier queries. =D

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hallelujah!!! He has seen the light!!

    Good stuff coffeecat, you've just taken your first step into a much larger world
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - great stuff coffeecat - that is exactly the lesson to learn. Good job

Posting Permissions

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