Help with Group and Sort function in 2007

[kingdoz]

Hi Guys,

I have been breaking my head trying to make this work.

I have a problem with an invoices database, and Im trying to get a report that will list all my unpaid invoices in date order but group them together by company name so we see all invoices of that company (but the oldest companies first if that makes sense)..

What I mean is that I have a fields called InvoiceID and the smallest number is oldest, and a field called CompanyName.

I am trying to group by CompanyName not "with A on top" which is teh only option Access 2007 seems to have but by order of InvoiceID.

EG.
invoice 1 = Owed by Company C
invoice 2 = Owed by Company A
invoice 3 = Owed by Company C
invoice 4 = Owed by Company B

I need it to Group like this.

Company C: Invoice 1, Invoice 3
---
Company A: Invoice 2
---
Company B: Invoice 4 (Note that I do not want it to list Company C again as it already listed it earlier).
---

If you can help or advise me how on earth its possible please do let me know

Thanks
Doz

 

[PNGBill]

You use grouping and sorting and also group Headers and footers.

Place the control for CompanyName in the CompanyName Header section.

You may need to make a header for InvoiceID and place the InvoiceID control in that.

Other controls are in the body of the report.

Use the up arrows or down arrows to get the CompanyName Header above the InvoiceID header.

Sort as you wish and the result should be what you want.

You have a few other options like keeping to one page etc. test these to see how they effect the result.

 

[kingdoz]

Hi PNGBill,

Thanks for your help.

Unfortunately it doesnt work. It seems when I set a group in a report it wont let me sort via another field this way. Im still stuck.

I added a Group called CompanyName, but it will only let me sort by A-Z or Z-A first and only then by Invoice ID. What that means is that my report lists companies A,B,C etc, then Invoices sorted correctly but within the companies by A-Z. Im still trying to group by companies, but list the companies by InvoiceID first. Eg oldest invoice and the company with all the invoices under that company, then the next company group but by the next outstanding invoice and not by the next alpabeticakl company name.

 

[PNGBill]

You can use two queries.

The first query selects all records and you have the paramaters to be user input.

The 2nd query, uses the first query as it's record source and it does the grouping.

 

[vbaInet]

See attached. Have a look at rptInvoices.