Transcript Building MS Access Reports

Creating queries and
reports for list items
 Sample
Data
• CDCR Data Download
 Relational
Database
• Primary Key
• Child Key
• Lookup table
 Concatenate
 [LNAME] & ", " & [FNAME] & " " & [MNAME]
 "C:\Photos\" & [PAROLEE].[CDCNUM] & ".jpg"
 Built-in
and custom formulas
• Date conversions
 WeekdayName(Weekday([LASTCHANGEDATE]))
 MonthName(Month([LASTCHANGEDATE]))
 Year([LASTCHANGEDATE])
• Age calculations
 DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
 INT(([DateOfReport]-[DOB])/365.25)
 Concatenate
 [LNAME] & ", " & [FNAME] & " " & [MNAME]
 "C:\Photos\" & [PAROLEE].[CDCNUM] & ".jpg"
 Built-in
and custom formulas
• Date conversions
 WeekdayName(Weekday([LASTCHANGEDATE]))
 MonthName(Month([LASTCHANGEDATE]))
 Year([LASTCHANGEDATE])
• Age calculations
 DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
 INT(([DateOfReport]-[DOB])/365.25)
 Conditional
formatting
• Highlight specific records
 Sub-Reports
• One-to-many relationships
 Examples: SMT, Vehicles, Addresses, Conditions
• Can grow/shrink
 Back
to SQL statements
• Left([RACE],1)
• IIf([AGE]>=18,'A','J')
• Left([HEIGHT],1) & "'" & Mid([HEIGHT],2,2) & Chr(34)
 Other
Functions
• StrConv([LNAME] & ", " & [FNAME] & " " & [MNAME],3)
 Best
practices for photo storage and
database management
• One photo per record
 Multiple photos per person require a related table
• Thumbnail size version
 How
to add photos in MS Access 2007
and newer versions
 Contact
Information
Matthew Harris
Sonoma County Sheriff's Office
[email protected]
707-565-1628
Conan Mullen
Santa Rosa Police Department
[email protected]
707-543-3696