Duplicate records in report

Getting your data onto paper - or the web - Discussing the reports features of Base

Duplicate records in report

Postby gpaunica » Sat Jun 07, 2014 9:27 pm

Hello,
I have build a small base in openoffice base.
I have build a querry based on 3 tables.
The problem is in report like in atachment the column "Val_fact" it repeats. I want to ask you if is there any option that on that column the value to apear only one time?
In Access when I build the report is an option so to apear one time the value from "Val_fact" column.
But I want to learn Base because is free.
Thanks a lot.
Attachments
EVIDENTA FURNIZORI.odb
small base
(50.91 KiB) Downloaded 52 times
openoffice 4.1.0 on windows 7
gpaunica
 
Posts: 17
Joined: Thu Jun 05, 2014 1:23 pm

Re: Duplicate records in report

Postby MTP » Sun Jun 08, 2014 2:41 am

In the file you posted, the query SOLD FURNIZORI returns three lines. All three lines have something different from the other two.

Two lines do have the same "Val_fact". Which one of those two do you want to appear?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Duplicate records in report

Postby gpaunica » Sun Jun 08, 2014 6:20 am

It doesn't mather which one the ideea is to apear only once.
It can do with Base?
openoffice 4.1.0 on windows 7
gpaunica
 
Posts: 17
Joined: Thu Jun 05, 2014 1:23 pm

Re: Duplicate records in report

Postby MTP » Mon Jun 09, 2014 9:31 pm

Base can do it. It is more complicated than in Access. I think this query will do as you want:
Code: Select all   Expand viewCollapse view
SELECT "FURNIZORI"."Den_fz", "FURNIZORI"."Sold_initial", "INTRARI"."Val_fact", X."Suma_platita", "FURNIZORI"."Sold_initial" + "INTRARI"."Val_fact" - X."Suma_platita" AS "Sold_fz", "INTRARI"."Nr_fact", X."Nr_chit"
   FROM "INTRARI"
   INNER JOIN "FURNIZORI"
      ON "INTRARI"."Cod_fz" = "FURNIZORI"."Cod_fz"
   INNER JOIN (SELECT a."Suma_platita", a."Nr_chit", a."Nr_fact", COUNT(*) AS "Rank"
               FROM "PLATI" a
               LEFT JOIN "PLATI" b
                  ON a."Nr_fact" = b."Nr_fact" AND a."ID" >= b."ID"
               GROUP BY a."Suma_platita", a."Nr_chit", a."Nr_fact" ) X
      ON X."Nr_fact" = "INTRARI"."Nr_fact" AND X."Rank" = 1


I saved this query in the attached file.
Attachments
EVIDENTA FURNIZORI (2).odb
(50.7 KiB) Downloaded 57 times
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest