Order by - Forcing an un-natural sort

[CSADataCruncher]

I'm trying to help my husband duplicate a report that he has created in Excel. The report shows the following fields... "Company", "TitleGroup", "Team", "Year", "Title", "Name". I have another field that he has created called "YrSort" that isn't a visual part of the report but it represents exactly how he wants the information sorted.

However, the Report needs to be grouped by year and company. Our problem comes in that when grouping by company, Access automatically puts the companies in alphabetical order but we don't want them in alphabetical order. In other words, we want the company "Redskin Authentics" to display below the company "Wild Card". Is there a way to have Access group things one way but use another method to sort the groups?

Even within the groups, items are not being sorted correctly... we have some "Title Groups" under the "Companies" that have a numeric beginning and, since Access is sorting it as text, it starts with 10 and then puts 100 between 10 and 5.

This is all so confusing and I don't know if I've been able to explain the problem correctly. If you are patient with me, I could obviously use your expert advice.

Thank you in advance,
Peggy Neubert

 

[BoxHead]

Peggy,

I've found myself in situations where I want to sort 'out-of-order' and I've ended up adding a column to my table and calling it mySort.

If you imported the data from Excel you may already have the table data in the order you'd like and could just add the column, datatype number, and when you open the table and type 1 in the first row and 2 in the second, you should be able to hold your finger down on the down arrow and Access will fill in the rest of the numbers for you. It's better than autonumer I think because you can rearrange the numbers as you like.

Add that field to your query and report (it can be invisible in the report) and use it for your sorting.

When you have numbers at the beginning of text fields, you can use Val(textField) to pull the numeric value out of the beginning and sort by that if that is still an issue.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[MajP]

I would do what boxhead said for the unnatural sort. To add to what he said. The function Val(123Group) returns 123,and val(noLeadingNumbers) returns 0. You may want to use the below function in the calculated field of the query, where "field1" is your Group Name field. The only thing this adds to what was said is it assigns a large number to Groups that do not have leading values instead of a 0. In the case where you want to put all the Groups with leading numbers up front then then groups with non leading numbers.

ldgSort: IIf(Val([field1])=0,999999999,Val([field1]))

Then sort by "ldgSort", then by Group Name.

Group 1
20 Group
2 Group
Group 2

becomes

2 Group
20 Group
Group 1
Group 2

If you want the Groups with leading values after the nonnumeric leading values then just do what Boxhead said.

ldgSort: val([field1])

This will give
Group 1
Group 2
2 Group
20 Group

Neither of these solutions will account for the trailing values.
Group 20
Group 2

It could be done but requires a different function.

 

[CSADataCruncher]

Thank you both for your input. I'll try both of these because it actually addresses both issues. However, John, we did already add a numeric field at the beginning of his Excel files. He made put the files in order the way he wanted them and then gave them a huge numeric number like 2002.00165839. This field is called "YrSort".

But the problem I've been having is the fact that when I group by the "Company", Access ignores the YrSort and if I tried putting YrSort into my grouping and sorting features than each line is an individual record on my report with a title and line above it (not at all what we want).

However, I will still try what you've suggested... sounds like we had the right idea but I'm not doing something right in the creation of my report.

Peggy

 

[MajP]

It sounds like you want to Group by year, then by company, then the deails are the sorted by Title Group. In your sorting and grouping First select Year Sort and under Section Header choose Yes. Put your Year information in this first Header. Then Select Group by Company "Forced Sort Value" and choose to add a Header. Put company information in this header. Then Select sort by ldgSort (or whatever you call it) then sort by Team Name.
Okay I see what your problem is now that I reread it. You have very unnormalized data. You gave every record a sort value. You only need to give the company names sort values. Lets see you have 100 records and only ten companies. You should give each company a sort value one time. Company A would get 1 every time it appears in the table, company b would get 2, etc. You could do this quickly with a vlookup. Now sort and group by year and then the Company sort value.
If your data was normalized this would make more sense.

Year 2004
company A (sort value 1)
company b (sort value 2)
etc.
Year 2005
company A (sort value 1)
company D (sort value 4)
Year 2006
company b (sort value 2)
company D

 

[CSADataCruncher]

Thank you! lol That's what I orignally told my husband to do, but he did it his own way anyway and now I'm expected to make it work.

Yes, I am already sorting/grouping by year (forcing new page) but I have the year information in the page header so that it will repeat at the top of each page.

Then I have grouping on the company, so the company name will repeat at the top of each column and we added the YrSort to the grouping and sorting so that it would sort, but with no group heading or footer.

When I move the YrSort above TitleGroup in my grouping and sorting, it adds space after each line. But when I remove TitleGroup from my grouping and sorting all together, it still alphabetizes by Company (which I don't want) and does not put a space in between the TitleGroups at all.

I don't know if you understand my prattle... it doesn't even make sense to me now that I read it.

But I have tried moving the YrSort around to different locations and by moving it above the TitleGroup it does sort the 5, 10, 100, 500 titles correctly. So, I guess my only problem now is getting the Companies to sort by the YrSort rather than alphabetically.

I'll see if I can convince my husband to go back to a Company Sort vs a Year Sort column... it would make things so much easier.

Thank you again.
Peggy

 

[BoxHead]

Peggy,

You could create the 'normal' table with a make table query.

Use a totals query selecting first of YrSort and grouping on Customer name. Make the field names in the new table mySort and cName. Then add this table to your query for the report, join it on Company Name and ad the mySort field.

That should give you one sort value for each company name.

You could do the same thing with a DMin statement but I think that would slow things down quite a bit.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[CSADataCruncher]

OK... Yay! Part of it's working but for some reason we can't get both parts working at once. I think I need to try that

ldgSort: IIf(Val([field1])=0,999999999,Val([field1]))

Code that was mentioned earlier because I created the Sorting table with the MySort and CName fields and linked it in my query. Then I changed the sorting and grouping to MySort rather than Company but used the Company fields for the names to display on the report.

Now, I'm back to square one on the YrSort as far as getting the 5 Stripe, 10 Stripe, 20 Stripe, 50 Stripe, 100 Stripe, and 1000 Stripe titles to sort correctly without leaving a space in between each line.

Here is how I have the sorting working:

Year (Group Header) and force new page after section.
MySort (Group Header and footer) "Company" displays here.
YrSort
TitleGroup (Header)

by putting the YrSort above the TitleGroup, I get the sorting correctly but it leaves a line between each row because I was trying to keep the TitleGroups together.

This is what I'm trying to accomplish:

Pro Line Portraits (this is bold w/underline)
Base 18 Vinnie Clark
Base 34 Mark Murphy

Autographs 46 Vinnie Clark
Autographs 114 Liny Infante

National Convention 18 Vinnie Clark
National Convention 34 Tony Mandarich

Wild Card Draft (again this would be the company name which is bold and underline)
5 Stripe 21 Don Davey
5 Stripe 34 Esera Tuaolo

10 Stripe 21 Don Davey
10 Stripe 34 Esera Tuaolo

20 Stripe 21 Don Davey
20 Stripe 34 Esera Tuaolo

Packers Super Bowl II (Bold & Underlined)
Packers Super Bowl II 1 Intro Card
Packers Super Bowl II 2 Steve Wright


So, the bold & Underlined ones are what we have listed as "Company" and as you can see Wild Card comes between Quarter Back and Packers Super Bowl. Then the "TitleGroup" would be the Base, Autographs, 10 Stripe, etc. The main reason for the YrSort is to keep the Card numbers sorting correctly and to keep the TitleGroup sorting correctly.

When I add YrSort to above TitleGroup, it adds the lines between each row, but when I put it after TitleGroup, it doesn't seem to matter because Access sorts on the TitleGroup.

Peggy

 

[CSADataCruncher]

OK... now I tried adding another table for sorting (like the one you had me create with the Company sort) for the TitleGroup.

Now, everything is sorting correctly except I have a new problem... now it's no longer putting the Company names at the top of my columns.

Any suggestions?

Peggy