Results 1 to 9 of 9

Thread: count details

  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Unanswered: count details

    i have a clients table, and invoice table and a payments table, all connected through the client table and by ClientCode. i want to be able to query the database, so that it would return the number of invoices and the number of payments, but it keeps on brining back the wrong result. here is want i have:

    SELECT Count(Clientpayment.PaymentNo) AS CountOfPaymentNo, Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    FROM Client,ClientPayment, ClientInvoice
    where Client.ClientCode = ClientPayment.ClientCode
    and Client.ClientCode = ClientInvoice.ClientCode;

    any ideas?

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: count details

    Originally posted by conorokane
    i have a clients table, and invoice table and a payments table, all connected through the client table and by ClientCode. i want to be able to query the database, so that it would return the number of invoices and the number of payments, but it keeps on brining back the wrong result. here is want i have:

    SELECT Count(Clientpayment.PaymentNo) AS CountOfPaymentNo, Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    FROM Client,ClientPayment, ClientInvoice
    where Client.ClientCode = ClientPayment.ClientCode
    and Client.ClientCode = ClientInvoice.ClientCode;

    any ideas?
    I guess the numbers returned are too high, this will be the case when you have multiple records per ClientCode in any or both tables. I suppose you really need 2 queries:

    SELECT Count(Clientpayment.PaymentNo) AS CountOfPaymentNo
    FROM Client INNER JOIN ClientPayment
    ON Client.ClientCode = ClientPayment.ClientCode;

    SELECT Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    FROM Client INNER JOIN ClientInvoice
    ON Client.ClientCode = ClientInvoice.ClientCode;

    BTW, it looks like you have an Oracle background, in MS Access you can also use the INNER JOIN clause for table joins, like I did in the previous statements.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that depends what your trying to do,
    if you want to count all payment and invoices for a single client then i would sugest

    SELECT Count(Clientpayment.PaymentNo) AS CountOfPaymentNo
    FROM ClientPayment
    WHERE ClientPayment.ClientCode = [forms]![<formname>]![ClientCode]

    SELECT Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    FROM ClientInvoice
    WHERE ClientInvoice.ClientCode = [forms]![<formname>]![ClientCode];

    this will allow you run the query with different clients or if it just 1 client change [forms]![<formname>]![ClientCode] with the code


    if you want to find out how many payments and invoices you've got then

    SELECT Count(Clientpayment.PaymentNo) AS CountOfPaymentNo,
    FROM ClientPayment

    select Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    from ClientInvoice


    your way will dupliace every record in one table for every record on the other ie 3 in one and two in the other the count in both cases will be 6

    the only way to get these numbers is with 2 seperate queries on for each
    like the other guy says only you don't need the inner join unless you want to caount the invoices that have a valid client ie not blank or not on the list usally a invoice has a client and your in deep trouble if the client numbers dont match
    Last edited by m.timoney; 02-26-03 at 13:35.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Feb 2003
    Posts
    4
    what i am trying to do is develop the sql statement for a chart in VB, and i think a chart can have only one sql statement assigned to it. I want to show a chart showing the number of payments and number of clients for a specific client over the year. X axis being months of the year and y axis showing the number. Any ideas

  5. #5
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    Originally posted by conorokane
    what i am trying to do is develop the sql statement for a chart in VB, and i think a chart can have only one sql statement assigned to it. I want to show a chart showing the number of payments and number of clients for a specific client over the year. X axis being months of the year and y axis showing the number. Any ideas
    You can do this as follows: make two queries Q1 and Q2 for the queries mentioned in m.timoney's post and mine, and make a third query with the following SQL text:

    SELECT Clients.ClientCode, Q1.CountOfClientCode, Q2.CountOfClientCode
    FROM (Q1 RIGHT JOIN Client ON Q1.ClientCode = Client.ClientCode) LEFT JOIN Q2 ON Client.ClientCode = Q2.ClientCode;

  6. #6
    Join Date
    Feb 2003
    Posts
    4
    that works fine but how do i add the parameter ClientCode to it where it will only return data for that client. I am calling it from VB and not too sure of it. A little help will be great

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by conorokane
    that works fine but how do i add the parameter ClientCode to it where it will only return data for that client. I am calling it from VB and not too sure of it. A little help will be great
    the [forms]![<formname>]![<Location of clientcode>] part will read the clientcode from a form txt/combo/list box
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Feb 2003
    Posts
    4
    thanks, thats works fine, using the three queries. But i am still stuck with breaking it down by month. Both the ClientInvoice and ClientPayment Table have payments dates and i want to bring back results for the client based on a monthly basis. here is my code so far, any ideas on how to add the dates querying to it?

    1)
    SELECT Client.ClientCode, Count(Clientpayment.PaymentNo) AS CountOfPaymentNo
    FROM Client INNER JOIN ClientPayment ON Client.ClientCode=ClientPayment.ClientCode
    GROUP BY Client.ClientCode;

    2)
    SELECT Client.ClientCode, Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    FROM Client INNER JOIN ClientInvoice ON Client.ClientCode = ClientInvoice.ClientCode
    GROUP BY Client.ClientCode;

    3)
    SELECT Client.ClientCode, Query1.CountOfPaymentNo, Query2.CountOfInvoiceNo
    FROM (Query1 RIGHT JOIN Client ON Query1.ClientCode=Client.ClientCode) LEFT JOIN Query2 ON Client.ClientCode=Query2.ClientCode
    WHERE Client.ClientCode='EamonWHoll';

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    firstly save your self some processing and lose the joins in the first two and unless you want some data from the client table you don't need to inclued it in any of the queries (ClientCode don't count as it's in the other two)

    second lets just refine what your after do you want to do do you want to have a count for each month, or do you want a count for a month

    basicly to do these is
    SELECT ClientCode, Count(PaymentNo) AS CountOfPaymentNo, format(date,"mm/yy") as DateM_Y
    FROM ClientPayment
    GROUP BY ClientCode, DateM_Y;

    SELECT ClientCode, Count(InvoiceNo) AS CountOfInvoiceNo, format(date,"mm/yy") as DateM_Y
    FROM ClientInvoice
    GROUP BY ClientCode, DateM_Y;

    SELECT Client.ClientCode, CountOfPaymentNo, CountOfInvoiceNo, query2.dateM_Y as DateM_Y
    FROM query1 INNER JOIN query2 ON (query1.ClientCode = query2.ClientCode) AND (query1.dateM_Y = query2.dateM_Y)
    WHERE query1.ClientCode='EamonWHoll'
    GROUP BY DateM_Y;

    if for only one month
    SELECT Client.ClientCode, CountOfPaymentNo, CountOfInvoiceNo
    FROM query1 INNER JOIN query2 ON quer1.ClientCode = query2.ClientCode
    WHERE query1.ClientCode='EamonWHoll' and DateM_Y = #dec/2002#;

    note if DateM_Y = #dec/2002#; don't work you'll need to replace it with
    month(dateM_Y) = 12 and year(dateM_Y) = 2002; i've never tried the first fomat but i think it'll work

    Originally posted by conorokane
    thanks, thats works fine, using the three queries. But i am still stuck with breaking it down by month. Both the ClientInvoice and ClientPayment Table have payments dates and i want to bring back results for the client based on a monthly basis. here is my code so far, any ideas on how to add the dates querying to it?

    1)
    SELECT Client.ClientCode, Count(Clientpayment.PaymentNo) AS CountOfPaymentNo
    FROM Client INNER JOIN ClientPayment ON Client.ClientCode=ClientPayment.ClientCode
    GROUP BY Client.ClientCode;

    2)
    SELECT Client.ClientCode, Count(ClientInvoice.InvoiceNo) AS CountOfInvoiceNo
    FROM Client INNER JOIN ClientInvoice ON Client.ClientCode = ClientInvoice.ClientCode
    GROUP BY Client.ClientCode;

    3)
    SELECT Client.ClientCode, Query1.CountOfPaymentNo, Query2.CountOfInvoiceNo
    FROM (Query1 RIGHT JOIN Client ON Query1.ClientCode=Client.ClientCode) LEFT JOIN Query2 ON Client.ClientCode=Query2.ClientCode
    WHERE Client.ClientCode='EamonWHoll';
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •