OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

Join multiple tables that use a common identifier field

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
imissmysol
Newbie
Newbie


Joined: 20 Jun 2012
Posts: 1
PostPosted: Wed Jun 20, 2012 6:08 pm    Post subject: Join multiple tables that use a common identifier field Reply with quote
I have a ACCESS DB that has multiple tables that have a common field that is used.

For example, In table one the ID field is accompanied by FIELDS A B AND C

In table two, the ID field is accompanied by fields D E AND F

In table three, the ID field is accompanied by fields D G AND C

I want to make one table that has the ID field and B, C, D, E (for example)

I know how to do the query, but if i select the same field from both tables, it will not work. I need it to combine all of the information but not modify the ID field (other than add more records). I need all of the information related to each ID field in the respective table to be brought into one place. The intent is to export it as a csv at some point so I can import it into Outlook or Salesforce or both.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Thu Jun 21, 2012 9:02 am    Post subject: Reply with quote
imissmysol wrote:
I have a ACCESS DB that has multiple tables that have a common field that is used.

I know how to do the query, but if i select the same field from both tables, it will not work.

I do not understand your comment above. By first clicking on the Queries icon on the left, under Tasks, are you creating the Query by:
  1. Create Query in Design View...
  2. Use Wizard to Create Query...
I strongly recommend you use the first one above ( Create Query in Design View... ), and, follow these steps.
  1. Open your OpenOffice Base file ( *.odb )
  2. When the Add Table or Query pop-up is presented, click on the tables you want in your query, and, click on the Add button
  3. Click on the Close button
  4. On the screen that is presented, at the top, left click, and drag the ID field(s) that you want to 'relate' to the other ID fields so it looks like the graphic in post below by Sliderule ( click link below to see it Smile ) -- this is step number 7:

    http://www.oooforum.org/forum/viewtopic.phtml?t=51739




  5. You can decide on the Fields you want in your Query by EITHER:
    1. On the Field line, choose the field you want included in the display
    2. Double click on the Field name[/bolor] in the top portion that you want included in the display
    3. [color=blue]Single click on the Field name in the top portion that you want included in the display and drag it the the Field line below
    4. Single click on the Field name in the top portion that you want included in the display and press the Enter key, it will now show up on the Field line below

  6. Run your Query by EITHER:
    1. Press your F5 key
    2. On the Toolbar, press the Run Query(f5) icon
    3. From the Menu: Edit -> Run Query

  7. Save your Query
  8. Since, when you created your OpenOffice Base file ( *.odb ) [ and you Registered the name ]to connect to your Access database, now you can move the data, from your saved Query, to a Calc spreadsheet so that it can be 'saved' in CSV format. Smile
  9. From the Menu: File -> New -> Spreadsheet
  10. EITHER:
    1. Press F4 key
    2. From the Menu: View -> Data Sources

  11. Expand the list for your Registered Database name
  12. Expand the Queries
  13. Click on the name of the Query you saved above, and, DRAG it to a spot on the Calc spreadsheet ( probably want it in cell A1 )
  14. The data from your OpenOffice connected database will now appear in the Calc spreadsheet Smile
  15. You now want to save this as a CSV file, so:
    1. From the Menu: File -> Save As...
    2. Under Save as type... YOU SHOULD CHOOSE Text CVS (*.csv)
    3. Assign a File name: of your choosing
    4. Press the Save button

  16. Smile and say: "Gee Sliderule, that was easy, now, all I have to do is mark the forum post as [Solved] per the instructions at the bottom." Smile
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group