Re: [libreoffice-users] Re: Base scenario
Hi :) So in the editing can you change the direction? or rather the way that the relationships works? Regards from Tom :) From: Girvin R. Herr girvin.h...@sbcglobal.net To: Tom Davies tomdavie...@yahoo.co.uk Cc: Dan Lewis elderdanle...@gmail.com; users@global.libreoffice.org users@global.libreoffice.org Sent: Thursday, 10 January 2013, 21:44 Subject: Re: [libreoffice-users] Re: Base scenario Tom, Yes. I confirmed that right-clicking on a join line does allow deleting or editing. If I understand you correctly, no, relationship definitions should not be part of the back-end. The table relationship is defined for the back-end by the front-end through the SQL statements. For example, I have a table of suppliers, with names and addresses and other contact information. This table is related to almost all of my database (main) tables. Additionally, each main table has its own set of table relationships with other (sub?) tables, most of which are for selecting options with a join. Each record of these option tables contains a primary key and a text field for the option. For example, I have a table of statuses for the item in the main table record. An integer foreign key in the main table contains a primary key value corresponding to the text element of the statuses table record. That way, I am only storing an integer (key value) in the main table, rather than the option text, and with no repeated option text. It also standardizes the option texts. All of these multiple relationships must be defined by me - ergo it needs to be in the front-end. Hope this helps clarify this. Girvin Herr Tom Davies wrote: Hi :) Can you right-click on a relationship's join-line and edit it's properties? Shouldn't the relationships be part of the back-end rather than defined in the front-end? Regards from Tom :) From: Dan Lewis elderdanle...@gmail.com To: users@global.libreoffice.org Sent: Wednesday, 9 January 2013, 4:14 Subject: Re: [libreoffice-users] Re: Base scenario Comment inline below. --Dan On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the data elements of the first record to be -, which is my equivalent of unknown, just to have something to select that is not NULL. You could probably use a blank ( ), but I prefer seeing the - in forms and reports. Most times in reports, it is hard to see anyway. Seeing the - tells me the field is not NULL. Hope this helps. Girvin Herr These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value. Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B. Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL. Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which
Re: [libreoffice-users] Re: Base scenario
Tom, I can't seem to change the direction from the editing (Join Properties) dialog. If the direction needs to be changed, I have been selecting the line by right-clicking on it and selecting the Delete option. I then re-enter the relationship (Join) in the opposite direction as I had explained in my last posting to Dan et al. The type of Join can be changed in the right-click Edit option under the Join Properties dialog. There is a Type list box, which has the Inner, Left, Right, or Cross Join options. Under that list box in the dialog, there is a table labeled Fields involved, with two columns, left and right, corresponding to the left and right join ends. Under that panel, is a hint of sorts, that explains the Join type, the direction, and that warning about the join may not be supported, for all but when Inner is selected in the list box. There is another radio button labeled Natural for a Natural Join augmentation, but I am not familiar with that option. Hope this helps. Girvin Herr Tom Davies wrote: Hi :) So in the editing can you change the direction? or rather the way that the relationships works? Regards from Tom :) *From:* Girvin R. Herr girvin.h...@sbcglobal.net *To:* Tom Davies tomdavie...@yahoo.co.uk *Cc:* Dan Lewis elderdanle...@gmail.com; users@global.libreoffice.org users@global.libreoffice.org *Sent:* Thursday, 10 January 2013, 21:44 *Subject:* Re: [libreoffice-users] Re: Base scenario Tom, Yes. I confirmed that right-clicking on a join line does allow deleting or editing. If I understand you correctly, no, relationship definitions should not be part of the back-end. The table relationship is defined for the back-end by the front-end through the SQL statements. For example, I have a table of suppliers, with names and addresses and other contact information. This table is related to almost all of my database (main) tables. Additionally, each main table has its own set of table relationships with other (sub?) tables, most of which are for selecting options with a join. Each record of these option tables contains a primary key and a text field for the option. For example, I have a table of statuses for the item in the main table record. An integer foreign key in the main table contains a primary key value corresponding to the text element of the statuses table record. That way, I am only storing an integer (key value) in the main table, rather than the option text, and with no repeated option text. It also standardizes the option texts. All of these multiple relationships must be defined by me - ergo it needs to be in the front-end. Hope this helps clarify this. Girvin Herr Tom Davies wrote: Hi :) Can you right-click on a relationship's join-line and edit it's properties? Shouldn't the relationships be part of the back-end rather than defined in the front-end? Regards from Tom :) From: Dan Lewis elderdanle...@gmail.com mailto:elderdanle...@gmail.com To: users@global.libreoffice.org mailto:users@global.libreoffice.org Sent: Wednesday, 9 January 2013, 4:14 Subject: Re: [libreoffice-users] Re: Base scenario Comment inline below. --Dan On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point
Re: [libreoffice-users] Re: Base scenario
Ian How are you defining your primary keys and your foreign keys ? From your post, it looks like you have two tables, 'Members' and eMail. Implicit in your post it appears that the primary key of 'Members' is 'Record ID'. Also, the primary key of 'eMail' is 'Message ID' while a key called 'Members ID' is a foreign key in the 'eMail' table. I don't see anywhere that you have specifically designated these keys as primary and foreign keys. If you defined your tables in MySQL, I imagine you would have to add the foreign key constraint and designate the primary keys in your appropriate MySQL table definitions. If you do this, the relationships window should show a connection between Members.Record ID and eMail.Members ID. You might try , in the relationship window, to click on Record ID in the Members table and drag to Members ID in the eMail table. This dragging method works when using the embedded Database but I'm not sure if it will transmit the necessary information to the remote database. Hank On Tue, Jan 8, 2013 at 9:32 AM, Ian Whitfield whitfi...@telkomsa.netwrote: Hi All Re - My previous post Have been doing some Googling etc and found the 'Relationships Window' for setting Relationships. I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set any Relationships!!?? Is this another Gotcha of using MySQL and Base together? As it does _NOT_ seem to work at all!! I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55 IanW Pretoria RSA. -- For unsubscribe instructions e-mail to: users+help@global.libreoffice.** org users%2bh...@global.libreoffice.org Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-** unsubscribe/http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.**documentfoundation.org/** Netiquette http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.**libreoffice.org/global/users/http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
Tom, Yes. I confirmed that right-clicking on a join line does allow deleting or editing. If I understand you correctly, no, relationship definitions should not be part of the back-end. The table relationship is defined for the back-end by the front-end through the SQL statements. For example, I have a table of suppliers, with names and addresses and other contact information. This table is related to almost all of my database (main) tables. Additionally, each main table has its own set of table relationships with other (sub?) tables, most of which are for selecting options with a join. Each record of these option tables contains a primary key and a text field for the option. For example, I have a table of statuses for the item in the main table record. An integer foreign key in the main table contains a primary key value corresponding to the text element of the statuses table record. That way, I am only storing an integer (key value) in the main table, rather than the option text, and with no repeated option text. It also standardizes the option texts. All of these multiple relationships must be defined by me - ergo it needs to be in the front-end. Hope this helps clarify this. Girvin Herr Tom Davies wrote: Hi :) Can you right-click on a relationship's join-line and edit it's properties? Shouldn't the relationships be part of the back-end rather than defined in the front-end? Regards from Tom :) From: Dan Lewis elderdanle...@gmail.com To: users@global.libreoffice.org Sent: Wednesday, 9 January 2013, 4:14 Subject: Re: [libreoffice-users] Re: Base scenario Comment inline below. --Dan On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the data elements of the first record to be -, which is my equivalent of unknown, just to have something to select that is not NULL. You could probably use a blank ( ), but I prefer seeing the - in forms and reports. Most times in reports, it is hard to see anyway. Seeing the - tells me the field is not NULL. Hope this helps. Girvin Herr These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value. Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B. Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL. Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which the foreign key does not have a value that matches any value of the primary key, the fields in the left side of it will be NULL. Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In this case, each row of table A is joined to all the rows of table B. This contains all of the possible combinations of combining both tables. Usually, some rows of the output will have the table A fields all showing NULL while others will have the fields of table B
Re: [libreoffice-users] Re: Base scenario
Dan Lewis wrote: Comment inline below. --Dan On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the data elements of the first record to be -, which is my equivalent of unknown, just to have something to select that is not NULL. You could probably use a blank ( ), but I prefer seeing the - in forms and reports. Most times in reports, it is hard to see anyway. Seeing the - tells me the field is not NULL. Hope this helps. Girvin Herr These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value. Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B. Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL. Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which the foreign key does not have a value that matches any value of the primary key, the fields in the left side of it will be NULL. Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In this case, each row of table A is joined to all the rows of table B. This contains all of the possible combinations of combining both tables. Usually, some rows of the output will have the table A fields all showing NULL while others will have the fields of table B showing all NULL. Dan, Your and Winston's explanations of joins cleared up a lot of my misunderstanding of them. You can only get so much from books. The fact that my using an Inner Join requires non-null values to get a complete result set makes a lot of sense now. If I now understand correctly, I should be using a Left Outer Join. That way I always get the table A data in the result set, but table B data may be null if there is no match to table A. That is the response I was looking for. I just tried it and it does give me the same result set information (no records missing) as the Inner Join. However, I have no Null values in table A. It may take me a little while to test it with Nulls in the Left (table A) side. I also recant and apologize for my stupid remark about SQL losing data. The reasons for the missing data are now apparent to me and that remark was premature. While I was doing this join editing testing on my sample query, I took time to look at the order of joining in the Query Design window and it looks like in order to get the main table (with foreign key) as table A, I had to first click on the sub table (table B) element and then drag to the table A element. In other words, it needs to be primary key - foreign key. My original response to Ian was not sure of this drag order. In any case, the Join Properties dialog and prompt show the correct order in the Fields Involved window, with table A in the left column and table B in the right column. Thanks for the help. Girvin Herr snip -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems?
Re: [libreoffice-users] Re: Base scenario
Hi :) Can you right-click on a relationship's join-line and edit it's properties? Shouldn't the relationships be part of the back-end rather than defined in the front-end? Regards from Tom :) From: Dan Lewis elderdanle...@gmail.com To: users@global.libreoffice.org Sent: Wednesday, 9 January 2013, 4:14 Subject: Re: [libreoffice-users] Re: Base scenario Comment inline below. --Dan On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the data elements of the first record to be -, which is my equivalent of unknown, just to have something to select that is not NULL. You could probably use a blank ( ), but I prefer seeing the - in forms and reports. Most times in reports, it is hard to see anyway. Seeing the - tells me the field is not NULL. Hope this helps. Girvin Herr These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value. Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B. Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL. Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which the foreign key does not have a value that matches any value of the primary key, the fields in the left side of it will be NULL. Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In this case, each row of table A is joined to all the rows of table B. This contains all of the possible combinations of combining both tables. Usually, some rows of the output will have the table A fields all showing NULL while others will have the fields of table B showing all NULL. Ian Whitfield wrote: Hi All Re - My previous post Have been doing some Googling etc and found the 'Relationships Window' for setting Relationships. I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set any Relationships!!?? Is this another Gotcha of using MySQL and Base together? As it does _NOT_ seem to work at all!! I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55 IanW Pretoria RSA. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
I have the following - A Members Table called 'Members' with * The Key being 'RecordID'. INT, Auto, * Plus the rest of my 70-odd fields.. (This table comes up in my Main Form) 70 fields in one table? Sounds like this database may be de-normalised: http://en.wikipedia.org/wiki/Database_normalization I have now added another table called 'eMail' which has . * The Key being 'MessageID', INT, Auto * Plus the rest of the fields for Date, Type, Title and Message. (All TXT) * The final Field is called 'MembersID', INT, NOT Auto, You mention a Foreign Key' which maybe the answer to what I'm trying to do but how do I declare the RecordID/MembersID Relationship? http://en.wikipedia.org/wiki/Foreign_key The Libreoffice Base handbook hasn't been entirely translated yet: https://wiki.documentfoundation.org/images/b/bb/BG3401-IntroducingBase.pdf https://wiki.documentfoundation.org/images/6/65/PlanningDesigningYourDatabase_DEL_20120809.odt https://wiki.documentfoundation.org/images/0/06/Data_input_and_removal_DEL_20121106.odt Sincerely, Wolfgang -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
On 01/08/2013 09:32 AM, Ian Whitfield wrote: Hi All Re - My previous post Have been doing some Googling etc and found the 'Relationships Window' for setting Relationships. I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set any Relationships!!?? Is this another Gotcha of using MySQL and Base together? As it does _NOT_ seem to work at all!! I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55 IanW Pretoria RSA. No, it is not a Gotcha for using MySQL and Base. It may be a Gotcha because of how you have created the database. Creating a database is somewhat like building a house: you need to have planned out what you want first and how to create it. This does take time, but it is well worth it. Some to several problems that you have had is because there seems to be no well thought out plan/design for the database from the beginning. Part of this involves normalizing your tables. As stated, 70 fields in one table is very often an indication that the table is not normalized. This can lead to errors in entered data. The Primary-Foreign key pairs in MySQL are defined in views, queries, and forms. All of these are written to the Base field (.odb). What I think may work is to create a view that uses the essential fields from your 'Member' table and the needed fields from the 'eMail' table. Then you could create a form for this view that has the things you want. --Dan -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
Ian, Below, Girvin had a warning about joins. Specifically, he was talking about inner joins. SQL has different types of joins. Below, I explain them. Note: It has been some time since I used SQL. I might have errors. Sorry if you already know this information. But possibly it will be helpful to other people. Suppose that we join two tables, called table_a and table_b. Suppose that we join these two tables on columns column_a and column_b. Our join statement will looking something like: SELECT * FROM table_a INNER JOIN table_b ON table_a.column_a = table_b.column_b; Let a be any row in table_a that satisfies the join condition. Let b be any row in table_b that satisfies the join condition. Below, the text INNER and OUTER are optional when you type the joins in SQL. 1. An INNER JOIN gives you rows of the form (a, b). 2a. A LEFT OUTER JOIN gives you rows of the form (a, b) and (a, NULL). 2b. A RIGHT OUTER JOIN gives you rows of the form (a, b) and (NULL, b). 2c. A FULL OUTER JOIN gives you rows of the form (a, b), (a, NULL), and (NULL, b). 3. A CROSS JOIN is a Cartesian product between the rows of table_a and the rows of table_b. It gives you rows of the form (aa, bb), where aa is any row in table_a, and bb is any row in table_b. Winston On 01/08/2013 10:55 PM, Winston Chuen-Shih Yang wrote: Ian, Below, Girvin had a warning about joins. Specifically, he was talking about inner joins. SQL has different types of joins. Below, I explain them. Note: It has been some time since I used SQL. I might have errors. Sorry if you already know this information. But possibly it will be helpful to other people. Suppose that we join two tables, called table_a and table_b. Suppose that we join these two tables on columns column_a and column_b. Our join statement will looking something like: SELECT * FROM table_a INNER JOIN table_b ON table_a.column_a = table_b.column_b; Let a be any row in table_a that satisfies the join condition. Let b be any row in table_b that satisfies the join condition. Below, the text INNER and OUTER are optional when you type the joins in SQL. 1. An INNER JOIN gives you rows of the form (a, b). 2a. A LEFT OUTER JOIN gives you rows of the form (a, b) and (a, NULL). 2b. A RIGHT OUTER JOIN gives you rows of the form (a, b) and (NULL, b). 2c. A FULL OUTER JOIN gives you rows of the form (a, b), (a, NULL), and (NULL, b). 3. A CROSS JOIN is a Cartesian product between the rows of table_a and the rows of table_b. It gives you rows of the form (aa, bb), where aa is any row in table_a, and bb is any row in table_b. Winston On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the data elements of the first record to be -, which is my equivalent of unknown, just to have something to select that is not NULL. You could probably use a blank ( ), but I prefer seeing the - in forms and reports. Most times in reports, it is hard to see anyway. Seeing the - tells me the field is not NULL. Hope this helps. Girvin Herr Ian Whitfield wrote: Hi All Re - My previous post Have been doing some Googling etc and found the 'Relationships Window' for setting Relationships. I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set any Relationships!!?? Is this another Gotcha of using MySQL and Base together? As it does _NOT_ seem to work at all!! I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55 IanW Pretoria RSA. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems?
Re: [libreoffice-users] Re: Base scenario
Comment inline below. --Dan On 01/08/2013 07:07 PM, Girvin R. Herr wrote: Ian, Have you actually drawn any relationships? Base will not do that for you. Just adding the tables in the 'Relationships Window' will not create the relationships automagically. You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button. Base will then draw a line between the two. Note, the order of the drag is important. It determines the type of join. Joins are confusing to me too, so I can't help much there. I had to experiment with the direction to get it to work right. I think it was remote key to primary key, but I am not sure of that any more. Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing. IMHO, this is stupid (my mantra is: thou shall not lose data), but that is how the SQL language was set up. So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the data elements of the first record to be -, which is my equivalent of unknown, just to have something to select that is not NULL. You could probably use a blank ( ), but I prefer seeing the - in forms and reports. Most times in reports, it is hard to see anyway. Seeing the - tells me the field is not NULL. Hope this helps. Girvin Herr These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value. Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B. Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL. Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which the foreign key does not have a value that matches any value of the primary key, the fields in the left side of it will be NULL. Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In this case, each row of table A is joined to all the rows of table B. This contains all of the possible combinations of combining both tables. Usually, some rows of the output will have the table A fields all showing NULL while others will have the fields of table B showing all NULL. Ian Whitfield wrote: Hi All Re - My previous post Have been doing some Googling etc and found the 'Relationships Window' for setting Relationships. I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set any Relationships!!?? Is this another Gotcha of using MySQL and Base together? As it does _NOT_ seem to work at all!! I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55 IanW Pretoria RSA. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Base scenario
On 12/27/2012 10:09 AM, Ian Whitfield wrote: Hi Ian, So all I need now is for the Sub-Form to AUTOMATICALLY pick-up the Members ID from the main Form and to use as the filter criteria for which eMails to show. (I will also use this form to enter new eMails so the automatic pick-up of the Member's ID is also important here). I'm a bit lost here. Surely, the field used in your main table to show the Member's ID in your main form should reference the Member ID of your e-mail table, via a FOREIGN KEY relationship between your tables ? Perhaps I have misunderstood what exactly it is that you are trying to achieve or how exactly you have structured your tables ? If you need a value to appear in your main table, which is taken from your email table, then use the FOREIGN KEY relationship definition. If, however, you are inserting a value of MemberID into your e-mail table from your main table, then you need to have the MemberID field of your email table defined as a FOREIGN KEY reference for the main table. You can't expect both tables to know simultaneously whether to refer/receive at the same time, that won't happen as you would have created a circular reference. Your questions seem to me to be far more related to how to normalize your data relationships than how to get Base to do what you want with them, and would probably be better asked in a forum dealing with such questions, but I could be misreading / misunderstanding the whole point. Alex -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
Hi Ian, I'm not sure what you mean by automatic.Would you consider a list box with a drop down list of all the member names with the equivalent member ID being the input value to the subform automatic enough? I've used this approach many times on the project I'm working on. In your form edit mode in the design view, click on the list box icon and place a list box on your subform and follow the directions. Your source table for the list box display will be your Member table and the data to appear in the list box will be your member name attribute of that table. Finally, you will select the matching attributes from the two tables which links them. The Member ID number In the e-mail table and the Member ID from your member table (I'm guessing) will be the attributes you'll link. In this way, when you click on a name in the list box, you'll be inserting member ID into that form. Hank . On Thu, Dec 27, 2012 at 4:09 AM, Ian Whitfield whitfi...@telkomsa.netwrote: On 12/20/2012 04:18 PM, Alexander Thurgood wrote: Le 20/12/12 14:46, Ian Whitfield a écrit : Thanks for the feed-back Alex - appreciated!! OK - I have two Tables (Members and eMails). The Main Form brings up the Members Table with a button to pop-up a sub-Form with eMails that the Member has sent or I have sent to him/her. (This part I have working but at the moment it brings up ALL eMails!!) If your main form already contains the ID of the member, why don't you use the member ID as a filter criteria for your email form ? In form edit mode of your email subform, click on the form properties button, and then click on the three dots button to the right of the Filter field. This opens a filter dialog, where you can enter a Named Parameter for your filter, e.g. MemberID = :ID If you then save this Filter criteria, you will notice that the Filter field has been filled. You can then quit the Form Properties dialog and save your form. When you open your email subform, you will be asked to provide the ID of the member and the form will filter the results on the basis of that ID. Thanks Alex (Hope you and everyone had a great Xmas and Best Wishes for 2013!!) Your reply to me has made me change my layout of my Forms. I have removed all fields from my Sub-Form except the two IDs, the date, Sent/Received box, Message title and the message itself. The Button on the main Form I have re-labeled as eMails. So all I need now is for the Sub-Form to AUTOMATICALLY pick-up the Members ID from the main Form and to use as the filter criteria for which eMails to show. (I will also use this form to enter new eMails so the automatic pick-up of the Member's ID is also important here). I have tried a couple of syntaxes to do this with no luck - it always asks me for the Members ID!! Is there a way to automate this? I thought that I could use 'MembersID' (on eMail SubForm) = 'SchemaName.TableName.**FieldName' would work but so far no luck. Thanks for any advise. IanW Pretoria RSA -- For unsubscribe instructions e-mail to: users+help@global.libreoffice.** org users%2bh...@global.libreoffice.org Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-** unsubscribe/http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.**documentfoundation.org/** Netiquette http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.**libreoffice.org/global/users/http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
Hi Ian, First things first. I'm assuming that if you were to manually insert the Member ID in the appropriate text box on the subform, then the emails you're searching for will appear. That is the impression I received from reading your last post on this topic. Is this the case? If so, the list box should be able to solve the problem. If your form doesn't behave that way, I don't understand your problem. As for concatenating First and Last Names, I have been working with the embedded HSQL in LO and my MYSQL is a bit rusty. However I think using the UPDATE key word is incorrect as you've applied it. You are , I think, trying to add a new Column, ie. FullName to your member table, and populate it with the full names of your members. . UPDATE is used to change values. The keywords to add columns to a table is ALTER TABLE tablename ADD [COLUMN] columname datatype. Check my syntax it's rusty! Then you'll have to insert the full names into the new column in the altered table. Why not create a view instead, using the CONCAT function. Concatenating last name and first name in MYSQL and creating a view with the concat function: CREATE VIEW viewname SELECT Member ID, CONCAT (LastName, ' ',FirstName) AS FullName FROM TableName; to check if this works execute this select statement: SELECT* FROM viewname Your list box should work from this view if properly set up. Hank. On Tue, Jan 1, 2013 at 9:53 AM, Ian Whitfield whitfi...@telkomsa.netwrote: *Hi Hank* Thanks for your reply to me - appreciated!! *On 01/01/2013 10:21 AM, Hank Alper wrote: * *Hi Ian, I'm not sure what you mean by automatic.Would you consider a list box with a drop down list of all the member names with the equivalent member ID being the input value to the subform automatic enough? I've used this approach many times on the project I'm working on. In your form edit mode in the design view, click on the list box icon and place a list box on your subform and follow the directions. Your source table for the list box display will be your Member table and the data to appear in the list box will be your member name attribute of that table. Finally, you will select the matching attributes from the two tables which links them. The Member ID number In the e-mail table and the Member ID from your member table (I'm guessing) will be the attributes you'll link. In this way, when you click on a name in the list box, you'll be inserting member ID into that form. Hank * This sounds interesting and I can live with this answer although I must say I find it strange that My SQL can not automatically pick up a value like this!! Something like - MembersID = DatabaseName.TableName.FieldName or maybe MembersID = FieldName IN Parent Form Anyway - as I said I can use your idea. SO - I have set-up the List Box as you suggested and it brings-up all the names but does NOT put in the MembersID but rather just the Name?? Secondly - at the moment I have First Names and Surnames in separate fields so I decided to create a new field called FullName and now I'm trying to automate this. Googling for info on this I found the perfect answer to what I'm doing here . http://stackoverflow.com/questions/5774532/mysql-combine-two-columns-and-add-into-new-column But all it gives me is a Syntax error. I have looked and looked and found lots of other answers but they ALL give me syntax errors. I'm using MySQL 5.1.55 with Base as the front-end and putting in update table set FullName = concat(PrefFirstName, ' ', LastName); If tried everything I can think of, CONCAT_WS and looked at GROUP_CONCAT but no luck!! Can you see my error?? Last question - Will I have to add a Find button to my subForm to go and get all the respective eMails for each member or will a filter do the trick? Thanks for your help - appreciated. -- -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
On 12/20/2012 04:18 PM, Alexander Thurgood wrote: Le 20/12/12 14:46, Ian Whitfield a écrit : Thanks for the feed-back Alex - appreciated!! OK - I have two Tables (Members and eMails). The Main Form brings up the Members Table with a button to pop-up a sub-Form with eMails that the Member has sent or I have sent to him/her. (This part I have working but at the moment it brings up ALL eMails!!) If your main form already contains the ID of the member, why don't you use the member ID as a filter criteria for your email form ? In form edit mode of your email subform, click on the form properties button, and then click on the three dots button to the right of the Filter field. This opens a filter dialog, where you can enter a Named Parameter for your filter, e.g. MemberID = :ID If you then save this Filter criteria, you will notice that the Filter field has been filled. You can then quit the Form Properties dialog and save your form. When you open your email subform, you will be asked to provide the ID of the member and the form will filter the results on the basis of that ID. Thanks Alex (Hope you and everyone had a great Xmas and Best Wishes for 2013!!) Your reply to me has made me change my layout of my Forms. I have removed all fields from my Sub-Form except the two IDs, the date, Sent/Received box, Message title and the message itself. The Button on the main Form I have re-labeled as eMails. So all I need now is for the Sub-Form to AUTOMATICALLY pick-up the Members ID from the main Form and to use as the filter criteria for which eMails to show. (I will also use this form to enter new eMails so the automatic pick-up of the Member's ID is also important here). I have tried a couple of syntaxes to do this with no luck - it always asks me for the Members ID!! Is there a way to automate this? I thought that I could use 'MembersID' (on eMail SubForm) = 'SchemaName.TableName.FieldName' would work but so far no luck. Thanks for any advise. IanW Pretoria RSA -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Base scenario
Le 18/12/12 12:04, Ian Whitfield a écrit : Hi Ian, On my Main Membership Form I have added a button to bring-up the eMail SubForm. (I have this part working!) But what I see as the best way to do the last step is to add a ListBox named Find Member to the SubForm and this will read from a Query holding the concatanated First Name and Surname of each Member and that Member's ID Key. Once these are inserted into the SubForm it will extract all the relative eMails, sort them into date order and display them. What do you mean exactly by inserted into the Subform ? Your e-mail subform just contains e-mails, or does it hold something else ? I imagine that there is a foreign key reference to the member in the e-mail table ? I'm a bit confused about what you're trying to achieve here. So - Questions and Advice please on the following... * Is this the right way to approach this requirement? * Will the Query update automatically each time it is called so as to pick-up new eMails? * How do I make the ListBox 'read' the results of the Query? * How do I make these results automatically insert into their respective fields on the SubForm? * How do I make the SubForm do the sort as a final step? (Maybe a 'Sort' Button??) As far as I can see, the only way to achieve what you want to do is via macros, as it looks like you want to retrieve a dataset from a filter or query, and then inject part of the information from that dataset into another table ? Alex -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Base scenario
On 12/20/2012 01:52 PM, Alexander Thurgood wrote: What do you mean exactly by inserted into the Subform ? Your e-mail subform just contains e-mails, or does it hold something else ? I imagine that there is a foreign key reference to the member in the e-mail table ? I'm a bit confused about what you're trying to achieve here. So - Questions and Advice please on the following... * Is this the right way to approach this requirement? * Will the Query update automatically each time it is called so as to pick-up new eMails? * How do I make the ListBox 'read' the results of the Query? * How do I make these results automatically insert into their respective fields on the SubForm? * How do I make the SubForm do the sort as a final step? (Maybe a 'Sort' Button??) As far as I can see, the only way to achieve what you want to do is via macros, as it looks like you want to retrieve a dataset from a filter or query, and then inject part of the information from that dataset into another table ? Thanks for the feed-back Alex - appreciated!! OK - I have two Tables (Members and eMails). The Main Form brings up the Members Table with a button to pop-up a sub-Form with eMails that the Member has sent or I have sent to him/her. (This part I have working but at the moment it brings up ALL eMails!!) The second Form is for me to enter the eMails into the eMail Table. For this I need to be able to select the Member to get the Key ID so that only the eMails to and from the Member are found later for the pop-up Sub Form on the Main Form. Once I have selected the correct member it would be good to extract other needed fields from the Members Table to go into the eMails Form as well. (On reflection - - I think this last step could be done away with provided I can at least get the Member's ID Key out of the Members Table and insert it into the eMail Form as the Foreign Key). Thanks for your help and I hope this makes it clearer what I'm trying to do. IanW Pretoria RSA -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Base scenario
Le 20/12/12 14:46, Ian Whitfield a écrit : Thanks for the feed-back Alex - appreciated!! OK - I have two Tables (Members and eMails). The Main Form brings up the Members Table with a button to pop-up a sub-Form with eMails that the Member has sent or I have sent to him/her. (This part I have working but at the moment it brings up ALL eMails!!) If your main form already contains the ID of the member, why don't you use the member ID as a filter criteria for your email form ? In form edit mode of your email subform, click on the form properties button, and then click on the three dots button to the right of the Filter field. This opens a filter dialog, where you can enter a Named Parameter for your filter, e.g. MemberID = :ID If you then save this Filter criteria, you will notice that the Filter field has been filled. You can then quit the Form Properties dialog and save your form. When you open your email subform, you will be asked to provide the ID of the member and the form will filter the results on the basis of that ID. The second Form is for me to enter the eMails into the eMail Table. For this I need to be able to select the Member to get the Key ID so that only the eMails to and from the Member are found later for the pop-up Sub Form on the Main Form. Once I have selected the correct member it would be good to extract other needed fields from the Members Table to go into the eMails Form as well. (On reflection - - I think this last step could be done away with provided I can at least get the Member's ID Key out of the Members Table and insert it into the eMail Form as the Foreign Key). For this, you probably need to go and check out the OOo users forum and ask your question there, this kind of question has been asked there many times before and will probably require a macro. Alex -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
