I have a table which contains invoice lines and I am trying to produce a recordset that will produce a list by client with the total owed by that client.

The problem I am having is producing a recordset that will list each client once (maybe using DISTINCT) with the sum of all the invoice lines not paid by that client.

The table looks like this

Company PaidYN PriceLine

Company1 NO 100.00
Company1 NO 10.00
Company2 YES 100.00
Company1 YES 50.00
Company2 NO 60.00

The output I require is

Company Name Total Owed

Company1 110.00
Company2 60.00
ect

The recordset needs to list each company once and calculate the total owed based on PaidYN = NO

Is it possible to do this?


Russell

Let's see, I think you want something like:

SELECT DISTINCT Company, SUM(PriceLine) AS Total_Owed
FROM invoice
WHERE (PaidYN NOT LIKE YES)
GROUP BY Company

Invoice being this table's name.


With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Thanks Glen,

I'll give it a whirl.

Davrus

Russell

Thanks Glen,

I'll give it a whirl.

Davrus

Russell

Thanks Dennis.

This works fine.

Best Regards

Davrus

Russell

Thanks Dennis.

This works fine.

Best Regards

Davrus

Russell