Drew,
Thanks for help, but I still have a problem...
I'm newbie in sql and I'm trying to translate your instructions to the
reality of my database. I have these tables:
Clientes (IDCliente, Nome, Sobrenome, CPF, RG, Endereço, Bairro,
EndereçoEmail, NúmeroTelefone, NúmeroCel, ValorMensalidade)
Where this is the table that store information about the clients and:
IDCliente - is the Primary Key with a auto numeration integer
Nome, Sobrenome - store the name and second name of client
CPF, RG - store the number of the documents (Brazilian documents)
Endereço, Bairro - store the complete adress of client
EndereçoEmail, NúmeroTelefone, NúmeroCel - store the email, phone and cell
phone of clients
ValorMensalidade - store the value of month payment, this value is defined
and stored in another table, named Mensalidades, where one primary key
column store the values of month payments.
Pagamentos (IDPagamento, Mes, Ano, IDCliente)
Where "Pagamentos" is the table that store the information about the
payments in each month and:
IDPagamento - is the primary key with a integer auto numeration column
Mes - is the column that store the name of month of payment. The name of
month show in this column is stored in a separated table named Mes with a
primary column key where is stored all the name of months
Ano - this column store the year of payment. The years are stored in a
separated table named Ano, with a primary key column where is the years for
payment.
IDClient - Store the client identification code generated in the Primary Key
of table "Clientes"
Assuming the tables/columns I have this queries:
adimplentes - that shows me the name, second name, address, client code of
clients that have paid in a specific year and month. The sql code of this
query is this:
SELECT "Pagamentos"."IDCliente", "Clientes"."Nome", "Clientes"."Sobrenome",
> "Clientes"."Endereço", "Clientes"."Bairro", "Pagamentos"."Mes",
> "Pagamentos"."Ano" FROM "Pagamentos", "Clientes" WHERE (
> "Pagamentos"."IDCliente" = "Clientes"."IDCliente" ) AND ( (
> "Pagamentos"."Mes" = :Mês AND "Pagamentos"."Ano" = :Ano ) )
But when I try to use the code of the second query named qryDelqClients,
whith this code:
SELECT "Clientes"."IDCliente" AS "AllClient", "adimplentes"."IDCliente" AS
> "DelqID" FROM { OJ "Clientes" LEFT OUTER JOIN "adimplentes" ON
> "Clientes"."IDCliente" = "adimplentes"."IDCliente" }
The Openoffice.Org Base show me this error message:
Table not found in statement [SELECT "Clientes"."IDCliente" AS "AllClient",
"adimplentes"."IDCliente" AS "DelqID" FROM { OJ "Clientes" LEFT OUTER JOIN
"adimplentes" ON "Clientes"."IDCliente" = "adimplentes"."IDCliente" }]
What is wrong whith this code?
P.S.- Sorry for my bad english....
2008/3/29, Drew Jensen <[EMAIL PROTECTED]>:
>
> 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]
>
>
