Hi Peter,
Yes, you should base your form on a query instead of just a single form. The
query should include the appropriate ORDER
BY statement in it's SQL (Structured Query Language) view. This will be done
automatically for you, if you use the QBE
(Query By Example) grid in query design view, and you specify your desired
sort order(s) from left to right.
If you are new to SQL, and curious to see how the QBE helps build the SQL
statement for you, then in query design mode
you can switch back and forth between the normal QBE view and the SQL view.
To do this, click on View > SQL View to view
the SQL window, or View > Design View to return to the familiar QBE. As you
make changes in the QBE, you will see changes
made to the underlying SQL statement. Including an ascending or descending
sort will automatically add an ORDER BY clause
to the underlying SQL statement.
You should spend some time gaining an understanding of database design and
normalization before attempting to build
something in Access (or any RDBMS software for that matter). Here are some
links to get you started. Don't underestimate
the importance of gaining a good understanding of database design. Brew a
good pot of tea or coffee and enjoy reading!
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the
relationships between the various tables (Tools > Relationships...)
It's also a good idea to adopt a consistent naming convention, and to avoid
the use of reserved words. Here are some more
links for you:
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266
Tom
__________________________________
:
Dear Tom,
Thank you for your reply.
In other words, I should create a query ORDER BY ClassID
instead of using the Student Table ? I have to finish it
first and I will look into the relationship.
Thank again
__________________________________
Hi Peter,
There is a Student Table with StudentID as the primary
key. It also contains ClassID indicates which Class he /
she belongs to.
This does not appear to be a proper design. If ClassID is a foreign key to a
Classes table, you would have a one-to-many (1:M) relationship, ie:
"A class can have many students" and "A student can be enrolled in only
one class" (without repeating the student's name for each additional class).
The ClassID field should be in the classes table as a primary key. A third
join or linking table would contain the two foreign keys: StudentID and
ClassID. This is how you define a many-to-many (M:N) relationship, by joining
two 1:M relationships with a linking table. The M:N relationship can be
stated as follows:
"A class can have many students" and "A student can enroll in many classes".
I suspect that this is what you really want to achieve.
Once you address the design issues, try basing you sorts on a query, which
can be used as the recordsource for your form.
Tom
__________________________________
:
There is a Student Table with StudentID as the primary
key. It also contains ClassID indicates which Class he /
she belongs to.
I create a form and would like the records shown to be
sorted by the ClassID instead of the primary key StudentID.
I have attempted to add ClassID in the "Order By" item of
the form and set to "ClassID" but it doesn't work.
Then I go to the Student Table and click property,
add "ClassID" in the "Order By" in the "Table Properties"
but the form is still sorted by StudentID.
Is there any suggestion ?
