Database support
Database support
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.
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
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Database support
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
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
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
the query would return only those records for badges currently in effect.
likewise
would return only those records for badges that have expired.
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
);
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")
);
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
likewise
Code: Select all
WHERE "Awards"."ExpirationDate" < CURRENT_DATE
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Database support
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
kind regards
Nev
libreoffice 5.3 Version: 5.3.4.2 (x64) on Windows 7
Re: Database support
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:-
in order to restrict the output to only those records which fall within the set time limit we use:-
if you need help then please ask.
i will not be able to reply between 18 September to 22 September.
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"
Code: Select all
where datediff('dd', "tScores"."Date", current_date) <= "tBadge"."Life"
i will not be able to reply between 18 September to 22 September.
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
Re: Database support
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
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
Re: Database support
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.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.
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;
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
Re: Database support
I have as requested.
Thank you kindly
regards
Nev
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
Re: Database support
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.
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. |
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
Re: Database support
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
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
Re: Database support
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
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
Re: Database support
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.
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.
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
Re: Database support
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
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
Re: Database support
Chris
Worked out I can copy my table into Calc and manipulate for copying back into the database.
regards
Nev
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
Re: Database support
what i have attempted to demonstrate is how you might store/retrieve score/badge data within YOUR database using the minimum of effort.Niftyjk originally said,
Can I record when badge was issued and if still current not have that name appear in my query
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.
how would i know?Niftyjk said, Do we need the Instructor ID field
before even considering this you need a solid, thoroughly tried & tested structure.Niftyjk said, how I could get my current database details into your work
use a parameter query. if you experience any input issues then use the format YYYY-MM-DDNiftyjk said, can we have a query for between dates
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
Re: Database support
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.
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.
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
Re: Database support
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
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
Re: Database support
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
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