[Solved] Calculating a total within a given date range

[jeng2699]

I'm new to Base, and databases in general. I managed to create a database to keep track of the training my employees have received. My main form consists of the employee's name, hire date, department and job title. The subform tracks the subjects they've been trained on and the hours spent.
I've done some simply queries such as returning the employees who have been trained on a specific machine, but now I want to do something more complex and need some help.
Government regulations require our employees receive 24 hours of training within their first 60 days of employment. So, I want to select the employee and somehow receive a report of all the training they've received within 60 days of their hire date, with a sum of the hours so I don't have to do the math myself, and I can make sure we're in compliance.
Or, I'd like to know which employees have been hired less than 60 days ago and how many hours of training they've received so far.

[Villeroy]

viewtopic.php?f=13&t=88023

[jeng2699]

I finally had time to check out that link and I downloaded the sample databases posted in the responses. However, when I try to open the forms and queries to see how they are set up, I get an error message saying the connection to the data source could not be established. So unfortunately it doesn't help. Thanks anyway.

[eremmel]

Did you setup Java 32bit you need to have installed and configured in Base menu: Tools -> Options -> Advanced (or alike). Just search for Base and Java.

[jeng2699]

I have Java 1.8.0_131 installed.

[Villeroy]

I finally had time to check out that link and I downloaded the sample databases posted in the responses. However, when I try to open the forms and queries to see how they are set up, I get an error message saying the connection to the data source could not be established. So unfortunately it doesn't help. Thanks anyway.

Troubleshooting Open/LibreOffice and Java
Unless you run a 32-bit Windows system you should either install a 32-bit Java for your 32-bit OpenOffice or install LibreOffice which is availlable as a 64-bit binary for MS Windows working together with a 64-bit Java.

[d_h159@yahoo.com]

I am looking for something very similar to what you are trying to accomplish in reference to calculating date ranges. But, in response to the reason why you might not be able to open the database is because you may need to download not only the java 131 but, also, the java 131 x86. You may need the 32 bit and 64 bit versions of java. That's what worked for me!

Best of Luck!

[jeng2699]

I downloaded and installed 32 bit Java and nothing changed. I still can't open the forms in those other databases. Are there step-by-step instructions that I can read somewhere, for what I want to do? That would be easier that trying to figure out what someone else did by looking at their work. I've read a lot of tutorials, but haven't come across this type of task yet.

[RoryOF]

Did you select the 32 bit Java in OpenOffice's /Tools '/Options /OpenOffice /Java?

[jeng2699]

Yes, I did. I followed the instructions in the link Villeroy posted.

[jeng2699]

OK, Forget Java, and let's get back to my original question. I did some more research and found the following links:
http://openoffice.blogs.com/openoffice/ ... eries.html
viewtopic.php?f=13&t=78997&p=362647&hil ... er#p362647

I now have a query returning employee name, hire date and total training hours; and a report based on said query. Now, I need to introduce a date range, so my report will return only those employees with less than six months on the job.

[Villeroy]

How do you run OpenOffice Base without Java?
Are you connected to an external database engine?
You don't need most of the Base tools?
The exact answer to your question depends on the database engine you are running. Base is not a database. OpenOffice is shipped together with a simple database engine. But that one can't be used without Java.
Independently from Base or any specific database engine, your question is trivial. Any most trivial SQL tutorial teaches aggregation queries.

[jeng2699]

I have Java installed. Base works just fine. It was just those links on that other thread that don't work right. And that may be due to an issue with those specific files. If you don't want to help, don't respond. I've been doing as much reading and experimenting as I can, in between my other work responsibilities. All I asked was that someone point me in the right direction to find the information I need, because I haven't come across it on my own yet.

[Sliderule]

You said:

I now have a query returning employee name, hire date and total training hours; and a report based on said query. Now, I need to introduce a date range, so my report will return only those employees with less than six months on the job.

In order for someone to help you answer your question . . . please . . . include here the SQL Query you have created as a starting point. That is, so we can help you modify your already created Query to only display the information you want ( that is the result set you desire ) . . . paste the Query ( the SELECT statement ) .

Sliderule

[jeng2699]

This is what I have so far:
SELECT "Training"."Name" AS "Name", "Employees"."HireDate" AS "Hire Date", SUM( "Training"."CourseLength" ) AS "Total Hours" FROM "Training", "Employees" WHERE "Training"."Name" = "Employees"."Name" GROUP BY "Training"."Name", "Employees"."HireDate" ORDER BY "Hire Date" DESC

As a beginner, I am more comfortable working in Design View, or using the wizard. I don't expect others to do the work for me. Thank you for any direction you can provide.

[Sliderule]

Code: Select all

-- Now, I need to introduce a date range, 
-- so my report will return only those employees with less than six months on the job.
-- Allows for prompts to enter a date range for "Employees"."HireDate"
SELECT 
   "Training"."Name" AS "Name", 
   "Employees"."HireDate" AS "Hire Date", 
   SUM( "Training"."CourseLength" ) AS "Total Hours" 
FROM "Training", 
     "Employees" 
WHERE "Training"."Name" = "Employees"."Name" 
  AND "Employees"."HireDate" Between :Enter_01_From_Hire_Date and :Enter_02_To_Hire_Date
GROUP BY "Training"."Name", 
         "Employees"."HireDate" 
ORDER BY "Hire Date" DESC

Code: Select all

-- Now, I need to introduce a date range, 
-- so my report will return only those employees with less than six months on the job.
-- Uses DATEDIFF function with a hard-coded 6 months range from CURRENT_DATE
SELECT 
   "Training"."Name" AS "Name", 
   "Employees"."HireDate" AS "Hire Date", 
   SUM( "Training"."CourseLength" ) AS "Total Hours" 
FROM "Training", 
     "Employees" 
WHERE "Training"."Name" = "Employees"."Name" 
  AND DATEDIFF('month', "Employees"."HireDate", CURRENT_DATE) < 6
GROUP BY "Training"."Name", 
         "Employees"."HireDate" 
ORDER BY "Hire Date" DESC

Explanation:

1. CURRENT_DATE returns the computer date at the time of running the computer.
2. DATEDIFF . . . according to HSQL documentation:

   
   DATEDIFF(string, datetime1, datetime2)
   
   returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values:

   1. 'ms' = 'millisecond'
   2. 'ss' = 'second'
   3. 'mi' = 'minute'
   4. 'hh' = 'hour'
   5. 'dd' = 'day'
   6. 'mm' = 'month'
   7. 'yy'= 'year'.

   Both the long and short form of the strings can be used.

3. In the FIRST Query I wrote above . . . the user is prompted to enter a date range. This is defined in the WHERE clause. By using the syntax :Enter_01_From_Hire_Date and :Enter_02_From_Hire_date the user is prompted to enter the two values. Since a : is used, that indicates a user prompt . . . which . . . must be entered a single word . . . that is withOUT any spaces. Multiple prompts are displayed in alphabetical order . . . therefore . . . I prefer to add numbers, such as 01 and 02 to be able to control the sequence they are displayed to the end user.
4. In the second Query, the database back-end, HSQL, uses the DATEDIFF function to calculate a month ranges from the Query run date.
5. I suggest you try these Queries ( use Base Queries, Create Query in SQL View... ), and, you can save them, and, look at how they look using the GUI ( Graphic User Interface ).

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jeng2699]

Thank you so much. Both querys are helpful. I'll have to spend some studying them, but I think I can follow what you did. And thank you for the explanation, it is much appreciated.

[Villeroy]

Regarding the example databases, you must not open them for viewing. You have to explicitly download the file to your own home directory. And then open the downloaded file.

[jeng2699]

Regarding the example databases, you must not open them for viewing. You have to explicitly download the file to your own home directory. And then open the downloaded file.

That must have been the issue then. Because that was what I did. I will keep that in mind for the future. Thank you.