| View previous topic :: View next topic |
| Author |
Message |
rancor
General User
Joined: 21 Nov 2006
Posts: 28
|
 Posted: Mon Jun 29, 2009 2:20 pm Post subject: Editable records in a query |
 |
|
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 |
|
 |
r4zoli
Super User
Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary
|
| Posted: Mon Jun 29, 2009 10:49 pm Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue Jun 30, 2009 1:58 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
rancor
General User
Joined: 21 Nov 2006
Posts: 28
|
| Posted: Tue Jun 30, 2009 1:22 pm Post subject: Bummer |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
amzg
General User
Joined: 04 Jul 2009
Posts: 13
|
| Posted: Fri Jul 17, 2009 5:13 pm Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Jul 18, 2009 6:13 am Post subject: |
|
|
menu:Tools>Relations
There are 3 tables:
[Movies] -->[Movies_Genres]<--[Genres]
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
amzg
General User
Joined: 04 Jul 2009
Posts: 13
|
| Posted: Sat Jul 18, 2009 8:33 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Jul 18, 2009 9:55 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
