order of records

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
I have a basic database for the students in our small school. In my "grade" field, I want to be able to mark the kindergartners with a K and the other students with the number of the grades they are in. But, course, when I want to have the records listed in order from K through 12, K is put at the end of the numbers. Is there any way around this?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
If you make a query, then in that query , an expression where K is the last number, the rest equal 1, 2 etc. then order by that expression, it should be okay.
 
Upvote 0
I'm not familiar with the steps to doing the query and putting in that kind of expression. Could you give me more specific steps? Will this work to let me put my table in a particular order without opening a query?
 
Upvote 0
You should make a grade table, and in the table you should have a sort order field (column) and then join this table in your query. This will enable you to control the sort order and in the query designer if you uncheck the show check box it will not be visible in your result.

hth,
Giacomo
 
Upvote 0
I'm afraid I'm still lost as to the exact steps to follow to implement the help from either of the last 2 posts.
Giacomo, can you walk me through making this table and joining it.
Thanks.
 
Upvote 0
OK starting with the table. Create a table with all your grades like this:

<script type="text/javascript"> function myFunc761405587(myData,myVal) { setTextmyFunc761405587("Info761405587" ,myVal); setTextmyFunc761405587("Data761405587" ,myData); } </SCRIPT> <script type="text/javascript"> function setTextmyFunc761405587(myID,myVal) { if (navigator.appName == 'Microsoft Internet Explorer') { document.all(myID).innerText = myVal; } else{ document.getElementById(myID).textContent = myVal; } } </SCRIPT><STYLE TYPE="text/css"> TD.AccTBInner { BORDER-RIGHT: none; BORDER-TOP: none; BORDER-LEFT: none; BORDER-BOTTOM: none; BACKGROUND-COLOR: #0000FF; FONT-SIZE: 12pt; FONT-FAMILY: times; FONT-STYLE: bold; COLOR: #ffffff;"} </STYLE> <STYLE TYPE="text/css"> TD.AccTBMain { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; BACKGROUND-COLOR: #0000FF; COLOR: #000000; FONT-SIZE: 8pt; FONT-FAMILY: times; "}</STYLE> <STYLE TYPE="text/css"> TD.AccHDRMain { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: none; BORDER-BOTTOM: none; BACKGROUND-COLOR: #d8d8d8; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-FAMILY: arial; "}</STYLE> <STYLE TYPE="text/css"> TD.AccHDRLeftElem { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: none; BACKGROUND-COLOR: #d8d8d8; WIDTH: "2%"; COLOR: #000000; FONT-SIZE: 12pt; FONT-FAMILY: webdings; "}</STYLE> <STYLE TYPE="text/css"> TD.AccDataElem { BORDER-RIGHT: #d4d0c8 0.25pt solid; BORDER-TOP: #000000 0.25pt solid; BORDER-LEFT: #000000 0.25pt solid; BORDER-BOTTOM: #d4d0c8 0.25pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; FONT-FAMILY: Sans-Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left; "}</STYLE> <STYLE TYPE="text/css"> TD.AccIEOnlyMain { BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; COLOR: #000000; FONT-SIZE: 12pt; FONT-FAMILY: arial; TEXT-ALIGN: left; "}</STYLE> <STYLE TYPE="text/css"> TD.AccIEOnlyInnerLeft { BACKGROUND-COLOR: #d8d8d8; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: left; "}</STYLE> <STYLE TYPE="text/css"> TD.AccIEOnlyInnerRight { BACKGROUND-COLOR: #d8d8d8; WIDTH: "10%"; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: Right; "}</STYLE> <STYLE TYPE="text/css"> TD.AccIEOnlyButton { BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #ffffff 0.5pt solid; BORDER-LEFT: #ffffff 0.5pt solid; WIDTH: "2%"; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Webdings; BACKGROUND-COLOR: #d8d8d8; TEXT-ALIGN: center; "}</STYLE> <STYLE TYPE="text/css"> TD.AccIEOnlyData { BORDER-RIGHT: #B0B0B0 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; WIDTH: "10%"; BACKGROUND-COLOR: #ffffff; FONT-SIZE: 9pt; FONT-FAMILY: caption ; TEXT-ALIGN: Left; "}</STYLE> <STYLE TYPE="text/css"> TD.AccInfoBarInnerRight { BACKGROUND-COLOR: #d8d8d8; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: right; "}</STYLE> <STYLE TYPE="text/css"> TD.AccInfoBarData { BORDER-RIGHT: #B0B0B0 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; BACKGROUND-COLOR: #ffffff; FONT-SIZE: 9pt; FONT-FAMILY: caption ; TEXT-ALIGN: left; "}</STYLE><CENTER><TABLE cellSpacing=0 cellPadding=2 align=center width=100%><TR><TD class="AccTBMain" border-left=1px solid colSpan= 4><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD class="AccTBInner" align=Left><FONT color=white>tblGrade : Table </FONT><TD class="AccTBInner" align=right>Access 2000</TD></TR></TBODY></TABLE></TD></TR><TR><TD class="AccHDRLeftElem">
</TD><TD class="AccHDRMain" align=middle ><CENTER>Grade</CENTER></TD><TD class="AccHDRMain" align=middle ><CENTER>sort_order</CENTER></TD><TD class="AccHDRMain" align=middle ><CENTER>Description</CENTER></TD></TR><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">4</TD><TD class="AccDataElem">K</TD><TD class="AccDataElem">0</TD><TD class="AccDataElem">Kindergarten</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">1</TD><TD class="AccDataElem">1</TD><TD class="AccDataElem">First Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">2</TD><TD class="AccDataElem">2</TD><TD class="AccDataElem">Second Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">3</TD><TD class="AccDataElem">3</TD><TD class="AccDataElem">Third Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">4</TD><TD class="AccDataElem">4</TD><TD class="AccDataElem">Fourth Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">5</TD><TD class="AccDataElem">5</TD><TD class="AccDataElem">Fifth Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">6</TD><TD class="AccDataElem">6</TD><TD class="AccDataElem">Sixth Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">7</TD><TD class="AccDataElem">7</TD><TD class="AccDataElem">Seventh Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">8</TD><TD class="AccDataElem">8</TD><TD class="AccDataElem">Eighth Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">9</TD><TD class="AccDataElem">9</TD><TD class="AccDataElem">Ninth Gade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">10</TD><TD class="AccDataElem">10</TD><TD class="AccDataElem">Tenth Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">11</TD><TD class="AccDataElem">11</TD><TD class="AccDataElem">Eleventh Grade</TD></tr><tr><TD ID="currRec761405587"; class="AccHDRLeftElem">
</TD><TD class="AccDataElem">12</TD><TD class="AccDataElem">12</TD><TD class="AccDataElem">Twelfth Grade</TD></tr><TR><TD ID="IE761405587"; class="AccIEOnlyMain" colSpan= 4><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD class="AccIEOnlyInnerRight"> Record: </TD><TD class="AccIEOnlyButton"> 9 </TD><TD class="AccIEOnlyButton"> 3 </TD><TD class="AccIEOnlyData"> 1 </TD><TD class="AccIEOnlyButton"> 4 </TD><TD class="AccIEOnlyButton"> : </TD><TD class="AccIEOnlyInnerLeft"> of 13</TD></TR></TBODY></TABLE></TD></TR><TR><TD class="AccIEOnlyMain" colSpan= 4><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP"><TBODY><TR><TD ID="Data761405587"; class="AccInfoBarInnerRight">tblGrade</TD><TD ID="Info761405587"; class="AccInfoBarData"; width="75%"> Record Count: 13</TD></TR></TBODY></TABLE></TABLE></CENTER><script type="text/javascript">if (navigator.appName != 'Microsoft Internet Explorer') { var a = document.getElementById("currRec761405587"); a.innerHTML = '
'; var b = document.getElementById("IE761405587"); b.textContent = 'Table format has been altered for non-IE browsers '; b.style.fontSize = 10;}</script>


And then when go back into your query and add this table into the query by selecting the "Show Table" button, find tblGrade in the table list and press the "Add" button. Now once you have the tblGrade table in your query select the grade from your existing table and hold it while moving your mouse the the grade field in tblGrade and then let go. If you've done it right there will be a line between the two fields. Now add the sort_order field into your query by dragging it into the query pane. Once it is in your query you can choose to sort on that column, and then as I said you can unselect the "show:" checkbox so that the sort order will not be visible in the query.

hth,
Giacomo
 
Upvote 0
Giacomo,
I've just discovered after 3 1/2 months that I never answered your reply. Thanks for the help.
"Now add the sort_order field into your query by dragging it into the query pane." Do you mean to drag it so that it becomes the second column in the query pane? That is what I did.
"Once it is in your query you can choose to sort on that column." I didn't know what that meant. I closed the query. Then when I tried to open it again, I get a warning that says. "The specified field '[Grade]' could refer to more than one table listed in the FROM clause of your SQL statement."
I really don't know how to use queries with tables. You've tried to help, but I'm just low on understanding.
 
Upvote 0
My responses are below in Red

Giacomo,
I've just discovered after 3 1/2 months that I never answered your reply. Thanks for the help.

-- No Problem


"Now add the sort_order field into your query by dragging it into the query pane." Do you mean to drag it so that it becomes the second column in the query pane? That is what I did.

-- That's correct


"Once it is in your query you can choose to sort on that column." I didn't know what that meant. I closed the query.

-- In the query pane the third row down is sort. Choose "ascending" under your sort_order field that you added above

Then when I tried to open it again, I get a warning that says. "The specified field '[Grade]' could refer to more than one table listed in the FROM clause of your SQL statement."

-- That means you probably used [Grade] as a criteria for your query and since you now have 2 table that both have a field named grade Access is getting confused. The solution is to specify which table it needs to comde from like this: [myTableName]![Grade]

I really don't know how to use queries with tables. You've tried to help, but I'm just low on understanding.

-- Well this is a great place to come to for help. However, without an understanding of the basics you're probably better of with a book or a class.

hth, Giacomo
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top