Query/Report problem

[gazsharpe101]

Hi everyone,

I am creating a query that shows a list of all invoices raised in the last month which shows the date on which they were raised along with the amounts. Each invoices has it's own ID, but there may have been more than one invoice raised to send to the same company for different amounts, i.e.

Invoice ID-------Amount------Date Raised-------Company
     1                 £55          01/02/2008        ABC Ltd
     2                 £300        09/02/2008        XYZ Ltd
     3                 £20          10/02/2008        ABC Ltd
     4                 £70          04/02/2008        DEF Ltd

What I do is run a report showing invoices raised between manually input parameter dates and I have a text box that totals the sums up and lists the total amounts for each company, i.e. ABC Ltd - Amount raised £75.

How do I list the report in order of the company that has had the most invoices raised against it in that period?

I have tried doing a sum on the amounts field on the query and grouping by things but it makes no difference to the outcome of the query which still displays as above. I think it may be something to do with the fact that it has unique invoice ID's or something but that is just a guess really.

Hope this makes sense.

Thanks for your help.
Gareth.

[Stewart Ross]

Hi Gareth. You will need a separate count query which takes as its input your report query; you then create a new query which is the composite of the report query and the count query joined on the company field. Include all fields from your original report query in this composite and the invoice count from the composite query you have now created. Although this repeats the number of invoices for that company on each detail line, subsequent grouping by the report will take care of the redundant repeated values.

Finally, you save the composite report and invoice count query under a new query name, base your report on this instead of the original query, and under the report's sorting and grouping you group by InvoiceCount in descending order.

The SQL for the count query is along the lines of

SELECT Company, Count([Company])) AS InvoiceCount
FROM [name of your current report query] GROUP BY Company;

Try something along these lines to get you going.

-Stewart

[NeoPa]

Basically, I think you need two record sources in your query.
1 is the native data. The other is a query from that table that groups by Company and returns the count of each company. In SQL that would be something like :

SELECT I.[Invoice ID], 
       I.[Amount],
       I.[Date Raised],
       I.[Company]
FROM [tblInvoice] AS I INNER JOIN (
     SELECT [Company],
            Count(*) AS CountCo
     FROM [tblInvoice]
     GROUP BY [Company]) AS subI
  ON I.Company=subI.Company
ORDER BY subI.CountCo DESC

I had to guess at some of the names but hopefully it is clear.