Edit answer tables and joining tables

[Lasse C]

Hi!

I'm new to databases. Maybe these questions should have been in separate threads, but in what I'm trying to do I think they are closely related, and discussing one problem in two threads could be very confusing.

I'm trying to build a database with several joined tables, and then create queries that present different parts of the total data in different ways. These queries collects data from more than one base table.

My first question is, is data in query answer tables ever, at any time, editable in OO Base?
This is a fundamental question of great importance that I've tried to find an answer to, but so far I haven't found a clear yes or no. I have found other people asking the same or similar questions in OpenOffice.org Forum, as have I, but yet not a clear answer to this fundamental question.

Every query I've built, even those based on single tables in databases with only one table, presents the data uneditable. I cannot change, delete or add new records in any of these answer tables.


My second question is, are there other ways to join tables than using queries?
I'm asking this because I cannot edit data through queries. So far it seems to me that it is necessary to create a query to join tables. I haven't a found way to just establish joins between tables without using a query.


So the problem looks like this. Since the data in the database I'm trying to build is split up into several tables I need to join the tables so that data is presented in an understandable user friendly way. The only way I have found to do this is by building queries that collect the data I'm intrested in in a specific query from several base tables.
But that leaves the answer tables uneditable. So when I want to change, delete or add new records in these answer tables I have to do that by editing, deleting or adding new data directly in the different base tables or using forms based directly on the base tables.
In using a database containing several joined tables this is of course an unacceptably complicated way to work.

All data fields in the base tables are set not to require input, except for the primary key fields which are all numerical ID-fields set to autovalue, so there should be no data integrity problem that prevents me from editing data in different base tables through the same query answer table.

I haven't tried MS Access myself but I know that data in base tables can be edited through query answer tables in MS Access. I took for granted that this was also possible in OO Base since it seems to me to be an essential part of using multiple tables to store data.

Regards.
Lasse C

[cscj01]

"My second question is, are there other ways to join tables than using queries?"

You can create a View in OO Base. Just open your database, click 'Tables', then 'Create View'. You can then choose the tables you want to query, the columns from those tables, and the type of join (use 'Insert' from the Main Menu, and choose 'New Relation'). Unfortunately, the Views I have created do not allow updates, even though I can update Views in my MySQL DB to which I am connected from within MySQL Query Browser. As a front-end, I am finding OO Base user unfriendly.

[Lasse C]

Thank you for your suggestion about views. I haven't explored how views work yet.

So far as I've searched the OpenOffices forums, and in the threads I've taken part in myself, I haven't come across a single post from a user of OO Base who clearly states query answer tables can be editable. So I'm beginning to think they never can. Not good at all! As I said, I took it for granted when I started exploring and working with OO Base recently after reading about relational databases in general.

If that is the case I can either wait and hope for OpenOffice to include updating through queries in a future edidion, which for some reason doesn't seem very likely, or start looking for another database software, free or not. I take the liberty to say that I welcome any suggestions, well aware that this is an OpenOffice forum.

Either way, query answer tables can be editable or not, I'd really like to know. And I know many others would too. I've read this question or similar ones over and over again in the forums, but not one clear yes or no so far. Someone out there must know. I don't know where to ask except here in the OpenOffice forums.

If this seems like I'm generally complaining about OO Base, which after all is totally free, that is not what I mean. Learning multi table database software is hard work (at least I think it is), and I would really like know if OO Base is the right choise for me before putting too much effort into learning it.
Thanks in advance!

Regards.
Lasse C