[Solved] Creating a query of two tables without repeating...

Creating tables and queries

[Solved] Creating a query of two tables without repeating...

Postby PunkintheGutter » Wed Sep 19, 2018 6:46 pm

I am working on a simple database with two tables: Events and CAPs. There is a one to many relationship between them where the table CAPs has the foreign key EventID. At the moment the query I'm using to display all information in both tables uses a left outer join, the problem with this is that for each CAP associated with an event it repeats the event data. I would like to know if it's possible to make it display the data from each event only once when there are multiple CAPs. If this isn't something possible to do with queries I was thinking there might be a way to do it with the Report functionality of base.
My current query:
Code: Select all   Expand viewCollapse view
SELECT "Events1"."EventID" AS "EventID", "Events1"."TimeStamp" AS "TimeStamp", "Events1"."Location" AS "Location", "Events1"."Type" AS "Type", "Events1"."Description" AS "Description", "Events1"."Prelim_Rep_Issued" AS "Prelim_Rep_Issued", "Events1"."Final_Rep_Issued" AS "Final_Rep_Issued", "Events1"."Email_Note" AS "Email_Note", "Events1"."Phone_Note" AS "Phone_Note", "CAPs1"."CAPID" AS "CAPID", "CAPs1"."Description" AS "Description", "CAPs1"."Response" AS "Response", "CAPs1"."Closed_Status" AS "Closed_Status", "CAPs1"."EventID" FROM { OJ "Events1" LEFT OUTER JOIN "CAPs1" ON "Events1"."EventID" = "CAPs1"."EventID" }

What_I_Get.png
What I get with my current query.

What_I_Want.png
What I want to see.

(You can ignore the second or I guess third eventID coulmn that shows up here, I fixed what I did there.)
Last edited by PunkintheGutter on Sun Sep 23, 2018 8:57 am, edited 1 time in total.
OpenOffice 4.1.5, Windows 10
PunkintheGutter
 
Posts: 2
Joined: Wed Sep 19, 2018 6:28 pm

Re: Creating a query of two tables without repeating entries

Postby eremmel » Wed Sep 19, 2018 10:38 pm

You can do that in a report, but not with queries.
On a form you can add your CAP data and on a subform you can add your Events, so that for the selected CAP the corresponding events are shown.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: Creating a query of two tables without repeating entries

Postby UnklDonald418 » Thu Sep 20, 2018 9:51 pm

According to your signature you are using OpenOffice 1.4.2 which doesn't appear to be correct
https://wiki.openoffice.org/wiki/Product_Release
Please update your signature.

The standard report writer in to OpenOffice doesn't offer much flexibility, but if you install the Oracle-report-builder extension (it is built in to LibreOffice)
https://extensions.openoffice.org/en/project/oracle-report-builder
You can use your query as the data source for a report. Group records on the "EventID" and add the other fields you want to appear on the Header and put the rest in the Details area of the report.

I had seen a strategy for suppressing the display of query fields so I thought I would give it a try using tables structured according to what I saw on your included images.
First the records in "CAPs1" are grouped according to "EventID" and then ranked within the groups by "CAPID"

Code: Select all   Expand viewCollapse view
SELECT "e"."CAPID",
                "e"."Description",
                "e"."Response",
                "e"."Closed_Status",
                "e"."EventID",
                ( SELECT COUNT( * )
                   FROM "CAPs1" AS "d"
                   WHERE "e"."EventID" = "d"."EventID" AND "e"."CAPID" > "d"."CAPID" ) AS "RANK" FROM "CAPs1" AS "e"
ORDER BY "e"."EventID", "RANK"


I used that query to create "View01"
Then CASEWHEN statements can be used to suppress the display of fields in the final query based on their "RANK"

Code: Select all   Expand viewCollapse view
SELECT CASEWHEN( "RANK" = 0,  "Events1"."EventID", NULL) AS "EventID",
       CASEWHEN( "RANK" = 0,  "Events1"."Location", NULL) AS "Location",
       CASEWHEN( "RANK" = 0,  "Events1"."Type", NULL) AS "Type",
       CASEWHEN( "RANK" = 0,  "Events1"."Description", NULL) AS "Description",
       CASEWHEN( "RANK" = 0,  "Events1"."Prelim_Rep_Issued", NULL) AS "Prelim_Rep_Issued",
       CASEWHEN( "RANK" = 0,  "Events1"."Final_Rep_Issued", NULL) AS "Final_Rep_Issued",
       CASEWHEN( "RANK" = 0,  "Events1"."Email_Note", NULL) AS "Email_Note",
       CASEWHEN( "RANK" = 0,  "Events1"."Phone_Note", NULL) AS "Phone_Note",
       "V1"."CAPID" AS "CAPID",
       "V1"."Description" AS "Description",
       "V1"."Response" AS "Response",
       "V1"."Closed_Status" AS "Closed_Status",
       "V1"."RANK"
FROM  { OJ "Events1"
       LEFT OUTER JOIN "View01" AS "V1"
       ON "Events1"."EventID" = "V1"."EventID" }


The results look promising, but not all fields with CASEWHEN statements were suppressed.
Also any records in "Events1" that don't have at least one corresponding record in "CAPs1" don't display properly despite the Left Outer Join.
In an Embedded Base database when numeric values like "EventID" are NULL they are displayed as 0.
That behavior isn't seen using a split database
[Wizard] Create a new 'split' HSQL 2.x database
But even then, I've not found a way to suppress the display of Boolean fields.
All of which confirms the sage advice of eremmel.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1225
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Creating a query of two tables without repeating entries

Postby PunkintheGutter » Sun Sep 23, 2018 8:56 am

Thanks for the help, I was trying to figure out how to group multiple fields on one level for a while when I realized how that grouping system sort of did what I want, but now I see that's much easier with the report builder.
OpenOffice 4.1.5, Windows 10
PunkintheGutter
 
Posts: 2
Joined: Wed Sep 19, 2018 6:28 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests