Help with a sort column

This is based on something you helped with previously Ray, so might be something I asked at the time, and it couldn't really be done. In which case, no problem - half the issue is down to the site having incomplete data that complicates the whole thing.

I've attached the page below.

I went in and updated the live file. You can't sort on a renamed column with AS, so I updated the sort references to use the original column name and that seems to have fixed it.

Thanks Ray - that's what I had and also thought it was OK, but then I noticed it wasn't.

If you sort by country so that its Z-A and the first ones are Zimbabwe, if you scroll down a bit you'll see a red 'Not Selected' one,amd a few rows further down a Germany and an Australia amongst the United States ones.

I think it's because its sorting on Activity which by coincidence a lot will have Country, but its presumably not actually sorting on Country.

Due to the history of the structure, its that weird one where Countries are a category of Activities. So although Countries are in the lodge_activities table, so are lots of things other than countries.

Thanks for having a look - really they need to enter a country for all these, then this becomes less of an issue.

If there isn't a consistent field or way to identify the country then you wouldn't be able to sort by it consistently. Is there an activity type or something of that nature that would allow you to identify if it is a country or not? It might not be possible given your description.

Yup - there's a Category_ID field. For these lists, once everyone has their country added, I think I can use a join in the main recordset to pull through the country. Going down that route I couldn't get it to include the cases where no country was added to the lodge_activities table. (That table should really just be called 'activities', or better still something like 'keywords', but it was originally supposed to be for activities at lodges, and its kind of grown from there, hence the slightly unintuitive lay names tables).

You originally helped out with the recorder within a recordset approach for lists of lodges which included two different activity categories, so couldn't both be included in the main recordset.

If you use a LEFT OUTER JOIN you will be able to use the field when it has a value. That is probably the best solution for now and it will work as long as people have set the country without excluding those that haven't.

HI Ray - just looking at this again today. Couldn't really get it to do what I wanted, probably because of the data structure, and the way Country is part of the profile thing with other stuff. fortunately the County is also stored for most Agent companies in that flat table, so just using that instead. Means that Country will needed to be added there and as part of the keyword profile thing, but it will save a lot of bother.

I have spotted a weird thing to do with records not adding adding up in a couple of recordset - have posted separately about that rather than add it here.