Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Microsoft Access: Combine Tables (Like Fields)

  1. #1
    Senior Member Info Tech Geek's Avatar
    Join Date
    Jan 2003
    Location
    Vernon, CT
    Posts
    828

    Microsoft Access: Combine Tables (Like Fields)

    It has been a while since I have worked with access and I ran into a slight problem. I can't seem to resolve it with a query. How do you combine two tables that contain the same information for two different departments?

    Table 1

    Name / Dept / Field 1 / Field 2

    Table 2

    Name / Dept / Field 1 / Field 2

  2. #2
    Senior Member
    Join Date
    Apr 2004
    Posts
    1,130
    insert into table2 as Select from table1

    or somehing like that. all lines from table1 will be inserted on table2. is that?
    Meu sítio

    FORMAT C: Yes ...Yes??? ...Nooooo!!! ^C ^C ^C ^C ^C
    If I die before I sleep, I pray the Lord my soul to encrypt.
    If I die before I wake, I pray the Lord my soul to brake.

  3. #3
    Senior Member Info Tech Geek's Avatar
    Join Date
    Jan 2003
    Location
    Vernon, CT
    Posts
    828
    If I need to find all that match field 2 from both tables, how can I do that? Both tables have the same exact fields I just need a method to combine the tables to search all for specific similarities.

  4. #4
    AntiOnline n00b
    Join Date
    Feb 2004
    Posts
    666
    hi


    You want to Physically append the data in table2 to table1

    Simply Copy the table2 and Paste it it will give you three options one of them would be append to excisting table .....it will append the content to the excisting table.

    Or write in VBA code window in Access to do that ..

    DoCmd.RunSQL "INSERT INTO TableTarget SELECT Table2.* FROM Table2;"

    Then can easly search for recod...

    You want to create a View /Query of sort to show records from two tables on the basis of a similar record

    something like in VBA code window

    DoCmd.RunSQL "Select dept, field1,field2,field3 from table 1,table2 where table1.field1=table2.field1 and field1<10;"

    i don't have SQL server or Access to check it out..just did it on paper so you will have to check it out

    --Good Luck--

  5. #5
    Senior Member Info Tech Geek's Avatar
    Join Date
    Jan 2003
    Location
    Vernon, CT
    Posts
    828
    No, I don't want to append the tables perm. I want to combine the tables in a query to search both tables for data and use it in my reports.

  6. #6
    AO Ancient: Team Leader
    Join Date
    Oct 2002
    Posts
    5,197
    Do you have unique records for each record in both tables such as an SSN or something.

    It's hard not knowing the purpose and field structure of the two tables to help.
    Don\'t SYN us.... We\'ll SYN you.....
    \"A nation that draws too broad a difference between its scholars and its warriors will have its thinking done by cowards, and its fighting done by fools.\" - Thucydides

  7. #7
    AntiOnline n00b
    Join Date
    Feb 2004
    Posts
    666
    Hi

    You mean you have a different table for every Department..........The first rule of normalisation should have tried to stop you from doing that.... but oo well

    And Access isen't making it easier either it wouldn't allow me to run Multiple queries at the same time.....

    Now lets get this straight you have a different table for every department right.that means every table should only have one record........... about a particular department.

    Or every table has multiple records including recurring ones....can't see how.......Any relation(Foreign key etc) between these tables? i don't think there can be .

    I am still at loss why should have multiple tables of same fields in the same database....that's that's that's against the rules of normalisation......... don't you think you need to rethink the basic structure of the database(from what i am looking form here might be something else coz i can't make out without the actual structure of the database).


    --Good Luck--

  8. #8
    Senior Member
    Join Date
    Apr 2004
    Posts
    1,130
    select * from table1
    UNION
    select * from table2

    it will combine both in one VIRTUAL table (you can create a VIEW with thist)
    Meu sítio

    FORMAT C: Yes ...Yes??? ...Nooooo!!! ^C ^C ^C ^C ^C
    If I die before I sleep, I pray the Lord my soul to encrypt.
    If I die before I wake, I pray the Lord my soul to brake.

  9. #9
    if they have any kind of unique refrence in each row (like are all the names dif?) then create a new query - add in both tables and just drag then name of the field with the unique entry from table onto the corresponding field on the other table linking them

    then right your query as normal - but it will limit it to only entries which appear as rows in both tables

    is that what you mean??

    v_Ln

  10. #10
    Senior Member
    Join Date
    Jul 2001
    Posts
    420
    From my SQL programming long ago I remember using joins (inner or outer). From what your describing you want an inner join. Check out:
    http://www.google.com/search?hl=en&l...join+microsoft
    or
    even better:
    Join Commands from Google

    Hope that helps.

    Cheers,
    -D

    <EDIT>
    Did the google because I could not recall the syntax for a join in Access.
    </EDIT>
    If you spend more on coffee than on IT security, you will be hacked. What\'s more, you deserve to be hacked.
    -- former White House cybersecurity adviser Richard Clarke

Posting Permissions

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