| View previous topic :: View next topic |
| Author |
Message |
talikarng
Newbie
Joined: 10 Mar 2011
Posts: 4
|
 Posted: Sun Mar 13, 2011 7:44 pm Post subject: [Solved] Unsure how to build form with three tables |
 |
|
Hello,
I am trying to build a form to manage three tables (and possibly a query) which records and lists who has volunteered (if anyone) for a particular event. On the form I would like to be able to navigate to the duty and on a subform see the list of people attending and add/remove people if I need to. (I tried to use a query in the subform which combined all of the tables but I realised that I couldn't edit the query)
I have three tables built like this:
Table_MemberList
MemberID (primary key - set by me)
FirstName
LastName
Table_EventList
EventID (primary key - auto incremented)
PDN
Name
Date
Table_EventRegistrations
RegistrationID (primary key - autoincrement)
EventID (same as EventID in Table_EventList)
MemberID (same as MemberID in Table_MemberList)
I have not set up any relationships between the tables (because the MemberID and EventID will not change), but I use this query
| Code: |
"SELECT `Table_EventList`.`EventID`, `Table_EventList`.`EventDate`, `Table_EventList`.`PDN`, `Table_EventList`.`EventName`, `Table_MemberList`.`MemberID`, `Table_EventList`.`StartTime`, `Table_EventList`.`EndTime`, `Table_MemberList`.`FirstName`, `Table_MemberList`.`LastName` FROM { OJ `stjohn`.`Table_EventRegistrations` AS `Table_EventRegistrations` RIGHT OUTER JOIN `stjohn`.`Table_EventList` AS `Table_EventList` ON `Table_EventRegistrations`.`EventID` = `Table_EventList`.`EventID` LEFT OUTER JOIN `stjohn`.`Table_MemberList` AS `Table_MemberList` ON `Table_EventRegistrations`.`MemberID` = `Table_MemberList`.`MemberID` } ORDER BY `Table_EventList`.`EventDate` ASC, `Table_EventList`.`EventName` ASC"
|
to show each event and who is going to attend. (The left and right joins are to make sure that all events are displayed regardless of whether someone has volunteered to attend or not).
Can someone please point me in the right direction or show an example of this in action. On the form I would like to be able to navigate to the duty and on a subform see the list of people attending and add/remove people if I need to.
Last edited by talikarng on Tue Mar 15, 2011 3:39 pm; edited 1 time in total |
|
| Back to top |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Mon Mar 14, 2011 2:26 am Post subject: |
|
|
Hello
In this link you can find some tutorials who can important for you.
Learn first to make use of the forms and subforms. When you need a query based on two table use this query only for a form for selecting data. The form you want edit data must be based on query.
Romke |
|
| Back to top |
|
|
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 332
|
| Posted: Tue Mar 15, 2011 1:37 am Post subject: |
|
|
| Quote: | | On the form I would like to be able to navigate to the duty and on a subform see the list of people attending and add/remove people if I need to. (I tried to use a query in the subform which combined all of the tables but I realised that I couldn't edit the query) |
As RPG suggests it is a question of using a form and its sub-form. There would appear to be a typical Many-to-Many relationship between the Members and the Events. You have correctly created a linking table between these two main tables.
It will require a main form based on the "Table_EventList" table and a sub-form based on the "Table_EventRegistrations" table: the linking fields between the two should be their respective "EventID" fields.
In the sub-form you will need to 'replace' the "MemberID" field with a ListBox so that it displays a list of Member names from the "Table_MemberList" table though any selection writes the member's related ID to the "MemberID" field in the "Table_EventRegistrations" table. This ListBox should have an SQL statement as its 'Type of list contents':
SELECT CONCAT( "FirstName"|| ' ' || "LastName") as "Full Name", "MemberID" FROM "Table_MemberList".
It is easier to demonstrate this than explain so I have posted a crude example that can be downloaded from here.
I have deliberately included all the fields in the respective forms and sub-forms to demonstrate how the records displayed in the sub-form change to match a record change in the main form. It also shows how the ID field, relevant to whatever record is displayed in the main form, is automatically supplied when a new selection is made from the sub-form's list box. In normal use the sub-form should comprise just this list box. Names can then be added from the list or records in the sub-form deleted.
For simplicity, the file includes two form documents so that either new events or new members may be added to the respective tables by using the main form in each respective form document. It is simpler to do this initially by using two separate form documents, though there are more complex methods by which both operations can be completed within a single form document.
Please come back if you have problems or need further clarification.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu |
|
| Back to top |
|
|
talikarng
Newbie
Joined: 10 Mar 2011
Posts: 4
|
| Posted: Tue Mar 15, 2011 3:49 pm Post subject: |
|
|
Thankyou to both of you.
I was looking at the example suggested by RPG which was exactly what I was looking for but couldn't work out how they linked in the third table. I didn't realise that the data field could refer to a third table.
Just a clarification:
Arineckaig, in see the in SELECT statements you select two columns (one is a CONCAT of two columns and the other is the ID). Is this telling the form to display the first column but when selected write the second column into the table? |
|
| Back to top |
|
|
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 332
|
| Posted: Wed Mar 16, 2011 2:41 am Post subject: |
|
|
| Quote: | | Is this telling the form to display the first column but when selected write the second column into the table? |
Yes, this is the essential feature of a List box in Base. It displays a list of items represented by the first field in the SELECT statement but writes the content of the matching second field of the SELECT statement, when a selection is made from the list, to whatever field is set as the 'Data field' property for the List box as shown under its 'Data' tab list of properties. Thus three distinct fields are always involved in a List box: that rule applies even if the same field were to be used for more than one of these elements.
Furthermore the use of the SQL statement permits data to be read from any table but written to another: the two tables do not even need to be linked, but the data type of the second field in the SELECT statement must match that of the 'Data field' property.
I find it often helps to read a description from more than one source. You should find a good explanation of the List box form control in the post by DACM in the other forum at http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=35924#p164740.
You will also find this all in the help file of OOo Base, but it requires careful study: I sometimes suspect a computer program may have translated the Base Help File from its original language. |
|
| Back to top |
|
|
|
