Possibly wrong forum - query/report

[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?

Hope this makes sense.

Thanks for your help.
Gareth.

 

[gemma-the-husky]

use a totals query to generate the totals - this casn also generate counts, and you can sort on the counts

 

[gazsharpe101]

I have tried doing a totals query on the amounts field but as each invoice id is unique, it isn't making the slightest bit of difference to the outcome of the query/report.

Thanks for your help.

 

[gazsharpe101]

I have had an idea, here is an example of the report:

Summary of invoices for all companies between x and y dates:
ABC LTD - Amount Raised £75
DEF LTD - Amount Raised £70
XYZ LTD - Amount Raised £300
Report summary:
Total value of invoices raised: £445.

That is a snapshot view where each entry is under the COMPANY header and the textbox that shows the amount is called 'txtTotalAmount'.

Would it be possible to have a list box in the report summary that gets filled using each of the 'txtTotalAmount' values and orders them accordingly with the highest at the top. Then next to that I can fill another listbox showing the corresponding company names therefore showing the companies that I have invoiced the most?

Thanks.
Gareth.

 

[gemma-the-husky]

use a totals query, but only put certain columns in the query

as you say, including the invoice number will give a separate row for each invoice - but you dont need the invoice no in the query, because you are summing the invoices

you only need a few carefully selected fields