Query to pull dates from table

Creating tables and queries

Query to pull dates from table

Postby AmyBUK » Mon Dec 17, 2018 3:57 pm

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   Expand viewCollapse view
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
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query to pull dates from table

Postby Villeroy » Mon Dec 17, 2018 10:02 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query to pull dates from table

Postby UnklDonald418 » Mon Dec 17, 2018 10:18 pm

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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1153
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query to pull dates from table

Postby Villeroy » Mon Dec 17, 2018 10:26 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query to pull dates from table

Postby AmyBUK » Tue Dec 18, 2018 11:40 am

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 25 times
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query to pull dates from table

Postby keme » Tue Dec 18, 2018 4:23 pm

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...
User avatar
keme
Volunteer
 
Posts: 3157
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Query to pull dates from table

Postby UnklDonald418 » Tue Dec 18, 2018 9:42 pm

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/images/0/02/Base_tutorial.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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1153
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query to pull dates from table

Postby Villeroy » Tue Dec 18, 2018 9:48 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: RPG and 2 guests