tblLibrarians: The people who work in the corporate library and will be answering the reference questions. This includes personal information such as birthday and address, and status information such as title, part-time/full-time and date of hire. The primary key is a string combining the first and last name of the librarian. Parent table to tblDailyRef.
tblEmployees: The people at the corporation who are asking the reference questions. While the library (and librarians) are all located in the Buffalo, New York area, the employees could be from any of the company’s worldwide branches. (In this case, they would be asking their reference questions by phone or e-mail.) Information included in the table would be similar to that of Librarians, although this table would include department information (since they could be from different departments), as well as the branch office they are located in. The primary key is a string combining the first and last name of the employee. Parent table to tblDailyRef. Child table to tblBranches.
tblDailyRef: This table would be used by the library staff for logging reference requests. Information included would be the librarian answering the question, the employee asking the question, date, time spent, where the information was found, etc. Time spent on the request would be written as a decimal, ie 1.5 for an hour and a half, or .25 for fifteen minutes. The source where the information was found could include: the library’s physical collection, the internet, or specific online databases. The primary key would be a combination of the date the question was answered, an an incremental number. Child table to tblLibrarians and tblEmployees. Daily Transaction Table.
tblRefRequests: This would be the master table that tblDailyRef is merged with on a daily basis. The primary key would be a combination of the date and the incremental number.
tblBranches: Location and contact information for each of the company’s branch offices. The primary key is the city where the office is located. Parent table to tblEmployees.
qryallbydate: A parameter query that returns all reference transactions from tblAllRef from an entered date.
qrysearchbranch: A parameter query that returns all reference transactions from tblAllRef from an entered branch name.
qrysearchlibrarians: A parameter query that returns all reference transactions from tblAllRef from an entered librarian's name (or part of a name).
qrytblallref: Sorts the records in tblAllRef by the date answered, and then by number. Used as the basis for frmAllRef.
qrytotaltimebydept: Displays the number of requests made by each department and the time spent on them. The records come from tblAllRef.
querytotaltimebybranch: Displays the number of requests made by each branch office, and the time spent on them. The records come from tblAllRef.
qryupdate: Used at the end of the day to add all records from tblDailyRef to tblAllRef.
qryclear: Used at the end of the day, after qryupdate, to delete all records from tblDailyRef. Some Visual Basic programming prevents this query from being run until qryupdate has been run, to prevent loss of information.
frmMenu: The main menu screen. Buttons lead to frmRef, frmTotal, frmEndofday, frmCompany, and frmSearch.
frmRef: For functions dealing with reference requests. Buttons lead to frmDailyRef, frmAllRef, and frmEndofday.
frmDailyRef: Used for logging or viewing today's reference requests. Data source is tblDailyRef.
frmAllRef: For viewing all reference requests since the database began. Data source is qrytblallref.
frmTotal: Showing totals and summaries of reference transactions. Buttons lead to frmqrytotaltimebydept, frmqrytotaltimebybranch rptlibrarians, rptqryallbydate.
frmqrytotaltimebydept: Data source is qrytotaltimebydept.
frmqrytotaltimebybranch: Data source is qrytotaltimebybranch.
frmEndofday: Used for end of day maintenance. Buttons lead to qryupdate and qryclear. Some Visual Basic programming prevents qryclear from being run until qryupdate has been run.
frmCompany: Showing company information. Buttons lead to frmBranches and frmLibrarians.
frmBranches: Shows information about each branch from tblBranches, with a subform showing information about the employees at each branch, from tblEmployees.
frmLibrarians: Used to view, update, add or delete information about the librarians. Data source is tblLibrarians.
frmSearch: Used to search reference requests. Buttons lead to frmqrysearchlibrarian and frmqrysearchbranch.
frmqrysearchlibrarian: Data source is qrysearchlibrarian.
frmqrysearchbranch: Data source is frmqrysearchbranch.
rptlibrarians: Shows information about each librarian from tblLibrarians, with a subreport showing each reference request they have answered, from tblAllRef.
rptqryallbyday: Data source is qryallbydate.