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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Marbles

Well-known Member
Joined
Feb 7, 2005
Messages
622
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.
 

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
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?
 

Marbles

Well-known Member
Joined
Feb 7, 2005
Messages
622
In a Query, the expression, IIF([Grade] ="K", 0,[Grade]) should sort ok.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

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
 

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
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.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

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
 

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
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.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

Forum statistics

Threads
1,136,586
Messages
5,676,671
Members
419,642
Latest member
Hyakkivn

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
Top