OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Group Summary
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Sun Oct 09, 2011 7:29 pm    Post subject: [SOLVED] Group Summary Reply with quote

I'm using a one to many relationship between two tables. I want to summarize the many table based on the single table by way of a common field.

Example I have a single record table (RackMod) with fields RackNumber and Rack Modification Date fields (not actual titles, more of descriptions) Then I have a many table (RackCycles) with Rack Number field and Rack Date Filled field. The racks get filled over and over. We are currently modifying for better part security in transit. I need to track the amount of times used after modification. I have a query that will give me the data but it shows all the individual cycle records by rack number. I want a report or query that gives me a total times used by each rack number not each individual record.

Does anyone know how i can get a summary report or query?

Thanks in advance for the attention to question
Ace


Last edited by Ace Blackwell on Thu Oct 13, 2011 7:09 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Oct 09, 2011 8:22 pm    Post subject: Reply with quote

You said:

ace wrote:
(not actual titles, more of descriptions)

I have a query that will give me the data but it shows all the individual cycle records by rack number. I have a query that will give me the data but it shows all the individual cycle records by rack number.

I want a report or query that gives me a total times used by each rack number not each individual record.

Therefore, I canNOT answer your question, since, you have NOT included the SQL that shows your Queries, NOR, an example of what you want the output to look like. Do you want a SUM of seconds, minutes, hours, days, months, years, other?

Sliderule
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Mon Oct 10, 2011 5:46 am    Post subject: Confusion Reply with quote

Sorry, my post was so confusing. I am usually accused of an overly wordy explaination.

Here is the Query SQL View
SELECT "ModRacks"."RackNo", "ModRacks"."DateMod", "RackRan"."DateRan" FROM "RackRan" AS "RackRan", "ModRacks" AS "ModRacks" WHERE "RackRan"."RackNo" = "ModRacks"."RackNo" AND "RackRan"."DateRan" >= "ModRacks"."DateMod" ORDER BY "ModRacks"."RackNo" ASC, "RackRan"."DateRan" DESC

I have two tables ModRack which is a list of all racks that have been modified. And RackRan which are the daily entries of all racks ran (Modified and Unmodified.) The query pulls out all the modified racks from the RackRan table. That data is used in a report. The report works fine. Ex

Rack X001 Modified 1/1/2011
X001 2/2/2011
X001 3/3/2011
X001 4/4/2011
Rack X002 Modified 1/2/2011
X002 2/15/2011
X002 2/28/2011

So I have the over all data and times cycled but I have to hand type a summary in a Calc sheet to create a one page report Ex

X001 Mod 1/1/2011 @ 3 cycles
X002 Mod 1/2/2011 @ 2 cycles.

I'm sure SQL / Base has the ability to do this for me. I just don't know how to accomplish it. Should I
1) Built a Query to summarize the data
or
2) Create a report that calculates a summary and prints it without the individual recods of the cycles?

If you or anyone has a better way to do this, I'm open to those ideas as well.

Thanks
Ace
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Oct 10, 2011 6:45 am    Post subject: Reply with quote

You said:

Ace wrote:
Rack X001 Modified 1/1/2011
X001 2/2/2011
X001 3/3/2011
X001 4/4/2011
Rack X002 Modified 1/2/2011
X002 2/15/2011
X002 2/28/2011

So I have the over all data and times cycled but I have to hand type a summary in a Calc sheet to create a one page report Ex

X001 Mod 1/1/2011 @ 3 cycles
X002 Mod 1/2/2011 @ 2 cycles.

Just to be sure I understand . . . that is . . . the terms you are using, so I can attempt to help, and, 'translate' it to the needed SQL Query ( so you can create a report from the Query ) . . . a few more questions.

Is definition of 'cycles' for the output, is really, the COUNT ( number ) of entries in table "ModRackes" less one ? ? ?

For example, for entry "ModRacks"."RackNo" of 'X002', since there are 3 entries ( 1/2/2011, 2/15/2011, 2/28/2011 ) for it, you want the Query to show: 'X002' the number of 2 ? ?

If so, the following SQL Query should work . . . or . . . something like it ( you might want to change some alias to meet your needs ):

Code:
-- Aggregate Query to return each UNIQUE "ModRacks"."RackNo" and the COUNT less 1 of "RackRan"."DateRan"
SELECT
   "ModRacks"."RackNo",
   COUNT("RackRan"."DateRan") - 1 as  "Day Cycle"  -- << Count number of "DateRan" and subtract one
FROM "RackRan" AS "RackRan",
     "ModRacks" AS "ModRacks"
WHERE "RackRan"."RackNo" = "ModRacks"."RackNo"
  AND "RackRan"."DateRan" >= "ModRacks"."DateMod"
GROUP BY "ModRacks"."RackNo"  -- << Return each UNIQUE "RackNo"
ORDER BY "ModRacks"."RackNo" ASC

Explanation: The above Query is called an AGGREGATE query. That is because, it is asked to 'operate' on the total result set ( returned values ) and in this case, to COUNT values for each UNIQUE value in the GROUP BY clause: "ModRacks"."RackNo"

Since it is an AGGREGATE Query, EACH column ( field ) returned in the SELECT clause, must EITHER be in the GROUP BY clause ( making it unique ), OR, be a 'calculation' . . . of:
  1. COUNT ( to COUNT of the number of times it finds the value ) Smile
  2. SUM ( to ADD together the numbers )
  3. AVG ( to calculate the AVERAGE of the numbers )
  4. MIN (to return the MINIMUM of the numbers )
  5. MAX (to return the MAXIMUM of the numbers )
  6. VAR_POP ( to return the VARIANCE of the POPULATION )
  7. VAR_SAMP ( to return the VARIANCE of the SAMPLE )
  8. STDDEV_POP ( to return the STANDARD DEVIATION of the POPULATION )
  9. STDDEV_SAMP ( to return the STANDARD DEVIATION of the SAMPLE )

NOTE: Instructions on using SQL SELECT above:
  1. Open your OpenOffice Base file ( *.odb )
  2. Press, Queries on the left
  3. Under Tasks, press, Create Query in SQL View...
  4. You can copy to the clipboard the SQL code above, and, paste it in the Window
  5. Run the Query from the Toolbar, or, Press F5
  6. Modify it if you want
  7. Save the Query to the name of your choice
  8. Use the saved Query to create your report
  9. Says: "Gee Sliderule, that was easy, thanks. Now all I have to do is mark the forum post as [Solved] per Sliderule's instructions at the bottom."

One more item, just as an FYI ( sorry, I could NOT help myself Smile ) . . . the SQL above could also be written for the user be PROMPTED ( asked ) to input a DATE. Notice the WHERE clause, and, the Prompt starts with a colon ( : ), and is one word ( no spaces, so, for clarity, I used an underscore ):

Code:
-- Aggregate Query to return each UNIQUE "ModRacks"."RackNo" and the COUNT less 1 of "RackRan"."DateRan"
SELECT
   "ModRacks"."RackNo",
   COUNT("RackRan"."DateRan") - 1 as  "Day Cycle"  -- << Count number of "DateRan" and subtract one
FROM "RackRan" AS "RackRan",
     "ModRacks" AS "ModRacks"
WHERE "RackRan"."RackNo" = "ModRacks"."RackNo"
  AND "RackRan"."DateRan" >= :Enter_DateRan_From Date -- << Prompt User to enter the "DateRan" date
  AND "RackRan"."DateRan" >= "ModRacks"."DateMod"
GROUP BY "ModRacks"."RackNo"  -- << Return each UNIQUE "RackNo"
ORDER BY "ModRacks"."RackNo" ASC


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

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Tue Oct 11, 2011 7:08 am    Post subject: Close Reply with quote

The code worked great except for two items.

1) The -1 ran the totals low by 1. Example rack X002 has 3 entries the code gave me a result of 2. It's simple enough to correct but....

2) If I add the one back in (remove the -1) some of the racks that haven't been through the process but have been modified, Should be at 0 but are now at 1.

It may help if I clarify the need over the actual content. It's hard to invision a rack issue if you aren't part of our unique project. So I'll put it in terms of something everyone can relate.(minus the field names )

If I have a tabel with colors listed (tblColor) and its content is....
Blue
Red
Yellow
Copper
and I have a file of cars (tblCars)with a field for the color of each car, When I scan the tblCars counting each group of colors as listed in the tblColor I will end up with a couple conditions
1) The tblCar.color will match a tblColor.color and which I want to count it (accumilative) .
2) I will have some tblColor.colors that are not list in the tblCars.color in which case the summary should = 0.
3) There will be some tblCars.colors that aren't listed in the tblColor, in which case those would be ignored.

So my report should be (minus headers/labels) something like
Blue = 2
Red = 0
Yellow = 5
Copper = 1

Meanwhile I really appreciate the above code and I'll continue to play around with it to see if I can get it to work.

Thanks Again
Ace
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Oct 11, 2011 9:02 am    Post subject: Reply with quote

What you are describing above can be accomplished with an SQL Query, but, there are a few QUALIFICATIONS . . . that is . . . things that must be done to accomplish the task.

OK, with the SQL code below, I am making the following assumptions about table and field names:
  1. Table Name: "tblCars"
    1. "ID" integer, Primary Key
    2. "color" varchar_ignorecase '<<< Only important column used in Query
    3. "car" varchar_ignorecase
    4. "year" integer

  2. TableName: "tblColor"
    1. "ID" integer, Primary Key
    2. "color" varchar_ignorecase '<<< Only important column used in Query
OK, now the Query below can be run, to return the results you want . . . BUT . . . a very important thing you have to do first . . . that is . . . since it is using a UNION clause, it will ONLY work if you instruct OpenOffice Base to run the Query directly . . . explained below ( comments for the Query start with two dashes, and, are comments only, not code Smile ):

Code:
-- This is a UNION Query, so, it MUST use Run SQL command directly
-- This part gets the COUNT of colors in "tblCars" matched to "tblColor"
Select
       "tblCars"."color",
       COUNT(*) as "Count of Colors"
From "tblCars",
     "tblColor"
Where "tblCars"."color" = "tblColor"."color"
Group By "tblCars"."color"

   UNION   -- << This is a UNION Query therefore can ONLY be run in direct mode, withOUT Base Parser

-- This part gets the "tblColor"."color" NOT IN "tblCars"."color" and 'hard-codes' a zero
-- This is to match the column definitions ( number of fields and field type ) in the top Query
Select
       "tblColor"."color",
       0 as "Number Found"   -- <<< Since NO records found hard code 0
From "tblColor"
Where NOT EXISTS
      ( Select
               "tblColor"."color"
        From "tblCars"
        Where "tblColor"."color" = "tblCars"."color" )
Order By "tblCars"."color" asc   -- << Order at the bottom

Explanation: Above is using TWO SELECT clauses is ONE Query, using, the UNION statement ( see links below for a quick tutorial on UNION ). The second SELECT uses a NOT EXISTS clause to return only the "tblColor"."color" that do NOT EXIST in "tblCar" table.

Since this is a UNION query, and, the OpenOffice Base parser does NOT yet 'support' a UNION query, you can still use it and get the results you want, but, you must run it in DIRECT mode.

Either:
  1. On the toolbar, press the Run SQL command directly icon ( SQL word with green check mark )
  2. From the Menu: Edit -> Run SQL command directly should have a check mark next to it

References to SQL UNION tutorials:
  1. http://www.sql-tutorial.com/sql-union-sql-tutorial/
  2. http://www.w3schools.com/SQl/sql_union.asp

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

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Tue Oct 11, 2011 10:48 am    Post subject: Union Reply with quote

Thanks for the help. I'll read the tutorials. I've worked with Unions before about 7 years ago in MSAcess. And then it was to accomplish a given task and never thought of again Very Happy

I'll keep you/group posted when I get in a position to trial. Meanwhile, just for clarification, are you saying I won't be able to use the query from a report? or I need the report to access the SQL code directly?

Either way, the data is the most important even if it's just a query table as it eliminates a lot of "scanning spread sheets" and counting entries manually. Not to mention eliminating the human error factor.

Thanks again and I'll be back to update.

Ace
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Tue Oct 11, 2011 10:48 am    Post subject: Union Reply with quote

Thanks for the help. I'll read the tutorials. I've worked with Unions before about 7 years ago in MSAcess. And then it was to accomplish a given task and never thought of again Very Happy

I'll keep you/group posted when I get in a position to trial. Meanwhile, just for clarification, are you saying I won't be able to use the query from a report? or I need the report to access the SQL code directly?

Either way, the data is the most important even if it's just a query table as it eliminates a lot of "scanning spread sheets" and counting entries manually. Not to mention eliminating the human error factor.

Thanks again and I'll be back to update.

Ace
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Tue Oct 11, 2011 10:49 am    Post subject: Union Reply with quote

Thanks for the help. I'll read the tutorials. I've worked with Unions before about 7 years ago in MSAcess. And then it was to accomplish a given task and never thought of again Very Happy

I'll keep you/group posted when I get in a position to trial. Meanwhile, just for clarification, are you saying I won't be able to use the query from a report? or I need the report to access the SQL code directly?

Either way, the data is the most important even if it's just a query table as it eliminates a lot of "scanning spread sheets" and counting entries manually. Not to mention eliminating the human error factor.

Thanks again and I'll be back to update.

Ace
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Oct 11, 2011 11:05 am    Post subject: Reply with quote

Ace:

You said / asked:

Ace wrote:
I'll keep you/group posted when I get in a position to trial. Meanwhile, just for clarification, are you saying I won't be able to use the query from a report? or I need the report to access the SQL code directly?


I never use reports using the Writer option, but, I think you can use the query that way.

When using ORB ( Oracle Report Builder ) which is what I always use . .. you certainly CAN use the Query as written . . . assuming, you save the Query rather than entering the SQL as code.

If you elect to 'hard code' the SQL into an ORB, simply DO NOT let it 'analyze' the SQL ( this means use the Base Parser ). I hope that is clear, as clear as mud. Smile

Another option, if you so desired, is to use the Query to create a View ( right click on the Query, and, choose . . . Create as View ). This way, you can use the View, just like a table, in any report of your choice. Smile

I hope this helps, please be sure to let me / us know. And when appropriate, please be sure to mark this forum post as [Solved] per instructions at the bottom. Best of continued success.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Wed Oct 12, 2011 6:04 pm    Post subject: Almost There Reply with quote

Well I changed the code to fit my tables. It left me with one issue. If I run the code with the SQL direct button activated I get a "Data Content could not be loaded" error. If I deactivate the button I get. "The given command is not a Select statement, Only Queries are allowed. " Any idea of what I've done wrong?

BTW the code is

Select
"RackRan"."RackNo",
COUNT(*) as "Rack Cycles"
From "ModRacks",
"RackRan"
Where "RackRan"."RackNo" = "ModRacks"."RackNo" AND "RackRan"."DateRan" >= "ModRacks"."DateMod"
Group By "ModRacks"."RackNo"

UNION
Select
"ModRacks"."RackNo",
0 as "Total Cycles" -- <<< Since NO records found hard code 0
From "ModRacks"
Where NOT EXISTS
( Select
"ModRacks"."RackNo"
From "RackRan"
Where "ModRacks"."RackNo" = "RackRan"."RackNo" )
Order By "ModRacks"."RackNo" asc -- << Order at the bottom

Thanks
Ace
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Oct 12, 2011 6:26 pm    Post subject: Reply with quote

Ace:

You said:

Ace wrote:
Any idea of what I've done wrong?

Yes.

Try the following SQL code:

Code:
Select
   "ModRacks"."RackNo",   -- << Make sure this is the field you want, same as in Group By clause
   COUNT(*) as "Rack Cycles"
From "ModRacks",
     "RackRan"
Where "RackRan"."RackNo" = "ModRacks"."RackNo"
  AND "RackRan"."DateRan" >= "ModRacks"."DateMod"
Group By "ModRacks"."RackNo"   -- Make sure this is the field you want, each UNIQUE value here

   UNION

Select
   "ModRacks"."RackNo",
   0 as "Total Cycles" -- <<< Since NO records found hard code 0
From "ModRacks"
Where NOT EXISTS
   ( Select
        "ModRacks"."RackNo"
     From "RackRan"
     Where "ModRacks"."RackNo" = "RackRan"."RackNo" )
Order By "ModRacks"."RackNo" asc -- << Order at the bottom


Explanation: In your FIRST Select, you had the field "RackRan"."RackNo", BUT, your Group By clause had "ModRacks"."RackNo" . I can only assume you really wanted a "ModRacks"."RackNo" in the Select and the Group By, since, that is also the field you defined in the Order By at the bottom. Smile

So,, make sure you change the TOP one like above . . . I assume that is what you want.

See the SECOND post by Sliderule here:

Sliderule - Second Post Explanation wrote:
Since it is an AGGREGATE Query, EACH column ( field ) returned in the SELECT clause, must EITHER be in the GROUP BY clause ( making it unique ), OR, be a 'calculation'


Suggestion: This is sort of like putting together some pieces. That is, make sure the first one ( top Select ) works as desired, and, the second one works as desired, then, just put them both together with a UNION between them. Smile

One More Note: The code above, should be copied and pasted after pressing: Create Query in SQL View... . . . and . . . on the toolbar, Run SQL command directly icon . . . that is, run as a Query.

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

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Thu Oct 13, 2011 7:00 pm    Post subject: Code Works Reply with quote

Well the change in code did work and I appreciate the patience. My logic doesn't work however. Notice I placed a check in the query to verify that the entries in the RackRan file are after the modification date listed in the ModRacks file. And it is working to some degree. Here is my issue. I have a total of 95 modified racks. So my summary should show all 95 racks even those with totals of 0s. However the query listed gives me 78 racks listed. Once I compared the tables I found five possible conditions

1) RackRan Date is after the ModRack Date for a common Rack No. The record is counted. GOOD
2) RackRan entries for a Rack that hasn't been modified. The entries are ignored. GOOD
3) RackRan dates for a common rack no. are both before and after the ModRack date. This is working. If a Racks has been ran both before and after the modification activity then only the after date is counted. GOOD,
4) ModRack entry for a modified rack but has not been ran (RackRan) Query shows 0. GOOD
5) ModRack has an entry for a modified rack. RackRan has an entry for the same rack but the dates in RackRan are all before the modification. So the count should be 0 on the report but instead the query ignores the ModRack.. This is the missing racks from my report. NOGOOD.

So if you have any ideas. Please let me know. I'll put SOLVE on the top entry because you answered my original question.

Thanks
Ace
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Oct 13, 2011 7:57 pm    Post subject: Reply with quote

Try this, create a SEPARATE Query, just for testing. Remember, I do NOT have your database, so, I canNOT test it myself to confirm it will work as desired.

The idea here, is to meet that LAST condition you indicated above:

Ace wrote:
5) ModRack has an entry for a modified rack. RackRan has an entry for the same rack but the dates in RackRan are all before the modification. So the count should be 0 on the report but instead the query ignores the ModRack.. This is the missing racks from my report. NOGOOD.


Code:
-- You can add an appropriate comment here for documentation to your likeing
Select
   "ModRacks"."RackNo",
   0 "Rack Cycles"
From "ModRacks"
Where EXISTS
   (Select
      "ModRacks"."RackNo"
    From "RackRan"
    Where "RackRan"."RackNo" = "ModRacks"."RackNo"
      AND "RackRan"."DateRan" < "ModRacks"."DateMod")
     
  AND NOT EXISTS
   (Select
       "ModRacks"."RackNo"
    From "RackRan"
    Where "RackRan"."RackNo" = "ModRacks"."RackNo"
      AND "RackRan"."DateRan" >= "ModRacks"."DateMod")

Explanation: If this returns only the number of "ModRacks"."RackNo" that you want, you can copy and paste this to your other already created Query. This should go BEFORE the Order By clause, and, also, include another UNION statement before it ( so your ultimate Query will have 2 UNION statements. Smile

Please be sure to let me / us know. And, if it works, or, you find some other technique to work, for others reading the forum, please be sure to include your final SQL.

Sliderule
Back to top
View user's profile Send private message
Ace Blackwell
General User
General User


Joined: 07 Oct 2011
Posts: 13

PostPosted: Fri Oct 14, 2011 12:14 pm    Post subject: Perfect. Reply with quote

Thanks. Worked like a charm. Pretty impressive too given you didn't have the actual database to work with.

Hey, last quick question. Is OOSQL (HSQL?) any different from other SQLs? Meaning if I read up on SQL in general, will I run into a lot of syntax issues?

Thanks Again. Slideruler is the man! Very Happy

Ace
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group