Access Report>From to Sort non-alphabetical order 1

[mesafloyd]

Hello,

I am not very good at programming so here I am, at your kindness

I have a Table (tblPorpertyLists)
In the table I have 29 fields, but this thread references only to one field... (CATEGORY)

(CATEGORY) contents is limited from a Combobox that has specific CATEGORYs i.e. Apt , Condo , House , Studio, Duplex etc

For printing, I have made a report (reprtRentalGuide2New)
In the report I have made a Header(CATEGORY)

--All is good so far--(looks darn good too! - IMHO ;-)

This Header(CATEGORY) is important to the form.
When I sort for the report to print, the form wizard only allows alphabetical ascending or decending of this CATEGORY. This does work...

however...

What I need is to is, in the form, to list the CATEGORYs by MY fixed order. It is OK to hard code the Apt, Condo, etc in VB or SQL code, because the quantiy and names of the CATEGORY selections will not change except by my hand, (not the users)The users are only allowed to choose one of the selections in CATEGORY

AT this point, sorting is my problem, for instance, rather than sorting in alphabetical sequence, I would like them to sort by my defined order... i.e.

Condo
House
Studio
Apt
Duplex
etc..

How do I do have the header CATEGORY sort by my order?...

I have gotten to the "CATEGORY Header" and selected "Sorting and Grouping" but it only allows me to ascend or decend alphabetically, I don't know how to enter my own selected sort order.

Am I clear???

Might need VBE or SQL for that rather than the Combo box... I don't know.

Let me know if you have any questions.... remember I am not a programmer, but I can do some <very> minor tasks..

I am looking for the simplest and easyist way that my feeble brain can understand...

Thanks in advance.

 

[CautionMP]

mesafloyd,
Easiest answer: Create a table [tt]tblPorpertyCategories[/tt] with two Fields; [tt]Name[/tt] & [tt]SortOrder[/tt].

Use the table as the Rowsource for your ComboBox and join to your report Query so you can sort the Report by [tt]tblPorpertyCategories.SortOrder.[/tt]

The other way is to use [tt]Choose()[/tt] in your query, in the QBE pane you could do this and then once again use this field as the sort order on your report.
[tt]SortOrder: Choose([CATEGORY],"Condo,House,Studio,Apt,Duplex")[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mesafloyd]

Thanks CMP

Regarding solution #2

I am not quite sure where your suggestion goes.

Does it go into the report.. (reprtRentalGuide2New)
and put it into the VB of the design view in the code... starting with Private Sub.. something???

Sorry to be soooooo lame here...
Just a bit more direction please..

 

[CautionMP]

mesafloyd,
No worries and not lame, learning.

Option 2 would go into the query you use for [tt]reprtRentalGuide2New[/tt] as a calculated field. In query design view add the following in a blank column (it's all one line when you copy/paste):
[tt]SortOrder: Switch([CATEGORY]="Condo",1,[CATEGORY]="House",2,[CATEGORY]="Studio",3,[CATEGORY]="Apt",4,[CATEGORY]="Duplex",5)[/tt]

I appologize, orginally told you to use [tt]Choose()[/tt] and not [tt]Switch()[/tt], sorry about that.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mesafloyd]

Hi CautionMP,

Now my ignorance is REALLY going to be exposed.
I tried to understand your directions but I am having problems.. not with MSaccess, but with my ability.

Bigger picture.
This is a rudamentary db but works very well for me, Im at the end and now tweaking in some nuances.
I have only 4 objects
-tblPropertyLists
-frmAllfields
-frmUpdates
-reprtRentalGuideNew2

In frmAllfields in the CATEGORY field have a lookup in Row Source that is:

"Condos";"Houses";"Studios";"Apts";"Duplexes"

I do not have any queries in my DB at all. All searches and lookups are in either VB, SQL or in these property fields.

In the reprtRentalGuideNew2 I have made a CATEGORY Header that works beautifully for the layout and categorizing all the properties.

As you know (I state this only to refresh) my problem is the sorting of the CATEGORY... I cannot sort in my defined order. I can only sort by ascending or decinding ... which works.

In your suggestion you said to go into the query I use for reportRentalGuid2New.... but I don't think there is a query to be found... I am using the lookup? that I have in the frmAllFields as stated above.

Am I making sense?

I love your suggestion if I can just replace the Row Source <above> with a string that could do the job. I was hoping your string did that but when I sat down today trying to do it, I found myself very confused...

Can you help me get to where I am suppose to be...
Thanks

 

[CautionMP]

mesafloyd,
The easiest solution to explain:
[ol][li]Create a query by using the wizard.[/li]
[li]In the Tables/Queries select [tt]Table: tblPropertyLists[/tt][/li]
[li]From Available Fields move everything into Selected Fields.[/li]
[li]Next[/li]
[li]Detail (shows every field of every record)[/li]
[li]Next[/li]
[li]What title do you want... [tt]qryPropertyLists[/tt][/li]
[li]Select Modify the query in design[/li]
[li]Finish[/li]
[li]Move to an empty column in the new query and paste the code from the post on 23 Aug 06 21:57[/li]
[li]Save and close the query[/li]
[li]Now open it and make sure you don't get an error, close it if it's ok[/li]
[li]Open [tt]reprtRentalGuideNew2[/tt] in design view[/li]
[li]Open the properties box for the Report and navigate to Data[/li]
[li]In the Record Source box select [tt]qryPropertyLists[/tt] from the list[/li]
[li]In Sorting/Grouping change [tt]CATEGORY[/tt] to [tt]SortOrder[/tt][/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mesafloyd]

Hi CMP,
Thanks for your patience.
I followed every step except on 16 I have a problem.

There is no selection for "Sorting/Grouping

Under the Data Tab, there is:
Record Souce tblPropertyList (as you stated)
Filter <blank>
Filter On No
Order By <blank>
Order By On Yes

 

[mesafloyd]

Hi CMP
Ignord my last question on the #16.. Sorting/Grouping, I got it, I should get that from the button on the top

I backtracked and I think I blew it on the #10.
Move to an empy column... and paste the code...
Question
there are Rows.. what do I put in the following:
Field: (Should I name this fild 'SortOrder'?)
Table: (should this be tblPropertyLists)
Sort: (Is this where I paste the aug23?)
Show (this is a checkbox... do I check it?)

IB a lame-o

 

[CautionMP]

[tt]Field : SortOrder: Switch([CATEGORY]="Condo", 1, [CATEGORY]="House", 2, [CATEGORY]="Studio", 3, [CATEGORY]="Apt", 4, [CATEGORY]="Duplex", 5)
Table : blank
Sort : Ascending
Show : Check it[/tt]

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mesafloyd]

When I open the reprtRentalGuidNew2, I get a popup...

Enter Parameter Value

SortOrder
_________________________

What do I put in that field?

Also, If i just do OK, I go to reprtRentalGuideNew2 , but it does not have any Category or SortOrder field for indicating.. Apt, or Condo... etc... I used to have a field for that that separated the property records but it is no longer there...

You getting ready to give up on me yet??/

 

[mesafloyd]

Hi Caution,

FOUND IT AND IT WORKS!
I went over it several times, knowing it was me
My call to SortOrder was mispelled....

Sorry to drag you through this...

Thank you very much for bearing with me....

Best Regards,

 

[mesafloyd]

Hello,
I may have been a bit hasty in saying the above solution works... there are a few problems,(probably me) be here it is.

First, I think there is a 'flushing' or 'clearing' that must be done after generating a new query and prior to executing the report (reprtRentalGuide2). Because I have built and deleted several query's on this (testing different scenerios) but it appears an older query <single item> sticks at the top of the list... I cannot make it go away... It is not in the SWITCH lookup in the Query field, but an older one stays in.. I have compacted and repaired the database several times-does not remove the old lookup.

Also, it appears using numbers... 1,2,3,4....9,10,11,12 as the lookup to the switch, has a problem in that after 1 comes 11... I tried using 01,02,03... 09,10,11.. but it appears the 0<zero> has a problem in the lookup... I treid AA BB CC, but that brings up other issues...

Second, And this may be the killer for this "Query" method, I have 19 sorts with two expressions in each sort, I have generated 25 for testing. When I enter the 25 in I get a message "The Expression You Entered is Too Complex"
I find if I lower the count to 10 it will accept the code (But I have other problems.. more on that later...)

Apparently MS ACCESS can only accept 28 expressions max(according to a google I did).

Any suggestions on the above?

I have written an experimental Query for trying to figure this out. It is in the same format as suggested in above threads.

Here it is.
SortOrder: Switch([CATEGORY]="FIRST 1",1,[CATEGORY]="SECOND 2",2,[CATEGORY]="THIRD 3",3,[CATEGORY]="FOURTH 4",4,[CATEGORY]="FIFTH 5",5,[CATEGORY]="SIXTH 6",6,[CATEGORY]="SEVENTH 7",7,[CATEGORY]="EIGTH 8",8,[CATEGORY]="NINTH 9",9,[CATEGORY]="TENTH-testing ASCII - !@ #$ %^ &*()_+ 10",10)

Does my explaination make sense??

I have put allot of time here (I dont mind because I am learning) I am not a programmer, but I can follow someones lead if they make it basic enough..
Thanks to CautionMP for all the above help- very much appreciate you help and patience.
Regards,

 

[CautionMP]

mesafloyd,
Post [tt]23 Aug 06 20:40[/tt] option 1. Create the new table, join it to the query your using for the Report and remove the calculated field entirely (since your report is now based on a query and not a table).

For your sanity I believe this more traditional data structure will be more usable, besides, [tt]Switch()[/tt] has very limited use and IMHO it is a bandaid.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mesafloyd]

Having some problems... I have made the tabe tblPropertyCategories with two fields -Name- (19ea different names) and -SortOrder-(100...200...300.. etc to 1900) (No Hyphens - )

I have two issues in front of me....
1. Rowsource for the Combo Box.
I think your saying I can use the tblPropertyCategories as the Rowsource for the Combobox so I dont have to type in the 19 different names.... In frmAllFields in the CATEGORY Rowsource, I put in tblPropertyCategories.SortOrder I think that is wrong because it is my only choice on the pull down now, I would expect all 19 choices..( I know it's me)

2 How to Join the new table tblPropertyCategories to the query already made...?
In qryPropertyLists I removed the old calculated field previously made - that field is now empty, so the qryReportLists only has the original fields form the main tblPropertyLists. (Is this correct?)

Appreciate your staying with me.... Im still trying to my myself here..

 

[mesafloyd]

Im sinking,

I have made the new table tblPropertyCategories but I just can't figure out how to 'join' it to the query qryPropertyLists ... I have removed the calculated field entry and the only remaing fields are the same fields that are in the tblPropertyLists

I know Im close, but cant get there.

Glub...glub..

 

[CautionMP]

mesafloyd,
I don't know a real good way to explain joins in the query builder, but here goes:
[ol][li]Create a new Query in Design view.[/li]
[li]When the Show Table dialog pops up, click Close.[/li]
[li]In the query toolbar the button next to the Save button should be SQL, click it.[/li]
[li]Paste the following text into the query window:
[tt]SELECT tblPropertyLists.*, tblPorpertyCategories.SortOrder
FROM tblPropertyLists LEFT JOIN tblPorpertyCategories ON tblPropertyLists.CATEGORY = tblPorpertyCategories.Name;
[/tt][/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mesafloyd]

Hi Caution
Sorry for the absence, Im in the middle of a system crash... (why me??;-)

Thanks for the above...

Do I assume I replace the older query with this one???
ie, use the same name as the older one qryPropertyLists ? I did that...

Note there is a mispelling that is perpuating in the table names... tblPORpertyCategories .
I have been correcting all references to read as tblPROpertyCategories.

I dont know if that makes a difference... but it does not seem to do it. Mabe that subtle difference is causing the problem?

I will continue to try to beat this out...

Continued thanks.

 

[mesafloyd]

Hi Caution,
Everything works now, I can't tell you how much I appreciate your guidance....the query you gave me worked fine.. (very complicated for my alleged mind!)

I want to complete this thread in case anyone was tracking the solutions. (I think it is our responsibility to ensure we close issues as to whether or not we were successful)

Just a minor point... In the tblPropertyCategories that sets up the sort order, if you use numbers to set the order Access uses the MSD(Most significant digit) as the priority.. meaning it will sort 90 after 110. I think that is odd because the numbers addigned by Access are in logical order... Oh Well... I used A, B, ...C etc for sort order...

Thanks again, you are Great!!!

 

[CautionMP]

mesafloyd,
Glad to hear you got it working.

if you use numbers to set the order Access uses the MSD(Most significant digit) as the priority.. meaning it will sort 90 after 110

Yes, if your store the numbers in a Text field, if you store them in a number field they will sort correctly.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)