Expert: Scottgem - 2/23/2005

Question
I created a database in access to track vehicle mileage. One field has users enter a department number. I need to show in my report which agency the department falls under. Is there a way to write a if,then type statement to automatically assign an agency number to each department. I have 590 departments in 31 agencies. I was trying to use =IIf([Department #]=800,1) {1 is the agency #} but I cant seem to figure out how to add more department #'s if that is even possible.
Thanks for your help
B

Answer
You need more tables. You need An Agency table and you need to an Agency field in your departments table.

tblAgencies
AgencyID (Primary key-Autonumber)
AgencyName

tblDepartments
DepartmentID (PK Autonumber)
DepartmentName
AgencyID (Foreign key to tblAgencies)

With that structure you can join your tables to get Agency Name for reporting purposes.

HTH
Scott<>