creating queries and reports for list items. sample data cdcr data download relational database...
TRANSCRIPT
Building MS Access Reports
Creating queries and reports for list items
Data Structure Discussion Sample Data
• CDCR Data Download
Relational Database• Primary Key• Child Key• Lookup table
Query design
Query tricks for reports 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)
Query tricks for reports 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)
Where did you find that?
Report design
Report tricks Conditional formatting
• Highlight specific records
Sub-Reports• One-to-many relationships
Examples: SMT, Vehicles, Addresses, Conditions• Can grow/shrink
Advanced report design 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)
Adding photos to reports 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
How far can we go?
Questions? Contact Information
Matthew HarrisSonoma County Sheriff's [email protected]
Conan MullenSanta Rosa Police [email protected]