[Solved] GROUP function causes crash

Discuss the database features
Post Reply
Bruce in Louisville
Posts: 6
Joined: Thu Nov 26, 2020 12:59 am

[Solved] GROUP function causes crash

Post by Bruce in Louisville »

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

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)
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: GROUP function causes crash

Post by Sliderule »

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

    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.
Post Reply