Table relationship and forms

[gijimbo]

I am entirely new to databases. This is the first time I've ever tried to make one but now's as good a time as any to learn.
I've been trying to do all the learning by going through tutorials I've found online. However I've gotten stuck and I'm pretty sure it's because I do not fully understand the relationship between "table relationships" and "forms".

I'm trying to make a database to help me manage tasks, projects and priorities at work. I wanted to organize things as follows:
- There are 4 main categories that projects fall under. I've made a Categories table that contains Category and *CategoryID fields.
'*' designates primary key.
- There can be any number of projects but each one must fall under one and only one of the categories. I have a Projects table that contains Project, *ProjectID, and CategoryID fields.
- Now tasks get a little trickier because the same task can actually fall under multiple projects. After a bit of research it sounds like I need to use a "many-to-many" relationship for this. My Tasks table contains *TaskID, Task, StartDate, StatusID, LeadTime, EndDate, and Notes fields.
- Since I have a many-to-many relationship there's another table named Proj_Task with *ProjectID and *TaskID fields.
- My final table contains the various Status types. The table is named Status with Status and *StatusID fields.

I set up the relationships as shown in the attached image.

I want to have a form for the creation of new projects. What I envisioned was a form containing a dropdown box in which you select the desired category and a subform (as a datasheet) containing the list of all current projects that fit under that category under which you can add another project.

The problem is, I can't make a form that does this.

Edit: I've attached what I want the form to look like but it does not work.

[Arineckaig]

The problem is, I can't make a form that does this.

Welcome to the forum. It is not immediately clear from your post just what your form document fails to do. If the two 'link' fields properties have been correctly set to the "CategoryID" field for each, then selection of a record in the main (Category) form will automatically filter the (Projects) sub-form.

I suspect the problem lies in the use of a List or Combo box for the Category field in the main form to make that selection. Such boxes in Base are essentially data entry controls. Use instead a simple Text box and then as you use the navigation toolbar at the foot of the form document to move the record pointer through the Category records displayed in the main form, the linked sub-form will automatically update to filter and display the project records linked to each category in turn.

As you have only four categories the navigation toolbar will quickly move throught them. If you were to have many more I suggest the main form has a table/grid control with a single sorted 'Category' text column: selection of any record from that displayed list will immediately update the sub-form.

The essence of the form/sub-form paradigm in Base is that a sub-form is the result of a SELECT statement, supplied by the GUI, with the WHERE clause being the content of the two sub-form's 'Link' fields.

Please come back if this fails to work - in which case it would make it easier to offer help if you were to attach to the post a copy of your .odb file with a sensitive data removed.

[gijimbo]

I think I understand what you are saying. I also think you understand what I'm trying to do; you explained it right anyways. Therefore, would I be right in assuming that what I wanted to do is not really possible with a form in OpenOffice? As you said, there are only a few categories so it really shouldn't be a problem to just "navigate" through each of them for this form. However, I had eventually planned on having a more detailed task entry form in which you could create a new task, choose a category then select one of the projects under that category or create a new project under the selected category... all on the same form. Is this also not going to be possible? Sorry, like I said in the OP this is the first time I've done any programing with a database. I guess I have more of a programing background (lots of C++/C, and Basic) so I had thought creating a database would have been relatively simple and straight forward... maybe it is once you get used to it (just like everything else) but at this point I'm a bit lost and confused.

Are there any good database relationship tutorials you could point me to that involve form creation?

[DACM]

Hi gijimbo,

Arineckaig is more than capable of stepping you through this request so I hesitate to offer assistance that deviates from that discourse. But you've definitely got a handle on relational database design! So don't jump off-track with this assumption:

...would I be right in assuming that what I wanted to do is not really possible with a form in OpenOffice?

That's not the case.

Arineckaig was simply offering you some simple solutions first. Those are (in order of simplicity):
1. Use a Text Box with Nav Bar or dedicated Push Buttons to filter the Projects SubForm on-the-fly
2. Use a Table Control grid to use point-and-click for Category selection which also filters the Projects SubForm on-the-fly
3. Use a List Box as you've done, but add a 'Refresh' Push Button to the SubForm to force a manual filtering of the Projects
4. Use a List Box as you've done, but add a Macro to eliminate the Push Button to filter the Projects SubForm on-the-fly

These and other SubForm filtering techniques are presented here:
[Example] Filter/Search with Forms (leveraging SubForms)
...which includes several tutorials and related links under the heading: "More Information"

[DACM]

...form in which you could create a new task, choose a category then select one of the projects under that category or create a new project under the selected category... all on the same form. Is this also not going to be possible?

And if I may...

That's possible as well, using cascading List Boxes either with or without Macros. But that's a very aggressive Form (based on your nicely normalized database design) because it's not possible to add a new Project using the same List Box used to store the ProjectID to the Proj_Task junction table (without some convoluted Macro logic). But you could add another MainForm to your Form based on the Projects table with another Category List Box and a Text Box to add new Projects. Or you could add a Push Button to pop-up a separate Form for this New Projects task...see the Form: 'Titles ( button switchboard )' and related Macro in Songs - List Box Switchboard.odb
...

[gijimbo]

Thanks! I'll look your suggestions over tomorrow.

[Arineckaig]

I had thought creating a database would have been relatively simple and straight forward... maybe it is once you get used to it (just like everything else) but at this point I'm a bit lost and confused.

Let me assure you we have all been there when starting with Base and the task is made no easier if you come new to creating a database with a strong programming background. DACM has offered you a comprehensive list of excellent potential solutions that may require varying levels of expertise. As an alternative starting point but only if you have the persistence to work thorough a tedious tutorial, I would dare to suggest a crude set of notes I prepared on the form/subform paradigm: some people have been so kind as to say they were helpful. Together with a demo Base file they can be downloaded from:
http://dl.dropbox.com/u/10552709/FilterExamples.zip