Multiple sorts on one group (1 Viewer)

[tabitha]

I have a union query of the Employees table and Dependents table on [Employee ID] which I build for a census report, and when I run the query, it sorts correctly, but it's not sorted at all on the report. I tried to add a sort on the group, but it just ignores it.

I need it grouped by [Employee ID], then sorted by [Last Name], then [Relationship], then [Age], so it will be alphabetical by the employees last name, then put the employee first, then the dependents by oldest to youngest. Is there a way to do this in the code, and where would I put that?

 

[pbaldy]

Sorting for a report needs to be set up in the report. In design view, click on the Group & Sort icon in the ribbon.

 

[Ranman256]

Every dependent must have the EMP employeeID shown too.
If you group by EmpID 1st, then the names won't sort.
Seems the sort would be
Last
EmpID
Relationship
Age

Tho I don't see what sorting relationship does. And age will mean nothing here.

 

[tabitha]

Both union tables have and [Employee ID] field, it's what links them together. I have the grouping first because sometimes a dependent will have a different last name, and if I sort by name first, they won't be together. I want the Relationship desc so the Subscriber would be first, then Spouse, then Dependent, then Age was included so the oldest child would be first, to youngest.

The grouping works successfully right now by [Employee ID], but when I add a sort, even if it's just the last name, it does nothing.

 

[tabitha]

Man I'm still stumped by this. Everything I'm reading online just says "Use Group, Sort, and Total ribbon at the bottom" and while this works for one level, it just fails beyond that. I can group by [Employee ID] and do no other grouping or sorting; I can group by [Last] and do nothing else; I can sort by [Last] and do nothing else; I can sort by [Age] and do nothing else.. but I cannot group by [Employee ID], then sort by [Last]. That's all I really have to have, we can just look at it to see who is the employee, though it'd be nice to have them at the top. What am I missing??

 

[pbaldy]

You should be able to sort on more than one level. Can you post the db here, or a representative sample?

 

[tabitha]

How would I post a sample?

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[tabitha]

I've made a copy of the database and deleted all the personal information with just one test company and three employees with their dependents. If you double click on the Census Union form, then type "ABC" and click the button, it will run the report. You'll see if you go into Design View, that there is in fact a Sort By Last after the Group by Employee ID, but the names are just listed in the order that they were created.

Let me know if you need any other explanation.

 

[pbaldy]

I'm not sure what you're seeing that I'm not. The last names are in order. If I add a sort on first name, the names then sort within the same last name. What specifically are you expecting to see different?

 

[tabitha]

They aren't alphabetical for me? I'm not sure what you're seeing, but when I look at this picture, they aren't in order. It should go Adams, Smith, Wilson.

 

[pbaldy]

The primary sort is the group by, so Employee ID 1 is Smith, 2 is Wilson, 3 is Adams.

 

[tabitha]

So how do I override that? Can I change the primary sort?

 

[pbaldy]

You want the report sorted by last name, but with employee ID's kept together? So if Joe Smith's wife kept her maiden name and it was just Jane Burns, she would still show up with the Smith's, not up after the Adams? First thought that comes to mind is adding a field to both parts of the UNION query:

, Employees.[Employee Last Name] & Employees.[Employee ID] as SortField

and use that as the primary sort. I concatenated name and ID to handle you having more than one employee with the same last name.

 

[tabitha]

Genius!! I never thought to add an entirely new field to just make what I wanted, and it worked just like I need it to. Live and learn! Thanks a bunch!

 

[pbaldy]

Happy to help!