Database support

Discuss the database features
Post Reply
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Database support

Post by Niftyjk »

I hope somebody has the time to help me.

I have managed to get a Base database working quite well. I am involved in cadets and I record their shoot scores. My main tables are Nom Roll and Shoot Scores. When a cadet shoots 65% he/she is awarded a single rifle badge. If they should should 85% or better they are awarded a crossed rifle badge. I have two queries which give me the results for both which works okay. Both badges have a time limit from when awarded. However when I run my query I get everybody that has qualified from day one. I have to submit a report of those cadets who have been newly awarded the relevant badge but the only way I can do it is manually check who has been awarded a badge previously then strike these of the report which is very time consuming.

Can I record when badge was issued and if still current not have that name appear in my query so I can forward the new update list of new awardees onto the relevant people.

Hope this makes sense. Would really appreciate any help please.
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Database support

Post by UnklDonald418 »

You don't detail what is in the table Nom Roll but I assume it contains data unique to each Cadet.
As I understand it, you have 2 types of Badges that can be awarded, and multiple cadets who can receive these awards. In database parlance that is a many to many relationship. That type of relationship requires 2 tables.
Based on those assumptions my suggestion is to have one table that contains the Badge information for instance the following SQL command

Code: Select all

CREATE TABLE "Badges" (
"BadgeID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"BadgeName" VARCHAR(25) NOT NULL,
"BadgeDescription" VARCHAR(50) NOT NULL,
"BadgeDuration" INTEGER
);
in this suggestion "BadgeDuration" would be the number of days a badge remains valid i.e. 90 or 365.

The second table which is sometimes referred to as an intersection table would be something like

Code: Select all

CREATE TABLE "Awards" (
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"CadetID" INTEGER,
"BadgeID" INTEGER,
"AwardDate" DATE,
"ExpirationDate" DATE,
CONSTRAINT FK_CADET FOREIGN KEY ("CadetID") REFERENCES "Nom Roll" ("ID"),
CONSTRAINT FK_BADGE FOREIGN KEY ("BadgeID") REFERENCES "Badges" ("BadgeID")
);
This table assumes there is an Primary Key of Integer type in the "Nom Roll" table named "ID" which uniquely identifies a single Cadet.
The "ExpirationDate" field would be optional, but I think it might serve to simplify your queries. It could easily be calulated and stored by adding the value in "BadgeDuration" to the "AwardDate" when a new award is posted.

Of course your queries will need to contain additional joins to include data from "Awards" and "Badges"
If you add a WHERE clause to your query something like

Code: Select all

WHERE "Awards"."ExpirationDate" > CURRENT_DATE
the query would return only those records for badges currently in effect.
likewise

Code: Select all

WHERE "Awards"."ExpirationDate" < CURRENT_DATE
would return only those records for badges that have expired.
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
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

THank you so much for your response. Day time here so will have a go tonight. If it is of assistance attached is my database.
kind regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Database support

Post by chrisb »

Niftyjk,
please download the attachment.
it's a very simple database which contains two queries, one form & three tables('tCadet', tScores' & 'tBadge').

'tBadge' defines the badge criteria.
'ID' integer auto value primary key.
'Name' varchar.
'Life' integer. the time limit. in regard of this database the number of days.
'ScoreStart' integer.
'ScoreEnd' integer.

'tScores' contains the score data.
'ID' integer auto value primary key.
'Cadet_ID'.
'Score'.
'Date'.

this enables us to join 'tScores' to 'tBadge' like this:-

Code: Select all

join "tBadge" on "tScores"."Score" between "tBadge"."ScoreStart" and "tBadge"."ScoreEnd"
in order to restrict the output to only those records which fall within the set time limit we use:-

Code: Select all

where datediff('dd', "tScores"."Date", current_date) <= "tBadge"."Life"
if you need help then please ask.
i will not be able to reply between 18 September to 22 September.
Cadets.odb
(14.03 KiB) Downloaded 140 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Thankyou so much.

I would like to email my database so you can see my layout but it is 218kb which is too large for me to upload here. It probably needs work!

If you can help YES PLEASE.

regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Database support

Post by chrisb »

Niftyjk,
both UnklDonald418 & i responded to your initial post.
you have not provided any feedback.
without constructive feedback it's impossible to make any further suggestions.
i'm not sure that we need to see your database but if you want to make it available & it contains NO personal info(unlikely) then upload it to a free file sharing site such as https://www.mediafire.com & paste a link on this forum.

alternatively copy your database, delete the table content, shrink the database then input a few rows of dummy data like this:-
1. copy the database & rename it as 'MyCopy'.
2. load 'MyCopy'.
3. copy the code below & paste it here >> menu:Tools>SQL.

Code: Select all

delete from "Shoot Scores";
delete from "Nom Roll";
shutdown compact;
4. hit execute then close the window.
5. exit libreoffice.
6. reload 'MyCopy'.
7. input a few rows of fictitious data.
8. exit libreoffice.
9. upload 'MyCopy' to this forum.

we know very little about your database. i have assumed that its bulk is comprised of table data.
if it's still too big to upload after carrying out steps 1 to 8 then delete any reports. still too big? delete forms.
make sure you tell us how we can help.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

I have as requested.

Thank you kindly

regards
Nev
Attachments
MyCopy.odb
(68.37 KiB) Downloaded 190 times
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Database support

Post by chrisb »

Niftyjk, download the attachment.
hsqldb is a relational database.
when we have values which may be repeated on numerous occasions then it's usually best to store them in their own dedicated table.
do not worry about the number of tables you end up with, just keep things simple/organised (modular).
do not store calculated values in tables.
time spent considering the layout is always time well spent. converting a poorly designed to database to a more normalised model can be very difficult & time consuming.

you can check out the structure here>> menu:Tools>Relationships.
the tables are a mixture of both yours & mine.
there are no hidden queries.
queries which begin with 'qLB' are used by list boxes.

qScores_All_SQL.
we have one query 'qScores_All_SQL' which requires the base parser to be disabled. this is because of the number of left joins & the small subquery it contains.
this query displays all of the score data.
i have created a view from this query 'vScores_All'. this enables us to use it as the data source for all other queries excluding those used for list boxes.

the forms are also straight forward corresponding with your own.
although this is a basic example i cannot be sure that it's 100% bug free(time!).
i added two cadets & their scores to test form input.
you are not much good at feedback but give it a thorough workout & let me know what's good/bad about it.
 Edit: replaced demo (made minor improvements) 27/09/17 00:41 GMT 
 Edit: 27-09-17 06:40 GMT. PLEASE DO NOT DOWNLOAD THIS ATTACHMENT.
i had mistakenly assumed that the field 'AGEDOUT' in the authors original table 'NOMROLL' indicated whether or not the badge time limit had expired.
an updated attachment can be found in my next post. 
Cadets_26-09-17.odb
(33.29 KiB) Downloaded 173 times
Last edited by chrisb on Wed Sep 27, 2017 7:55 am, edited 2 times in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Chris

Sorry had a weekend away with my son (and no computer). I certainly will go over in the day or two. Be assured I am extremely grateful for you time.

regards
NEv
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Chris

Every shoot I have about 50 cadets. Some may be on the tNomRoll some might. To update I normally have a Nom Roll layout and a Scores layout. I copy these across to my database. Will your database reject duplicate entries in the tNomRoll if I double up please.

regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Database support

Post by chrisb »

Niftyjk,
the only field in the table 'tNomRoll' which can not be duplicated is 'ID' the auto value integer primary key, give it a go & try to identify where it does/does not meet your requirements.
Here is the updated attachment.
Cadets_27-09-17.odb
(33.45 KiB) Downloaded 151 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Chris

I sneaked a look at and played around at lunch today at work. The only issue I can see is in the the rank. Sorry to be a bore but we have in cadets the ranks of
CDT, LCDT, CCPL, CSGT, CFSGT, CWOFF and CUO. In the Instructors we have CIV, LAC, CPL, SGT, FSGT, WOFF, PLTOFF etc etc. Do we need the Instructor ID field and use only the Rank ID eg 0 = CDT, 1 = LCDT up to 6 = CIV , 7 = LAC etc.

Can I ask how I could get my current database details into your work please.
Also can we have a query for between dates for example if I have advised badges up to 30 JUNE then have have more shoots in July for example I can do a search between dates so I only have July results for cadets or instructors who have not previously been awarded a badge (and thus current).

Sorry about all this.

regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Chris

Worked out I can copy my table into Calc and manipulate for copying back into the database.

regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Database support

Post by chrisb »

Niftyjk originally said,
Can I record when badge was issued and if still current not have that name appear in my query
what i have attempted to demonstrate is how you might store/retrieve score/badge data within YOUR database using the minimum of effort.
all you have to do is input the raw data & the computer does the rest. no manual calculations & no need to split the score data into more than one table.
have i succeeded?

the table 'tShootScores' is self explanatory.
the table 'tNomRoll' is oblique. in particular the purpose/significance of the fields 'SERVICENo', 'AGEDOUT'.

if we have two individuals with identical First & Last names how do we tell them apart?
people info should be stored in a dedicated table which has an auto value integer primary key. this unique value enables the computer to identify each individual.
in order for the user to identify individuals we need at least some additional criteria, MiddleName, DateOfBirth, Address. this would obviously require some restructuring.
Niftyjk said, Do we need the Instructor ID field
how would i know?
Niftyjk said, how I could get my current database details into your work
before even considering this you need a solid, thoroughly tried & tested structure.
Niftyjk said, can we have a query for between dates
use a parameter query. if you experience any input issues then use the format YYYY-MM-DD

Code: Select all

select * from "vScores_All" where BADGE is null and SHOOTDATE between :FirstDate and :LastDate
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Database support

Post by chrisb »

these notes are for those who may have shown an interest in this topic.

it's obvious that the value of Rank will not always remain constant. a cadet may be promoted/demoted. this signifies that the field 'Rank' should not be stored in the table 'tCadet'.
i have included a link table called 'tCadet_ID_Rank_ID' to address the issue.

i have no idea as to the purpose of the field 'AGEDOUT' in the table 'tCadet' but because it simply acts as a boolean Y/N i have not moved/removed it.
i also do not have a clue where the 'Instructor' fits into the scheme & have therefore deleted the table 'tInstructor' & removed all reference to it.

the input form now reflects our needs.
it may be noted that the list box 'Unit' in the table control 'Cadets' does not display properly when the form is first loaded.
the field 'Name' should be shown but 'ID' is displayed. in other words the list box has not kicked in. refreshing the form or clicking the field header sets things straight.
this seems to be a bug in openoffice(seen it several times before). it appeared here when a second subform with a table control was added (no table control then no problem).
it may/may not be present in libreoffice, i wouldn't know because i don't use it.

the attachment may be a simple database but never the less it is a database(the correct tool for the job) & not a spreadsheet.
Cadets_02-10-17.odb
(16.73 KiB) Downloaded 179 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Chris

Sorry just got in from a cadet activity.
Yes you are correct ranks do vary. Agedout was my simplistic way of excluding from my reports any cadets that had left or aged out ie reached the age of 20 years.
Again Instructor was my way of differentiating between cadets and the adult Instructors. An easier way is cadets always have a prefix of either CM or CF and Instructors either AO or 8496166.

kind regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
Niftyjk
Posts: 12
Joined: Sat Sep 16, 2017 1:01 pm

Re: Database support

Post by Niftyjk »

Chris

During lunch break had another look. Each shoot I have between 50-100 scores to enter, some being new names others having shot before. In my old database I used to use calc to copy the new records both for the names and shootscores and pasted into Base. Is that still possible please with the tform? or another method.

regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
Post Reply