Query to pull dates from table

Creating tables and queries
Post Reply
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Query to pull dates from table

Post by AmyBUK »

Hi

I have a database with two tables; 'Training Needs' and 'Training Log V2'. The Training Needs Table has a number of tick boxes, which are selected based on the trainees individual needs. The Training Log V2 is the table holding all training records.

I'd like to create a query which will give me the dates the training has been completed if the training needs entry is ticked for that person.

So far I have the following, but I'm getting a little stuck now. I've got the date for one of the training courses, but I don't know how to get the others without causing extra rows of data, which I could do, but then the report I want to create will have one date in each section. It's a little bit of a mess, so any help would be greatly appreciated.

Code: Select all

SELECT "Training Needs"."FullName", "Training Needs"."Company", CASEWHEN( "AbrasiveWheels" = FALSE, 'No', 'Yes' ) AS "AbrasiveWheels", CASEWHEN( "AsbestosAwareness" = FALSE, 'No', 'Yes' ) AS "AsbestosAwareness", "Training Log V2"."TrainingDate", "Training Log V2"."CourseTitle", CASEWHEN( "AsbestosMedical" = FALSE, 'No', 'Yes' ) AS "AsbestosMedical", CASEWHEN( "CAT&Genny" = FALSE, 'No', 'Yes' ) AS "CAT&Genny", CASEWHEN( "CoreDrill" = FALSE, 'No', 'Yes' ) AS "CoreDrill", CASEWHEN( "FacefitFFP3" = FALSE, 'No', 'Yes' ) AS "FacefitFFP3", CASEWHEN( "FacefitSR100" = FALSE, 'No', 'Yes' ) AS "FacefitSR100", CASEWHEN( "Ladders" = FALSE, 'No', 'Yes' ) AS "Ladders", CASEWHEN( "ManualHandling" = FALSE, 'No', 'Yes' ) AS "ManualHandling", CASEWHEN( "NewStarterTrade" = FALSE, 'No', 'Yes' ) AS "NewStarterTrade", CASEWHEN( "NNLWAsbestosManual" = FALSE, 'No', 'Yes' ) AS "NNLWAsbestosManual", CASEWHEN( "PASMA" = FALSE, 'No', 'Yes' ) AS "PASMA", CASEWHEN( "Scaffold" = FALSE, 'No', 'Yes' ) AS "Scaffold", CASEWHEN( "SMSTS/SSSTS" = FALSE, 'No', 'Yes' ) AS "SMSTS/SSSTS", CASEWHEN( "TetraLevel3" = FALSE, 'No', 'Yes' ) AS "TetraLevel3" FROM "Training Needs", "Training Log V2" WHERE "Training Needs"."FullName" = "Training Log V2"."FullName" AND "Training Log V2"."CourseTitle" = 'Asbestos Awareness'
Ultimately, I'm looking to create a report that looks like the following, with all of the dates of the training completed on the right hand side?:
Training Needs.PNG
Many thanks in advance.
OpenOffice 4.1.5 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query to pull dates from table

Post by Villeroy »

Looks like bad database design. But it is hard to tell without seeing the actual database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query to pull dates from table

Post by UnklDonald418 »

Without knowing the exact table structure and the relationships between the tables it would be difficult to offer any advice.
We could be of more help if you upload a sanitized sample database.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query to pull dates from table

Post by Villeroy »

If your query returns the right data but with a repeating date for each course the trick is grouping by the date when using the report wizard. Then the date will appear on top of its tabular section.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AmyBUK
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query to pull dates from table

Post by AmyBUK »

I've deleted all the original data and most of the forms, reports and queries to get the file size low enough, but I've left the training ones and added some dummy data into a copy of the database. I've attached it.

(please be a little gentle - I've only used OpenOffice Base for about 3 weeks, and it's not part of my normal job so I'm still learning - it's certainly not perfect)
Attachments
Training Log Database Upload.odb
(103.38 KiB) Downloaded 184 times
OpenOffice 4.1.5 on Windows 7
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Query to pull dates from table

Post by keme »

About DB Design:
It is a good idea to use "persistent identifiers" as DB keys. You identify course participants in the log by full name. Sometimes people change names, or a mistyped name is corrected, and with your solution the course data will lose its connection to trainee. Also, people can have identical names. Use a separate, persistent ID number, like an official passport/bank/welfare system ID, or your own Ops.OpID, to connect between person and qualification. This adds a level of abstraction, so it is less intuitive, but it adds database integrity/stability. Also, redundant data is usually not a good idea. You have Forename, Surname and FullName. Usually, one can be built from the other two (unless you distinguish between formal and informal names).

The Training Needs table also seems like a bad idea to me. You have the courses/skills as separate entries in the course list. If trainees are required to take all courses, the need yes/no should be determined by the existence of a log entry for that employee and that course, with no expiry or a future date (> today) for the expiry date. In other words, "training needs" should be a query, extracting needs from existing data. If some trainees only need specific courses, you need another "requirements" table where you identify which skill each trainee needs (connect Ops and Courses).

I do not have the time to do a full analysis of your data set, sorry! Also, that job might require additional info from you. Hopefully you get a few pointers from this, and someone else will add to it. I may be back...
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query to pull dates from table

Post by UnklDonald418 »

Before you can resolve your issue with the query you first need fix some issues with your database design.
A couple of general comments. Table and column/field names containing spaces generally are not a good idea. They might appear to be a little more readable but in the end they are more likely be a source of data entry errors and confusion.
Database engines like HSQLDB used by Base, require numerical indexes not text fields to identify database records, as a result well designed database tables are often not really human readable.

The process of database normalization is used to help design well functioning tables.
Have you looked at the tutorial
https://wiki.documentfoundation.org/ima ... torial.pdf
He includes a discussion of basic normalization concepts.
Your tables Ops List, Training Log V2 and Training Needs are all in need of normalization.

Beginning database designers often need to scrap a design and start over. Experienced designers develop strategies to minimize false starts but sometimes they still happen, often because new information or requirements are revealed during the design process.

Looking at the table Ops List, it appears to define a number of unique characteristics about an employee.
Normalization rules tell us that any other tables needing to reference this table should use the primary key value "OpID" rather than the employee name.
The column/field "FullName" is problematic, with the potential for misspellings and mismatches. When you want to display a full name on a form or report use a query something like ("Forename" || ' ' || "Surname") AS "FullName" or maybe ("Surname" || ', ' || "Forename" ) AS "FullName"
The columns/fields for "Company" and "Trade" should be integers storing the "CompanyID" and "TradeID" values, not the the actual text values. Those two columns are commonly referred to as foreign keys.

The table Training Log V2 appears to store specific details about completed training.
Again you have text fields for "CourseTitle", "Fullname" and "Company". They instead it should store the foreign key values "CourseID", "OpID" and since "Company" can be obtained from the "OpID" link it is redundant and can be eliminated from this table.

I too find the Training Needs table to be problematic.
A different approach to consider would be to add an entry into Training Log V2 for each required training. Then if the TrainingDate field is null you know they have not completed that training. That way the Training Needs table can be eliminated along with the associated complications.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query to pull dates from table

Post by Villeroy »

AmyBUK wrote:(please be a little gentle - I've only used OpenOffice Base for about 3 weeks, and it's not part of my normal job so I'm still learning - it's certainly not perfect)
The database design has to be as perfect as can be. Otherwise you don't get much out of OpenOffice Base. Base is not a database development suite. It is a tiny addition to this office suite. It presumes that the database design is based on primary keys and foreign keys. See https://support.microsoft.com/en-us/hel ... ion-basics
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply