Interestingly enough, the bit of SQL code that Kevork quoted here is an appropriate and universal way to design and build a database query. It works with OpenOffice Base as well as Access and Mark can confirm that it can work with FileMaker Pro.
<br><br>Whilst we can use a drag and drop interface to build these bits of code, it is certainly worth showing your students the underlying SQL query that is being built up.<br><br>Regards Roland<br><br><div><span class="gmail_quote">
On 14/03/07, <b class="gmail_sendername">Kent Beveridge</b> <<a href="mailto:kbeveridge@stbc.vic.edu.au">kbeveridge@stbc.vic.edu.au</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi all,<br>Obviously, there are many of you guys and gals out there who have great skills with database queries, validation techniques etc, so...<br>how about if a 'database of subroutines', using for example, some of the query routines just posted in answer to other's questions, could be created.
<br>Would this be in breach of any rules Kevork? This way, we could allow those who use 'non-proprietary' database programs(non-Access!) as well as Access to be able to access some quick solutions that we are coming across. After all, professional programmers store a database of subroutines and call them up then modify them to suit the project or application.
<br>Surely this is encouraging good practice whilst assisting others at the same time. What do you think folks?<br><br>This could also apply to some of the other software based applications we need to teach our students during the year for the other outcomes too. Hopefully, this does not breach any VCAA rules.
<br>This does not need to be subject specific.<br><br>Kent Beveridge,<br>I.T. to years 8-12<br>St. Brigids Catholic Sec. College<br>Horsham<br><br>"This email and any attachments may be confidential. You must not disclose or use the information in this email if you are not the intended recipient. If you have received this email in error, please notify us immediately and delete the email and all copies. The School does not guarantee that this email is virus or error free. The attached files are provided and my only be used on the basis that the user assumes all responsibility for any loss, damage or consequence resulting directly or indirectly from the use of the attached files, whether caused by the negligence of the sender or not. The content and opinions in this email are not necessarily those of the School."
<br><br>________________________________<br><br>From: <a href="mailto:itapps-bounces@edulists.com.au">itapps-bounces@edulists.com.au</a> on behalf of Kevork Krozian<br>Sent: Tue 13/03/2007 10:26 PM<br>To: Year 12 IT Applications Teachers' Mailing List
<br>Subject: Re: [Year 12 IT Apps] Access question<br><br><br>Hi Mark,<br><br> A table ( Table1) with 4 fields ( ID, name, surname, suburb and postcode ).<br>We want to count how many times a suburb occurs and order from highest to lowest.
<br><br>Create a new query, Add the table to the grid, click on View , SQL view, paste in the following:<br><br>SELECT Table1.suburb, Count(*) AS num_suburbs<br>FROM Table1<br>GROUP BY Table1.suburb<br>ORDER BY Count(*) DESC;
<br><br>Run and the output is attached as well as screen grabs of the table and the query grid in design view.<br><br>Desc stands for descending, so order by most numerous to least numerous.<br>Hope this helps<br><br><br>
Kevork Krozian<br>Mailing List Creator and Administrator<br><a href="mailto:kevork@edulists.com.au">kevork@edulists.com.au</a><br><a href="http://www.edulists.com.au">www.edulists.com.au</a> <<a href="http://www.edulists.com.au/">
http://www.edulists.com.au/</a>><br>Tel: 0419 356 034<br><br> ----- Original Message -----<br> From: Jones, Mark A <mailto:<a href="mailto:jones.mark.a@edumail.vic.gov.au">jones.mark.a@edumail.vic.gov.au
</a>><br> To: <a href="mailto:itapps@edulists.com.au">itapps@edulists.com.au</a><br> Sent: Tuesday, March 13, 2007 2:14 PM<br> Subject: [Year 12 IT Apps] Access question<br><br> Dear all<br>
<br> I'm looking for a function (for a query or report) that will search a list of names then provide the name of the most numerous one. Any ideas would be appreciated.<br><br> regards<br> Mark Jones
<br> Cann River P-12 College<br><br><br><br> Important - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education.
<br><br> _______________________________________________<br> <a href="http://www.edulists.com.au">http://www.edulists.com.au</a> - FAQ, resources, subscribe, unsubscribe<br> IT Applications Mailing List kindly supported by
<br> <a href="http://www.vcaa.vic.edu.au">http://www.vcaa.vic.edu.au</a> - Victorian Curriculum and Assessment Authority and<br> <a href="http://www.vitta.org.au">http://www.vitta.org.au</a> - VITTA Victorian Information Technology Teachers Association Inc
<br><br>_______________________________________________<br><a href="http://www.edulists.com.au">http://www.edulists.com.au</a> <<a href="http://www.edulists.com.au/">http://www.edulists.com.au/</a>> - FAQ, resources, subscribe, unsubscribe
<br>IT Applications Mailing List kindly supported by<br><a href="http://www.vcaa.vic.edu.au">http://www.vcaa.vic.edu.au</a> <<a href="http://www.vcaa.vic.edu.au/">http://www.vcaa.vic.edu.au/</a>> - Victorian Curriculum and Assessment Authority and
<br><a href="http://www.vitta.org.au">http://www.vitta.org.au</a> <<a href="http://www.vitta.org.au/">http://www.vitta.org.au/</a>> - VITTA Victorian Information Technology Teachers Association Inc<br>_______________________________________________
<br><a href="http://www.edulists.com.au">http://www.edulists.com.au</a> - FAQ, resources, subscribe, unsubscribe<br>IT Applications Mailing List kindly supported by<br><a href="http://www.vcaa.vic.edu.au">http://www.vcaa.vic.edu.au
</a> - Victorian Curriculum and Assessment Authority and<br><a href="http://www.vitta.org.au">http://www.vitta.org.au</a> - VITTA Victorian Information Technology Teachers Association Inc<br></blockquote></div><br><br clear="all">
<br>-- <br>Roland Gesthuizen - ICT Coordinator - Westall Secondary College<br><a href="http://www.westallsc.vic.edu.au">http://www.westallsc.vic.edu.au</a><br><br>"Never doubt that a small group of thoughtful, committed citizens can change the world; indeed it is the only thing that ever has." --Margaret Mead