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

How to join two tables ?

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


Joined: 07 Jul 2005
Posts: 61

PostPosted: Tue May 02, 2006 4:45 am    Post subject: How to join two tables ? Reply with quote

I've searched here, I've searched google, I cannot find the answer to this question.

I use Access mainly to join tables and export a merged table or spreadsheet. After finally figuring out how to import tables into Base (which as far as I can see does not have an import table function like Access) I now have two tables.
The first we shall call "pricing". this table contains a part number and a price along with an ID that I set up upon import.
The second table we shall call "ranking". this table contains the same part number and a rank. along with an ID that I set up upon import.

The common link between these two tables is the "part number" I need to join these tables, then be able to query the join and export one table/spreadsheet which now has "part number', "price" and "rank"

In Access, It was an easy project. I would simply import the two tables, go to join tables, drag "part number" to "part number" in the two join boxes, make my query and then go to "edit in office" and there was my spreadsheet.

When I try to drag "part number" to "part number' in the join fields box in Base, I get this error message every time.

Primary or unique constraint required on main table: "Ranking" in statement [ALTER TABLE "Pricing" ADD FOREIGN KEY ("Part No.") REFERENCES "Ranking" ("Part")]

I use Access for this daily and don't want to use it anymore, but have not been able to find a simple and most importantly, quick way to do this using Base. Any help would be greatly appreciated.

I do realize I can do pretty much the same thing using vlookup, but prefer the database method for the flexability of the query.

Many, many thanks in advance for any help.
Back to top
View user's profile Send private message
llinosh
General User
General User


Joined: 13 Apr 2006
Posts: 7

PostPosted: Wed May 03, 2006 1:06 am    Post subject: Reply with quote

I have always had this message when no primary key is set up in both tables. Creating the relationship in Base is pretty simular as that in Access. (tools - relationships and then drag and drop form one table to the other) The biggest difference I've noticed (so far) is that you can't distinguish what kind of relationship is in existance, and therefore you can't change it to what you need or want. From what I have seen it automaticaly decides the type of relationship for you. I have also had a simular error report when I've tryed changing the relationship or canceling it - could be a bug?
Back to top
View user's profile Send private message
greg_h
General User
General User


Joined: 05 May 2006
Posts: 16
Location: New Zealand

PostPosted: Sun May 07, 2006 1:55 am    Post subject: Reply with quote

Sounds to me that it might need a unique index on one of the fields in the relationship.

So if the primary key like llinosh suggests doesn't work, create a unique index on the part number field in the one side of the one to many (or if it's one-to-one you could create it on both).

Regards,

Greg H
Back to top
View user's profile Send private message
donlinux
Power User
Power User


Joined: 07 Jul 2005
Posts: 61

PostPosted: Sun May 07, 2006 2:11 am    Post subject: Reply with quote

Doesn't seem to matter what or how I do it. Anytime I try to join two tables I get the same error message. The only way it works is if I create a table from scratch, manually add data and then join. This doesn't work for me as all of my data arrives in excel format and doesn't need to be entered manually.

I guess it's just not quite ready for primetime. too bad. I really like all the other OO programs.

thanks,
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Sun May 07, 2006 3:34 am    Post subject: Reply with quote

After importing did you set the ID field as the unique primary key?
_________________
Ron from Iowa, USA
Back to top
View user's profile Send private message
donlinux
Power User
Power User


Joined: 07 Jul 2005
Posts: 61

PostPosted: Sun May 07, 2006 11:29 am    Post subject: Reply with quote

As a matter of fact, the only way it would allow me to import was if I set the ID as the primary key. If I did not set it as primary key when I was importing, it would not import any data, just an empty database.
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Sun May 07, 2006 11:56 am    Post subject: Reply with quote

Did you set the Part Number field as the primary key field in both of them, not the ID? (Pardon me if I misinterpreted what you had already posted.)
_________________
Ron from Iowa, USA
Back to top
View user's profile Send private message
donlinux
Power User
Power User


Joined: 07 Jul 2005
Posts: 61

PostPosted: Mon May 08, 2006 2:26 am    Post subject: Reply with quote

Ok. Maybe I'm doing something really wrong because OO won't let me create a table unless I set the primary key to ID. Here's what I'm doing.

I have OO Base open and I'm at the main table screen. I then open up a spreadsheet which has the data I need, let's call it Parts A. I highight the part number and the price, including the header rows, then I go to the base screen, right click and do a paste or paste special /RTF. When I go through the import screens, unless I set the primary key to ID, it won't import any data. If I don't set a primary key upon import, I get a "warning, no insert privileges" box and no data is imported.

If I import using the primary key "ID" then go into the table to try and change the primary key to "part ", I get this error message. "SQL Status: 23000
Error code: -104

Unique constraint violation: in statement [ALTER TABLE "shoeinterchange" ADD PRIMARY KEY ("FB228")]"

I'm baffled and I certainly appreciate the help.

Thanks,
Back to top
View user's profile Send private message
llinosh
General User
General User


Joined: 13 Apr 2006
Posts: 7

PostPosted: Mon May 08, 2006 5:22 am    Post subject: Reply with quote

I've had problems cutting and pasteing into Base on numerous things. Have you tryed just the straight fowrard Importing a Spreadsheet? What you need to do is open up Base, and select 'Connect to an Existing database' and select Spreadsheet from the dropdown menu. The next window asks you to Browse for your Spreadsheet. Locate it and follow the Wizard instructions. By doing it this way, you can set up your Primary Key after the importing is done by - right click the table you have newly imprted and selecting 'Edit'. Try this, if you havent already, and see if this helps.

Hope this helps a little,

Ll.
Back to top
View user's profile Send private message
donlinux
Power User
Power User


Joined: 07 Jul 2005
Posts: 61

PostPosted: Tue May 09, 2006 2:00 am    Post subject: Reply with quote

I tried connecting to a database/spreadsheet. It imported the table and I could view it but it would not allow any editing, all features were grayed out, including the main table page which wouldn't allow me to add a table either in design or wizard form.
Back to top
View user's profile Send private message
llinosh
General User
General User


Joined: 13 Apr 2006
Posts: 7

PostPosted: Tue May 09, 2006 5:24 am    Post subject: Reply with quote

I'm sorry - I've tryed it again and you are right! I have had it work once, must be another bug I had not seen. I'm currently writing a help book for Base under a translation project here in Wales, and this is the kind of problem that I have been continously getting. One day it works, another it doesnt - not much help to you I know, please accept my appology. If I come accross a solution, I'll let you know.

Ll.
Back to top
View user's profile Send private message
NigelRoot
Newbie
Newbie


Joined: 05 May 2011
Posts: 1

PostPosted: Thu May 05, 2011 9:27 am    Post subject: How to join two tables? Reply with quote

I had a look at this today, using OpenOffice 3.3.0, build 9567 in Win7 and I think I've got there. Decide which table is the ONE end of the relation and which table is the MANY end. Then the field at the ONE end simply must be the primary key in that table. Set that then it works! There's a hint of this in the Help on Relations, under 'Primary key and other key'.
Regards,
Nigel
Back to top
View user's profile Send private message
Filmor
Newbie
Newbie


Joined: 17 Mar 2014
Posts: 2

PostPosted: Mon Mar 17, 2014 10:30 pm    Post subject: Reply with quote

Relationships require 1)same 'Field Name', 2) one of the two to be an 'IDkey' 3) must share same 'Field Type' 4) the IDkey is always the '1' and the other is the 'n'.
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