Candy Floss
Download Sample Database (Access 2000 format)
Full Instructions (Word Document)
Download Sample Database (Access 2000 format)
Example - 2 forms (splashscreen and menu) and 2 macros (autoexec loads splash and menu opens "switchboard" once splash has finished.) The form splash has following properties set On_Timer- run macro menu (which closes switchboard and opens menu) and Timer_Interval = 3000 (3 secs).
Download Sample Database (Access 2000 format)
Password system. Based on a school system for recording estimated exam grades. Members of each department can view/edit details for students in their subject only but system admin (or say senior staff) can view all records. Could be used in loads of contexts where only certain people are allowed to view certain material. How it works:
1. Two tables - tblStudents has the data to be viewed/updated. tblUsers contains data about users of the system - user ID, name, dept, password
2. Three forms - frmLogin (main part of candyfloss), frmEstimatedGrades (based on query below), frmEstimatedGradesAdmin (based on query below)
3. Two queries - qryEstimatedGrades (displays results based on whatever happens to be in Department field of frmEstimatedGrades when button on this form is clicked), qryEstimatedGradesAdmin shows all records on frm
4. Two (3?) macros - mcrLogin is the big one and is attached to the Login button on frmLogin. Look at the conditions in the design window to see how it works (best done via the form). Basically - check if user id is right, password is right and if user is "admin" - if yes open admin form if no check if user id is right and password is right and go to dept form (based on qry in 3 above), otherwise refuse access. Other macro(s) just close down login form when login is complete.
Combo on frmLogin done with a wizard. It just jumps to record selected. UserID is and Password from orginal table are *hidden* i.e. column width=0 and **values** set with visible properties=no) and then compared with user entry.
Download Sample Database (Access 2000 format)
Table contains student names and "raw" test scores (test is marked out of 120). Two calculated fields in query convert score to % and then assign a grade based on uniform mark scheme (80%+=A, 70%+=B etc). Could use the same principle for looking at whether sales people meet targets by adding an extra field to the table - target, and then using this in the Iif function.
Download Sample Database (Access '2000 format)
Calculates grades based on student scores in exams/tests. Student can have two attempts at a test (like current AS/A2 rules) and different subjects can have different grade boundaries (not like current AS/A2 rules). A nice example of nested IF functions - the query reveals all. Thanks to ThË ®ªtT|ë §ñåkë for the idea.
Download Sample Database (Access 2000 format)
Based on the idea that a sports club can categorise members by age and then select teams from resulting queries i.e. only those members under 16 could play for the under 16s team. Table contains member name and date of birth. Query calculates age from DOB and then "puts" member into relevant category. Not perfect though - members can end up changing category halfway through the year!
The problem in Age CAN be resolved fairly easily - use a "cut off" date in the Age field instead of Now() i.e. age at a certain date. Even this isn't a perfect solution. Explain this in evaluation/testing.
Download Sample Database (zip file containing Access 2000 Database and Word Document)
Three forms New rental - form done with a wizard based
on qryLoans. Has an OnOpen macro that goes to a new
record. Clicking the Rent! button runs a macro that changes
status of video from "in stock" to "out of stock"
Return video - copy of new rental but based on qryVideoOut.
Clicking return runs a macro that changes status of video
from "out of stock" to "in stock".
Combo box done with a wizard (find a record in form option)
Stock - based on Unique table. Two subforms based on VideoIn and VideoOut queries. Each subform has an unbound field in the footer to add
up number of copies (you don't actually need any fields in
the subform but I left them in). Subforms are both linked
to main form via VideoID.
The VideoIn and VideoOut queries both contain an extra field not in the tables
- just inserts a value of 1 so you can count numbers in or out of stock
(on the subforms).
It needs improving for referential integrity and avoiding duplicates but it's
a start.
Download Sample Database (Access 2000 format)
System for reporting faults with computer equipment in a school (though it could be adapted for any organisation). Tables only. Did this as a result of a plea for help via FatMax. Useful example of relations between entities - tblFaultReport is a good example of using a 'junction' table to resolve many-to-many relationships.
Download Sample Database (Access 2000 format)
"Classic" four table database - can be applied to almost any transaction system (mail order, takeaway delivery, sales etc.). tblOrderDetails is a good example of a composite key. Also check out the calculated field in qryOrderDetails.
Limiting the contents of one
combo box on a form based on the contents of another
Download Sample Database (Access 97 format with explanatory Word Document)
The Word Document reveals all!
Download Sample Database (Access 2000 format)
Any booking system could use this - the example is for a coach outings company. If there are places left on coach a booking can be made otherwise message appears saying coach is full. Doesn't link into anything else just shows how a macro can be used to change number in a field using SetValue and how conditions are used to determine which parts of a macro run. Look at the OnClick macro on the form button.
Download Sample Database (Access 97 format)
First letter of first name and last name is capitalised regardless of whether they're entered in upper or lower case - input masks are in tblNames. Full Name on the form shows how to *combine* data from two or more fields using unbound controls and &.
Thanks to Jo for this Candy Floss!
How to include a logo on all your forms and reports without ending up with a huge database file.
Download Sample Database (Access '97 format)
Can anyone find a use for this? The button doesn't do anything but clicking anywhere else on the form brings up an easily customisable message box.
Download Sample Database (Excel 2000 format)
An Excel Candy Floss. Calculates NC levels from test scores. A good example of VLOOKUP.
Download Sample Database (Access '97 format)
Multiple choice quiz that could be adapted to be used for company training, driving school, etc.
Download Sample Spreadsheet (zip file)
Zip file includes notes on how the check digit is calculated plus a sample Excel file. Thanks to Martin Smith of Highsted School.
Download Sample Database (zip file)
This is a non-relational example of technique rather than database design. Uses an append query to create daily electronic class registers for teachers in a college/school. Word document explains how the database works.
Cascading Combo Boxes in Excel
Download Sample Spreadsheet (zip file)
Download Sample Database and Instructions (zip file)
This removes prefixes from video/CD/Mp3 track names so they sort in a more logical order. Could be improved easily by adding another expression to move common prefixes to the end of the name e.g. The Addams Family becomes Addams Family,The.
http://web.zdnet.com/pcmag/pctech/content/solutions/dbindex.htm (external link)
Solutions to some interesting problems.