By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,461 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,461 IT Pros & Developers. It's quick & easy.

Query/Report problem

P: 13
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.
Expand|Select|Wrap|Line Numbers
  1. Invoice ID-------Amount------Date Raised-------Company
  2.      1                 55          01/02/2008        ABC Ltd
  3.      2                 300        09/02/2008        XYZ Ltd
  4.      3                 20          10/02/2008        ABC Ltd
  5.      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.
Feb 21 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Company, Count([Company])) AS InvoiceCount
  2. FROM [name of your current report query] GROUP BY Company;
Try something along these lines to get you going.

Feb 21 '08 #2

Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT I.[Invoice ID], 
  2.        I.[Amount],
  3.        I.[Date Raised],
  4.        I.[Company]
  5. FROM [tblInvoice] AS I INNER JOIN (
  6.      SELECT [Company],
  7.             Count(*) AS CountCo
  8.      FROM [tblInvoice]
  9.      GROUP BY [Company]) AS subI
  10.   ON I.Company=subI.Company
  11. ORDER BY subI.CountCo DESC
I had to guess at some of the names but hopefully it is clear.
Feb 22 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.