creating a serials database by melissa farley serials associate ingram library, university of west...
TRANSCRIPT
Creating a Serials Database
by Melissa Farley
Serials Associate
Ingram Library, University of West Georgia
The Scope of this Class
Designing and Building a Serials Database using Microsoft Access
No Access Experience? No problem! Use these slides as a guide after taking a class in Access.
Advantages of a Serials Database
Customized Reports All Serials Information in One Database One-Screen Serials Information History of Changes Communication among serials staff Individualized Task Lists Track projects, such as claims and missing
issues list
Definition of Terms Table: Like an Excel spreadsheet—contains one category of
information per table
Field: Contains one specific type of information, like a column in Excel
Record: Information for a specific item, like a row in Excel
Primary Key: Unique identifier for every record
Index: A field that is indexed is quickly searchable. May or may not be unique.
Query: Performs multiple function with raw data in tables, like selecting particular records that meet certain criteria from multiple tables or even updating and deleting information in tables
Form: A more user-friendly way to edit and view data from 1 or more tables or queries
Reports: Printable reports with data from 1 or more tables or queries
Design—Determine needed Content TablesWhat information do you want to track?
My Content TablesAll Holdings ListTitle HistoriesCataloging and Serials CommunicationPhysical HoldingsOnline Access by PlatformCurrent Subscriptions TABL
ES
ALL HOLDINGS LISTIncludes a record for every title
Title HistoriesCataloging and
Serials Communication
Physical Holdings
Online Access by Platform
Current Subscriptions
TABLES
The All Holdings List Table
Title specific information ISSN, E-ISSN, call number, Bib#, OCLC#, and
summary holdings Autonumber primary key 2-cell index: ISSN and secondary code Archived status
One entry for every title—paper, online, or microform
Use current records and Voyager Access ReportsTABLES
The Primary Key—A Unique Identifier for Every Record
Most tables in your database will have a primary key.
The Pitfalls of using the ISSN Multiple titles with 1 ISSN Titles without ISSN
Use the ISSN as 1-field in a 2-field Index Use an Autonumber as your main table’s
primary key
ISSN Code PK
1234-1234
01 546
1234-1234
02 547
9999-3234
01 548
3938-4505
01 549
TABLES
All Holdings List Indexes—Bib Number
Not Primary: Some records have no Bib# (are null).Not Unique: While Bib#’s aren’t repeated, the null values force a “no” TABL
ES
All Holdings List Indexes—ISSN Number
2-field index. ISSNs are duplicated, with different secondary code. 01, 02, etc.Not Primary: Could be the PrimaryKey, but a 2-field primary key causes problems with forms.Unique: Between the 2 fields, all records are unique.
TABLES
All Holdings List Indexes—Primary Key Autonumber
Primary and Unique: Each records is automatically assigned a unique number.TABLES
Preparing Content Tables Online Platforms: JSTOR, Project MUSE, other
owned online subscriptions Subscription Agent: List of active subscriptions Voyager Access Reports: Title history report,
Physical holdings Using Excel, or another spreadsheet program,
create a spreadsheet for each of your downloaded tables (optional)
The importance of Cell Integrity Specificity—One category of information in each
tableTABLES
Cell Integrity
Title
Adweek
American Journal of Psychology
Behavior and Philosophy (frequently late)
Beverage World (cancelled)
Family
PC World (put on display)
Wired
Title
Adweek
American Journal of Psychology
Behavior and Philosophy
Beverage World
Family
PC World
Wired
TABLES
Imported from Subscription Agent
Add fields as needed: Use study fields
AHL-Foreign Key
Source—agent and direct
3-Field Primary Key Multiple Title Entries
from agent
Title changes
Different formats
2 orders
Match from source file on ISSN
Current Subscriptions Fields
TABLES
3-field Primary KeyMultiple online
records possible for each title
Match from source files on ISSN
Populate AHL-Foreign Key field by matching on ISSN
Online Access by Platform Fields—Table Design View
TABLES
1 record per titleHoldings fields data types
should be “Memo” or “Long Text”
Match on Bib# or ISSN from source file
Physical Holdings Fields—Table Design View
Fields Data Type
PK-ISSN Short Text
PK-Secondary
Short Text
Title Short Text
Call Number Number
Paper Holdings
Long Text
Microform Holdings
Long Text
Notes Short Text
Status Short Text
Fund Short Text
Barcode Short Text
AHL-Foreign Key
Number
Holdings Inventoried
Yes/No
Date Last Inventoried
Date/Time
Use Study Number
TABLES
Title Histories Fields—Table Design View
Imported from Voyager Access Reports
Only table linked to All Holdings List by Bib# instead of ISSN
TABLES
Cataloging and Serials Communication TableTable Design View
Create this one from scratch in Access 3-field Primary Key To and From fields with drop down list of
employees
Fields Data Type
Date Created
Date/Time
PK-ISSN Short Text
PK-Secondary
Short Text
Note Short Text
Completed Yes/No
DateCompleted
Date/Time
Go To Shelf Yes/No
AHL-Foreign Key
Number
Email ILL Yes/No
To Short Text
From Short Text
TABLES
Types of QueriesMost Common Queries:
Select Select particular records from particular tables based on particular criteria
Make Table Make a new table, with particular information from other tables
Append Add records to an existing table
Update Change data in particular records in particular fields
Rarely Used:
Crosstab
Delete
Union
Pass-Through
Data-DefinitionQUERI
ES
Select QueriesSpecify which types of records to include from the main table
Combine the data from several tables into one
Eliminate certain records based on desired criteria
QUERIES
All Holdings List Form
Creating the All Holdings List Form
1 main form5 subforms (a form within a
form)Title Histories, Communication, Physical Holdings, Online Holdings, Subscriptions
Forms
Create a query to eliminated “archived” records
Right-click to Add Tables.Click-and-drag desired fields down to the design grid.
QUERIES
Forms
• Click-and-Drag Subform from menu to Design View of All Holdings List Form
• Right-click subform box, select Properties
• Click the “All” Tab• Click the … button on the “Link Master
Fields” line.• Select the matching fields.• Click OK
• Click-and-Drag Subform from menu to Design View of All Holdings List Form
• Right-click subform box, select Properties• Click the “All” Tab• Click the … button on the “Link Master
Fields” line.• Select the matching fields. Notice there
are 3 this time. 1 2-field Index, and 1 Primary/Foreign Key
• Click OKForm
s
Current Subscriptions Report
1. Create a Select Query selecting only desired records.
2. Create Report based on new select query.
REPORTS
Current Subscriptions Report—Query
1. Create a new Select Query.2. Show all relevant tables, choose
desired fields, and drag them down to the design grid.
In my Current Subscriptions table, there are multiple placeholder records, such as membership entries. This query eliminates all of those extraneous records.
QUERIES
Current Subscriptions Report in Design View
1. Use the Report Wizard to create a new report based on the query you just created, selecting desired fields.
2. Edit layout in design view.
REPORTS
Use Study Report
1. Import Use Study Excel file into Access for desired dates.
2. Create a new table using a Make Table Query with Use Counts and Titles.
3. Create a Select Query using newly made table and Physical Holdings Table.
4. Create Report based on new select query.
REPORTS
Use Study Report in Design View1. Use the Report Wizard to create a new report based on the query you just created, selecting desired fields.
2. Edit layout in design view.
REPORTS
What’s Next?1. Take an Access class!
2. Determine your goals for your Serials Database.
3. Gather your data.
4. Sketch out the design of your database.
5. Start creating your database.
6. Get help when you need it: Internet Searches Microsoft Access forums
7. Consider classes in Visual Basic and SQL
Adding a Button to a Form 1. Open Form in Design View2. Under Form Design Tools and
Design, click on the Button Icon3. Click where you want the button.
Forms