OK - So I have very little experience with Access, but I've been given this project: I have to create a database of various government offices which will then be searchable by the state and county of jurisdiction. At this point, I've created the following tables (Table names are indicated by a *):
*Contacts
ContactID (primary key)
DepartmentName
ParentAgency
ContactTypeID
Address1
Address2
City
StateID
PostalCode
WorkPhone
FaxNumber
EmailName
Website
Notes
*Contact_Types
ContactTypeID (Primary Key)
ContactType
*Counties
CountyID (primary key)
CountyName
StateID
*States
StateID (Primary Key)
StateName
*CountyID_ContactID
ID (Primary Key)
Contact
County
As far as relationships between tables/fields go, I’ve linked the following fields:
Contact_Types.ContactTypeID & Contacts.ContactTypeID
States.StateID & Contacts.StateID
States.StateID & Counties.StateID
Counties.StateID & Contacts.StateID
In the CountyID_ContactID table, I set the Contact and County columns as look-up lists in the Contacts table and the Counties table, respectively. Access then created relationships between CountyID_ContactID.Contact & Contacts.ContactID as well as CountyID_ContactID.County & Counties.CountyID. The lookup list for the Contacts is set to display the DepartmentName, ParentAgency, and StateID; the lookup list for the County field is set to display the CountyName and StateID.
So I think I’ve got my tables set up the way they need to be…maybe? What I need to do now is design a query that will allow me to search for contacts based on state and county (ideally, I would use a form with drop-down lists or combo boxes to select them) and return all the applicable agencies, with all of their contact information (basically, all the information in the Contacts table), possibly in a report. Eventually, this query will need to be put on a webpage, so I’ll need some sort of form overlay…If there’s an easy way to do that, I’d love to know about it. But for now, any help designing the query would be greatly appreciated. I guess my biggest problem comes from trying to figure out which stateID field to use in the query, and which table my county selection should come from as well. I’ve got a query that sort of works, but because some federal agencies have, say, offices in Boston, MA and have jurisdiction in MA as well as in NH, my query won’t pick up any of those contacts.
If I haven’t made any sense, I apologize. I’ve been pulling my hair out over this thing for a couple weeks and just want it to be functional (and finished!). Thanks for whatever help you can offer.
*Contacts
ContactID (primary key)
DepartmentName
ParentAgency
ContactTypeID
Address1
Address2
City
StateID
PostalCode
WorkPhone
FaxNumber
EmailName
Website
Notes
*Contact_Types
ContactTypeID (Primary Key)
ContactType
*Counties
CountyID (primary key)
CountyName
StateID
*States
StateID (Primary Key)
StateName
*CountyID_ContactID
ID (Primary Key)
Contact
County
As far as relationships between tables/fields go, I’ve linked the following fields:
Contact_Types.ContactTypeID & Contacts.ContactTypeID
States.StateID & Contacts.StateID
States.StateID & Counties.StateID
Counties.StateID & Contacts.StateID
In the CountyID_ContactID table, I set the Contact and County columns as look-up lists in the Contacts table and the Counties table, respectively. Access then created relationships between CountyID_ContactID.Contact & Contacts.ContactID as well as CountyID_ContactID.County & Counties.CountyID. The lookup list for the Contacts is set to display the DepartmentName, ParentAgency, and StateID; the lookup list for the County field is set to display the CountyName and StateID.
So I think I’ve got my tables set up the way they need to be…maybe? What I need to do now is design a query that will allow me to search for contacts based on state and county (ideally, I would use a form with drop-down lists or combo boxes to select them) and return all the applicable agencies, with all of their contact information (basically, all the information in the Contacts table), possibly in a report. Eventually, this query will need to be put on a webpage, so I’ll need some sort of form overlay…If there’s an easy way to do that, I’d love to know about it. But for now, any help designing the query would be greatly appreciated. I guess my biggest problem comes from trying to figure out which stateID field to use in the query, and which table my county selection should come from as well. I’ve got a query that sort of works, but because some federal agencies have, say, offices in Boston, MA and have jurisdiction in MA as well as in NH, my query won’t pick up any of those contacts.
If I haven’t made any sense, I apologize. I’ve been pulling my hair out over this thing for a couple weeks and just want it to be functional (and finished!). Thanks for whatever help you can offer.
