Saul Lima wrote:
I have one databe created with the Openoffice.org Base 2.3. In this database
there are two tables, one for my clients and another to register the payment
of every client in every month.
I want to generate one report that contains all the clients that not paid.
This clients are all that is in the table Clients but AREN'T in payment
table. Look this example:
I want to generate a report that contains the clients (CCC and DDD in this
example) that are not included in the Payment table. How I can do this?
This type of query can drive guys that prefer SQL over GUI query
builders nuts, cuz you gotta think different.
At the core you first get a set of the clients that did pay, then you
get a set of clients NOT IN the first set, right
So - assuming tables
Client( ClientID, ClientName )
Payment( PaymentID, ClientID, PaymentDate, Amount )
The SQL statement could be then
SELECT C.* FROM "Client" C
WHERE C."ClientID" NOT IN (
SELECT P."ClientID" FROM "Payment" P
WHERE MONTH( P."PaymentDate" ) = :PmtMonth
AND YEAR( P."PaymentDate" ) = :PmtYear
)
Of course you substitute a month number and year number for PmtMonth and
PmtYear first. So you could open a query designer in SQL view and enter:
SELECT C.* FROM "Client" C
WHERE C."ClientID" NOT IN (
SELECT P."ClientID" FROM "Payment" P
WHERE MONTH( P."PaymentDate" ) = 1
AND YEAR( P."PaymentDate" ) = 2008
)
and get all the delinquent clients for January 2008 - however, an SQL
select statement like this one would require that this query be run with
as 'Direct SQL' or with the Base query analyzer turned off.
Now if there where a way to make it so that the query analyzer could be
left on then you could leave those named parameters and be prompted for
these parameters each time the query is run, say when a report based on
the query is run.
You use a Query in Query for this.
Actually I will need to create 3 queries.
First query named qryPayedClientstForMonth as
SELECT "ClientID" FROM "Payment" AS "P"
WHERE MONTH( "P"."PaymentDate" ) = :PmtMonth
AND YEAR( "P"."PaymentDate" ) = :PmtYear
This query is what prompts you for the month and year when you run it.
A second query named qryDelqClients as:
SELECT "Client"."ClientID" AS "AllClient", "qryPayedClientstForMonth
"."ClientID" AS "DelqID"
FROM { OJ "Client" LEFT OUTER JOIN "qryPayedClientstForMonth "
ON "Client"."ClientID" = "qryPayedClientstForMonth "."ClientID" }
In this second query then I have 2 columns one with every clientID from
the Client table and a second column with ClientIDs for those that payed
and NULL for those that did not.
Now I can create the 3rd and final query, the one that is used by the
report.
SELECT "qryDelqClients"."AllClient", "Client"."ClientName"
FROM "qryDelqClients", "Client"
WHERE "qryDelqClients"."AllClient" = "Client"."ClientID"
AND "qryDelqClients"."DelqID" IS NULL
Even though the parameters are buried 2 queries down Base will still
prompt you for the month and year when it is run, because each query is
such that the Base query analyzer is turned on.
HTH
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
