[Solved] GROUP function causes crash

Discuss the database features

[Solved] GROUP function causes crash

Postby Bruce in Louisville » Thu Nov 26, 2020 1:16 am

Hello! After years of using Access on Windows, and building applications in it, I am trying out Base in order to build a database for my business. Here's my problem:

I'm trying to build a summary query, using the design tool. But if I put a GROUP function on a field, then run the query, Open Office crashes.

I've looked for documentation on how to use GROUP (GroupBy), but haven't found anything that explains what I'm doing wrong.

FWIW, here's an example of what I'm trying to do.

Code: Select all   Expand viewCollapse view
ID  Type  Date
01   AA   01/02/2020
01   AA   01/03/2020
01   AA   01/04/2020
01   BB   01/05/2020
02   AA   01/02/2020
02   BB   01/05/2020


I would like a query that shows that person 01 opened 3 AA letters and 1 BB letter, and the person 02 opened 1 AA letter and 1 BB letter.

I put a GROUP on the ID field and on the Type field, but crash-a-roni.

Thoughts?
Last edited by Bruce in Louisville on Thu Nov 26, 2020 3:21 am, edited 2 times in total.
Open Office 4.1.8 ??? Mac OS Catalina (soon to be Big Sur)
Bruce in Louisville
 
Posts: 6
Joined: Thu Nov 26, 2020 12:59 am

Re: GROUP function causes crash

Postby Sliderule » Thu Nov 26, 2020 2:26 am

IMPORTANT NOTE: The instructions below, assumes your database back-end is HSQL ( or a similar database ). If NOT, and you are still having issues, please be sure to let the forum know the database back-end you are using. :crazy:

Please follow the instructions below, and, see if this meets your needs:

  1. Open your OpenOffice / LibreOffice Base file ( *.odb )

  2. Click on the Queries icon on the left

  3. Under Tasks, click on Create Query in SQL View...

  4. Copy and Paste the SQL Query below:

    Code: Select all   Expand viewCollapse view
    Select
       "MY_TABLE"."ID" as "ID",
       COUNT(*) as "COUNT",
       "MY_TABLE"."Type" as "letter"
    From "MY_TABLE" as "MY_TABLE"
    Group By "MY_TABLE"."ID",
             "MY_TABLE"."Type"

  5. Change any table ( for example: "My_TABLE" ) or column names ( "ID", "Type" ) above to match exactly the names in your database

  6. Run the Query, either:
    1. Press the Run Query icon on the Toolbar
    2. From the Menu: Edit -> Run Query

Explanation: If you want to see what the Query would look like, if you used the GUI ( Graphic User Interfacae ), either:

  1. From the Toolbar, press the icon Switch Design View On/Off
  2. From the Menu: View -> Switch Design View On/Off
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.
User avatar
Sliderule
Volunteer
 
Posts: 1247
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests