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

Editable records in a query

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


Joined: 21 Nov 2006
Posts: 28

PostPosted: Mon Jun 29, 2009 2:20 pm    Post subject: Editable records in a query Reply with quote

Is it possible to configure a query of two tables (joined on an ID field) such that the records can be edited directly? Everything I try does not work (tried native and postgresdb backend). This is a really powerful default feature of Access.

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


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary

PostPosted: Mon Jun 29, 2009 10:49 pm    Post subject: Reply with quote

Edit:I not read you post correctly, my answer was wrong, Villeroy post is the answer.

Last edited by r4zoli on Tue Jun 30, 2009 4:13 am; edited 2 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jun 30, 2009 1:58 am    Post subject: Reply with quote

Forms with subforms allow editing across tables. You can lay out the form controls so the user sees only one form with ediable controls.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
rancor
General User
General User


Joined: 21 Nov 2006
Posts: 28

PostPosted: Tue Jun 30, 2009 1:22 pm    Post subject: Bummer Reply with quote

Bummer subform functionality is OK, but just not the same thing as having in one table. When you have one to many condition subform doesn't show relationships you want to see every thing in one place. An ugly compromise is to have a third subform that is just a query "report" of the two tables that auto refresh when changes are made.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jun 30, 2009 1:44 pm    Post subject: Reply with quote

http://user.services.openoffice.org/en/forum/download/file.php?id=649 by Drew Jensen.
Many-to-many relation in form "Movies" where each movie belongs to more than one genre in a "nested list box" (a table control with a column of listboxes).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
amzg
General User
General User


Joined: 04 Jul 2009
Posts: 13

PostPosted: Fri Jul 17, 2009 5:13 pm    Post subject: Reply with quote

Villeroy wrote:
http://user.services.openoffice.org/en/forum/download/file.php?id=649 by Drew Jensen.
Many-to-many relation in form "Movies" where each movie belongs to more than one genre in a "nested list box" (a table control with a column of listboxes).


How can this many-to-many relation actually be seen, other than by going through the records in the form Movie? I ask to get a clue on how to create this and to understand the structure.

In the Relationships the links read "1-n" but we see double PKs there and in the MovieGenre table (how are two PK's accomplished by the way!?) - is this a clue to the many-to-many relation?

Or can the many-to-many relation be identified in the Form Naigator tree structure for the form Movie?

Thank you!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jul 18, 2009 6:13 am    Post subject: Reply with quote

menu:Tools>Relations
There are 3 tables:
[Movies] -->[Movies_Genres]<--[Genres]
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
amzg
General User
General User


Joined: 04 Jul 2009
Posts: 13

PostPosted: Sat Jul 18, 2009 8:33 am    Post subject: Reply with quote

Villeroy wrote:
menu:Tools>Relations
There are 3 tables:
[Movies] -->[Movies_Genres]<--[Genres]


Thank you Villeroy. Follow up question, trying to conclude a general approach on how to make "tables (joined on an ID field) such that the records can be edited directly" (ref original post). Let's call it "direct editability" here:

Is it correct that "direct editability" is achieved with:
a) the relations , i.e here manifested via the common Movies_Genre table, and
b) generally made possible by use of "connection table" or "link table"
(BTW, is connection/link table established terminology and common practice?)

And is perhaps this 'direct editability' also dependent on
c) the fact that Movie_Genres contain two PK's, and...
d) for the general case; a link table where all fields are PK for everything involved that is to be "directly editable"?


Thank you
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jul 18, 2009 9:55 am    Post subject: Reply with quote

There ar 2 different programs involved.
1. The database backend, which is represented in the table design and the relation window. If the left part on the status bar shows HSQLDB then you are working with the database engine documented here.
The database is a storage system where you can define valid rows. A relation determines the valid entries of a field (only existing genres for existing movies in "Movies_Genres"). In a properly designed relational database is not possible to enter invlid records.

2. The frontend, which can be Base, Access, a tool set like this forum's software, a script, a command line or something else that can be connected with a database. Usual frontends for humans allow for queries (virtual tables of recombined table data), input forms and output records.

So you create input forms on top of an existing database structure. The main prupose of a form is that you edit more than one table at the same time. In the movies form you edit the connector table which consists of numeric identifiers only as well as the movies' table.

The main purpose of Base is that you can connect with some database backend (MySQL, Postrgre, Access, Oracle, MS SQL and many others) in order to use the input forms for input, reports for output and several other ways to use database data in this office suite (mail merge, spreadsheets, ...)
The HSQLDB type of database you can create newly from scatch is a little bit special since it wraps the table data in the Base document itself. However, when you "open" this database, the tables are extracted to a temporary place before Base connects to this storage like with any other external database.

Finally, you can connect Base with non-databases (csv, spreadsheet tables and mail address books). These connections provide a read-only view on the tabular data, which is good enough for mail merge and other things.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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