3-column sort in a query

[JeanZander]

Hi, I have a fairly large database that I'm using for a report form that I wanted to sort through a query; however it is not working. Although I have 18 columns in my query, only three I want to use for sorting, so I put the columns I wanted sorted first. So the first column heading is Project (which I made ascending) the second is Project Code (also ascending), the third Project # (ascending). I have no other sorting criteria for the remaining columns. It seemed simple enough; however, here is an example of how it is sorting :

ALRT AP 128


ALRT AP 048
ALRT AP 155
ALRT AP 113
BS FTU 5311
COM-S SLC 043
COM-S SAN 010
COM-S Amend:11 019
COM-S MUR 035
COM-S Amend:11 016

Anyway, you get the idea. Any help would be greatly appreciated! Thanks, Jean

[ssanfu]

For a report, the sort order is not set in the query. It is set in "Sorting & Grouping" when in design view of a report.
I have A2000, so I don't know where it is in A2K7.

[JeanZander]

Thanks for responding Now I'm curious, why do querys have a sort category then? Perhaps for sorting in a query for other reasons then a report? In the meantime, I guess I'll look up sorting and grouping.

Wow, Anchorage. I lived in Wasilla for eight years....wish I was there now! Thanks again.

[ssanfu]

One reason is for forms.... If you create a quick query for display without using a form, you can sort it... easier to find data.

Finally warmed up to 25 deg from -5 deg. Only a foot of snow so far

[JeanZander]

Steve,

Maybe you can help me solve another puzzle to my original problem. So I have been experimenting with sorting & grouping using Report Wizard but when I select the fields I want for the report and sorting, there are two where the name changed from Project and Project Code to Expr 1002 and Expr 1003. Why would Access change the names of my fields to an anonomous expression? and then Project # wasn't even listed even though it is part of the Query I'm using for this report....actually maybe it is listed as Expr 1010.

...about Alaska, okay maybe I'm not glad I'm there when its -5 to 25 degrees.

[JeanZander]

Okay, for those who might be interested. Apparently you can select a default sort for your Query, but even though you've selected that same Query to run your report it won't print your report with that sort. The reason I had the words Expr 1002 and Expr 1003 in place of Project and Project Code was I accidentally had them in the Query twice, so as soon as I deleted the extra it worked fine. It's actually quite simple to sort a report once you go to that report and select Group and Sort. I was just wondering if anyone knows why when you go to "select a field" to sort it doesn't list all your fields. It listed Project, Project Code but not Project #....although it listed Entry # just fine. Project # is clearly listed in the Query above. Does anyone have a great answer!

[orange]

Please post the SQL for the query.

[JeanZander]

Orange,

Forgive my ignorance, but is "accdb" access database? I thought this was a forum for Access. My question doesn't include an attachment, but I guess my version of Access would indeed be relevant if that's what you mean. How does one attach in mdb format?

Thanks!

[June7]

Access 2007 can SaveAs mdb format. Access 2003 and earlier (how far?) use mdb format. Access 2007 and up use accdb. MDB and ACCDB are the filename extensions.

[ssanfu]

For the query you are having problems with, change to SQL view, copy the SQL statement and paste it into a post.

BTW, you should only use letters, numbers and the underscore in object names. The "#" sign is bad because it is a date delimiter.

[JeanZander]

Thanks! I think I figured out why couldn't select the field I needed in order to sort my report correctly ~ which was "Project #". Since our Project numbers include numbers, letters and commas, whomever set up the database identified this field, in the design mode, as a memo, rather then a number, or text. Obviously it would be difficult to sort memos. Next time I'll be sure to research more before getting on the forum.

[orange]

Jean,

No that is precisely why you should use the forum. Ask questions, we all have. That's how most of us found this and other forums. Searching for answers/advice/techniques to do something.

When you mentioned your query and the fields involved, I just wanted to see that query in SQL view.

The accdb and mdb, as June has mentioned, are different MS Access database formats. I have found that while some people have 2007 or 2010, there are (it seemed) many more with 2000-2003 versions. The 2000- 2003 can only read mdb format. Whereas 2007 and 2010 can read mdb and accdb.

That line with the accdb and mdb is part of my signature on this forum -- sort of a message independent of the actual post content.

Keep posting, keep asking and keep learning.