질문하기

kirsch's profile - activity

2020-09-09 10:07:35 +0200 으뜸 답변으로 표시됨 “Workers and tasks” need advice for table structure and relations in DB

Hello everybody

“Workers and tasks” unsolved example or "what would be the best table structure and relations to implement such DB ?" I have to set-up what looks like a quite simple DB, but after a few trials, I am unable implement successfully the table relations and user forms, I mean without data duplication, ! Here is the challenge :

Four tables

  1. Table T_workers contains personal record fields name, town... and sub-key ID_task referencing a task in table T_tasks.
  2. Table T_tasks (auxiliary table, empty at start) with two sub-key fields ID_branch and ID_role (a task is here defined as an association of a branch selection, and/or a role selection from the tables below.
  3. Table Tlist_branches, a predefined list containing Nb branches from which to select a task branch.
  4. Table Tlist_roles, a predefined list containing Nr roles from which to select a task role.

Forms

Functionality has to satisfy the following needs and rules :

  1. Create, display and delete worker record, modify worker personal static fields like name, town… as usual, no problem.

  2. Display, modify, add new, delete tasks. Is the idea of an auxiliary table T_tasks a good or bad approach when considering the next points ?

  3. A worker can be involved in one or many different tasks, limited only by the maximum different combinations of Nb branches with Nr roles from the predefined lists.

  4. A given task can apply for many worker's. It tells that these workers are involved in the same task (same branch and role).

  5. So the number of records in T_tasks increases or decreases dynamically according to the tasks added or deleted for different workers when updating data.

  6. On a worker's data form, display the list of tasks a worker is involved in.

  7. Of course one has also to propose the search/selection of a particular worker from a workers list_box. So I am more then lost in defining the overall structure !

Queries

The queries have to filter the workers on branch or role criteria or both from T_tasks, as well as “ordinary” easy filters on town and other simple static field criteria from T_workers.

Note : With branches and roles duplication in T_workers records AND a limited and predetermined number of allowed task-fields (I used five), I can cope with the problem in a redundant and non elegant way. In that case modifying a record in the branch or role list, will not update the duplicated data, and maintenance becomes tricky ;-(

If anybody knows a good approach (if not the best ?) to implement such a data base, I would be grateful to benefit from useful advice or example.

DB file, with the four tables described above as a start and a form skeleton download here : http://r.kirsch.free.fr/BD_LibO/WorkersAndTasksV0.odb

Robert.

2019-07-09 19:23:43 +0200 받은 배지  유명한 질문 (source)
2018-03-10 22:27:59 +0200 받은 배지  정리의 달인
2017-07-12 02:55:23 +0200 받은 배지  유명한 질문 (source)
2017-07-10 22:24:21 +0200 받은 배지  유명한 질문 (source)
2017-06-30 18:12:34 +0200 받은 배지  유명한 질문 (source)
2017-05-11 15:43:31 +0200 답변을 편집했습니다 What may be blocking outgoing emails in Mailmerge?

Similar unsolved Pb, on Windows 10 64 bit, since the last vorking LibO version 5.1.5.2 E-mailing procedure is blocked at

2017-05-11 15:41:40 +0200 답변을 편집했습니다 What may be blocking outgoing emails in Mailmerge?

Same unsolved Pb on Windows 10 64 bit since the last vorking LibO version 5.1.5.2 E-mailing procedure is blocked at step

2017-05-11 15:36:46 +0200 답변을 편집했습니다 What may be blocking outgoing emails in Mailmerge?

Same unsolved Pb on Windows 10 64 bit since the last vorking LibO version 5.1.5.2 I have that behavior up to Version 5.2

2017-05-11 15:36:32 +0200 답변을 편집했습니다 What may be blocking outgoing emails in Mailmerge?

Same unsolved Pb on Windows10 64 bit since the last vorking LibO version 5.1.5.2 I have that behavior up to Version 5.2.

2017-05-11 15:35:29 +0200 질문에 답변하였습니다 What may be blocking outgoing emails in Mailmerge?

Same unsolved Pb on Windows10 64 bit since the last vorking LibO version 5.1.5.2 The have that behavior up to Version 5.

2017-05-11 15:29:45 +0200 받은 배지  논객
2017-05-11 15:29:45 +0200 코멘트달린 답변 e-Mailing - Connection OK, Server OK, ...no mail sent !

I still have to stick on LibO V5.1.5.2 to be able to use e-mailing. Later updated versions up to 1.2.6.2 block the e-ma

2017-03-22 16:04:00 +0200 받은 배지  유명한 질문 (source)
2017-03-16 16:45:02 +0200 받은 배지  유명한 질문 (source)
2017-02-24 11:46:55 +0200 코멘트달린 답변 Why does LO Writer tell me that the Test Account Settings is successful, Connection to Outgoing Mail server was established, & 2 of 2 emails sent - but then says emails not sent: 2 (sending failed)

T found out that the sending parameters have to be set from the main menu (while Writer is runing) Tools-> Options-> LibreOffice Writer-> E-mail mailing Defining these parameters inside the mailing procedure in Writer will test the server OK but will not send the mails ! Looks like a bug in LibO

2017-02-22 22:30:58 +0200 받은 배지  주목할 만한 질문 (source)
2017-02-22 22:01:30 +0200 질문에 답변하였습니다 e-Mailing - Connection OK, Server OK, ...no mail sent !

Server parameters for e-Mailing to work

There is a strange behavior in LibreOffice V5.1-V5.2 (Windows 10 home, 64bits):

  • You need to set the outgoing server parameters in the menu Tools → Options → LibreOffice Writer → Mailing Email

    • If the parameters are set during the Mailing procedure in Writer, the parameter tests Connection & Server are OK but the mailing fails with the message "please run connect() first"!

Has to be known !

2017-02-22 14:17:07 +0200 받은 배지  학생 (source)
2017-02-21 22:09:56 +0200 받은 배지  인기있는 질문 (source)
2017-02-21 17:38:05 +0200 받은 배지  오거나이저 (source)
2017-02-21 17:30:54 +0200 코멘트달린 답변 Clean exit macro from DB and LibO ?

The above "LoQuit code

  • gives" an UNEXPETED ERROR on activating the macro from a QUIT LIBREOFFICE button in the Form!
  • and leaves a .lck file in the DB directory.

So I deactivate the button and label it with "quit with CRTL/Q" ;-)

2017-02-21 15:42:06 +0200 질문하였습니다 e-Mailing - Connection OK, Server OK, ...no mail sent !

LibreOffice Version: 5.1.6.2 (on Windows 10 home, 64bits)

Error ...please run connect() first -> No mail sent ! is that a known issue ?

I used mailing in the past on LibreOffice and it worked fine. Now I test the sending parameters and it says

connection OK
server OK

but no mail is sent:

ERROR: class 'smtplib.SMTPServerDisconnected'>: please run connect() first, traceback follows C:\Program Files\LibreOffice 5\program\python-core-3.3.0\lib\smtplib.py:340 in function send() [raise SMTPServerDisconnected('please run connect() first')].... and so on

I tested different sender accouts and I do not know what to do to "run connect first". Do I have to come back to a previous version of LibreOffice ?

Thanks for advice. Bob

2017-02-21 15:32:34 +0200 질문에 답변하였습니다 Why does LO Writer tell me that the Test Account Settings is successful, Connection to Outgoing Mail server was established, & 2 of 2 emails sent - but then says emails not sent: 2 (sending failed)

Same problem today LibreOffice Version: 5.1.6.2 (on Windows 10 home, 64bits)

No mail sent ! is that a known issue ? How did you solve the problem Randy ?

I used mailing in the past on LibreOffice and it worked fine. Now I test the sending parameters and it says

  • connection OK
  • server OK

but no mail is sent and it says

  • ERROR :

class 'smtplib.SMTPServerDisconnected'>: please run connect() first, traceback follows

C:\Program Files\LibreOffice 5\program\python-core-3.3.0\lib\smtplib.py:340 in function send() [raise SMTPServerDisconnected('please run connect() first')].... and so on.

I tested different sender accouts and I do not know what to do to "run connect first". Do I have to come back to a previous version of LibreOffice ?

Thanks for advice. Bob

2017-02-19 11:27:13 +0200 받은 배지  주목할 만한 질문 (source)
2017-02-19 00:34:25 +0200 질문에 답변하였습니다 Clean exit macro from DB and LibO ?

Hi Karolus, about your comment

That upper right corner "X" button does not the job properly in my case:

I start the database by opening a Form from start, the usual DB development window is not on screen (hidden). When I click the X button in the Form, the form-window closes leaving a LibreOffice process running! And it gives trouble later on when I try to start a other DB. Closing that Form trough its menu File->Close does the un-clean exit and leaves a unneeded and unwanted LibreOffice running process.

The only clean Exit from that is using in the Form the menu choice File->Quit LibreOffice (Of course it needs the menu bar in the Form) or I can use CTRL/Q to quit LibO.

But in my application, I'd like to have a nice button in the form to do that CTRL/Q action.

Thanks anyway for your comment Karolus, it makes me explain my problem more pecisely. Bob.

2017-02-18 17:54:44 +0200 받은 배지  인기있는 질문 (source)
2017-02-18 10:07:15 +0200 질문하였습니다 Clean exit macro from DB and LibO ?

Hello, is There a usable macro on hand, to trigger from a button in a Base Form, a clean exit from DB and LibO(V5) ? I'll take it ! Bob

2017-02-14 10:14:56 +0200 질문에 답변하였습니다 macro for a clean database ending and exit from LibreOffice

Hello So, after that long discussion, is there a usable macro on hand, to trigger from a button in a Base Form, a clean exit for DB and LibO(V5) ? I'll take it ! Bob

2017-02-14 10:09:55 +0200 질문에 답변하였습니다 macro for a clean database ending and exit from LibreOffice

Hello So, after that long discussion, is there a usable macro routine on hand, to trigger from a button in a Base Form, a clean exit for DB and LibO(V5) ? I'll take it ! Bob

2017-02-07 21:21:43 +0200 받은 배지  주목할 만한 질문 (source)
2017-02-07 10:56:30 +0200 코멘트달린 답변 Two tables DB and difficult Queries

Hi John Thanks a lot for your SQL code. It works perfectly to display all the records with flower names in place of the flower reference. I'll see later on, how to modify your code to suit my other query needs.

2017-02-07 10:55:32 +0200 으뜸 답변으로 표시됨 Two tables DB and difficult Queries

EXAMPLE DESCRIPTION

The DB can be seen as a Flower Shop situation using only 2 tables :

  1. T_bouquets, main table which records contain a) a name bouquet_Name b) six flower fields ID_flowerN (N from 0 to 5) containing an optional reference to a flower in T_flower_list.

  2. T_flower_list , contains the names flower_name of available flowers.

So each main table record defines a bouquet's name and up to 6 references to the selected flowers. And one can imagine all kinds of bouquets : undefined bouquet with no flower, some bouquets might have only one type of flower, other bouquets could combine flowers randomly, etc...

I have created a few queries displaying the ID-flower references in T_bouquets (with the exception of ID_flower0 which can be displayed by name through the relation). It works, but the reference numbers in the query results have no meaning to the end user florist, who wants to read the flower names!

HELP NEEDED

HOW TO establish the needed relations between the two tables and set up the SQL to display the flower names in place of the flower reference ID number ? It needs to change my "OK_by_ID.." queries In the example to some new "OK_BY-NAME.." queries ! I believe that it is possible in LibO.

NOTE :

Maybe that he solution needs a different table approach, or it might have something to do with the AskLibO discussion here https://ask.libreoffice.org/en/question/72892/grouping-in-base-report-based-on-multiple-fields-with-common-entries/ ?

EXAMPLE DOWNLOAD

http://r.kirsch.free.fr/LibO/FlowerSh...

2017-02-07 10:54:43 +0200 질문에 답변하였습니다 Two tables DB and difficult Queries

Hi John Thanks a lot for your SQL code. It works perfectly to display all the records with flower names in place of the flower reference. I'll see later on, how to modify your code to suit my other query needs.