Customize Sorts in Reports

[General Ledger]

Dear All,

First time using this forum.

I am looking for information about how to customize sorting in Access 2007 reports.

Issue #1: I have a report of open sales orders. I am sorting in ascending order and grouping by Salesperson (such as Al, Mary, Vinnie). I have one Salesperson called Stock, which is for small orders that can be filled from inventory and are not associated with an actual salesperson. I would like the Salesperson Stock to appear at the end of the report (such as Al, Mary, Vinnie, Stock), rather than in actual alphabetical order as it does now because I am simply sorting by Salesperson.

Issue #2: Within each Salesperson, I sort in ascending order by Customer Name and Order Number. However, under only Salesperson Stock, I want to sort by Product, Customer Name, and Order Number.

Since I am just a hack at using Access, please be explicit with any replies.

Thanks,

GL

 

[RuralGuy]

My suggestion would be to use the query to create the two sort fields you need for your task called maybe s1 and s2. Then you can set s1 = SalesPerson for all but the Stock and then set it to "zzzzStock" so it will sort last. Then you can set s2 = Product for Stock and "" for everything else which will not effect the sort for non stock items. The last two sort fields are Customer Name and Order Number which are the same for all records.

 

[vbaInet]

Well there's no easy way to solving your issues but let's crack on.

Issue #1:
1. In the query of your report's RECORD SOURCE, create an alias field and put

AliasName: IIF([[COLOR=Red][B]SalesPerson[/B][/COLOR]]="Stock", 2, 1)

You can call AliasName anything you want.
2. Go into your report and your first Sort should be the new Alias field followed by other sorts.

Issue #2 (the hardest bit):
1. Create a query with the sales orders fields as you have in your report, but this time filter it by "Stock". So only the stock orders will be present.
2. Create a report and base it on the query from step 1
3. Sort by Product
4. Create a subreport on your main report, drop it in the Detail Section and base it on the report you just created. Decrease the height so it's the same as your fields.
5. The link fields should be by SalesPerson
6. Go into the query of your main report and filter off all the Stock orders. You need to do it in a way that you have Stock as a SalesPerson but with Null stock orders.
7. Set some code to hide the subreport if SalesPerson is Not "Stock" and make it visible if it is.

Ah... even Allan posted.

 

[General Ledger]

Guys,

Thanks so much for the great responses. It seems both answers have to do with creating addition fields to sort by but which are not displayed on the report. I really like AliasName: IIF([SalesPerson]="Stock", 2, 1) and I think something I can handle.

I never worked with subreports so that will be learning experience. I look forward to the challenge.

Best regards,

GL

 

[vbaInet]

Just let us know if you need further assistance with this.

 

[General Ledger]

A mircle has occured. I did it!

I created a new field in my query Salesperson Sort: IIF([SalesPerson]="Stock", 2, 1) In my report I added as the new first sort (without headers or footers) Salesperson Sort in ascending order. The next sort levels were the same as before (Salesperson, Customer Name, and Order Number). Now the report has the Salesperson Stock at the end and all the other Salespersons alphabetically. Exactly what I was hoping for.

I do not have prior experience with subreports, as you suggested using. I then realized I could use the solution from my first problem to solve my second problem. I created a new field in my query Product Sort: IIF([SalesPerson]="Stock", [Product], 1) In my report I added as the new third level sort (without headers or footers) Product Sort in ascending order. The sort levels were now Salesperson Sort (to get Stock to show up last), Salesperson (to get alll other Salespersons in alphabetical order), Product Sort (to sort in alphabetical order if Salesperson is Stock), and Customer Name and Order Number as originally designed.

Now within the Salesperson Stock, the records appear alphabetically by Product, then Customer Name and Order Number. If the Salesperson is not Stock, the records for a Salesperson appear alphabetically by Customer Name and Order Number. Exactly what I was hoping for.

Again, thanks to both of you for showing me the way.

GL

 

[vbaInet]

I'm pleased to hear that.

Glad we could help.