I wasn't very thorough in my explanation. Each record is unique. In the
case of a Location table, the combination of Name, Address, City, etc. makes
up a unique record. There may be other entitites with the same city, etc.,
but the combination for a particular entity is unique. A field is often
added to a table in order to provide a convenient unique identifier for a
record. This field is known if Access as the primary key. It can be a
combination of fields, but it can also be a single field. By way of
example, an Employee table will usually include an Employee ID number by
which the employee is identified. No matter if the employee's name changes,
or the employee is transferred to another department, or whatever, the
number does not change.
When I suggested you add a Company table I did not realize that Company and
Agent are interchangeable. Remember that we cannot see your database, so
must rely on your description of it.
Who actually has the license? The company? The location? The agent? The
officer?
You say that a company may have several offices. Let me suggest an approach
that may help you understand the relationships between tables. Create a
company table (tblCompany) and a location table (tblLocation).
tblCompany
CompanyID (PK - may be autonumber)
CompanyName
Other company-specific information
tblLocation
LocationID (PK)
CompanyID (foreign key, or FK)
LocationAddress
Other location-specific information
The PK is established in table design view, but the so-called FK (not
everybody uses that term) is established when you create a relationship.
Click Tools > Relationships. Now click Relationships > Show Table (or use
the toolbar icon). Add both tables, then click OK. Drag CompanyID from one
table and drop it on CompanyID from the other table. Click Enforce
Referential Integrity in the window that appears. Close the Relationships
window.
You have modeled the situation in which a single company may have any number
of locations (one-to-many relationship).
Create a form (frmCompany) based on tblCompany, and another (fsubLocation)
based on tblCompany. With tblCompany open in design view, drag the icon for
fsubLocation onto it. This is one way to create a subform. Click the edge
of the subform, then click View > Properties. The window that appears is
the called the property sheet. Be sure that the Link Master and Link Child
properties both show CompanyID.
You can now add a company to tblCompany by way of the main form. When that
is done, you can add locations by way of the subform.
If Acme Company is number 123 in the Company table, each Location that you
enter in the subform automatically has 123 in the CompanyID field. This
field is what links the parent records (Company) to the child records
(Locations). No other company's locations have 123 in the Company field in
the Location table. By linking the child and master fields in the subform
control, as described, the linking field is added automatically to the child
table when you enter a record by way of the form/subform.
I realize there is more to the project than I have described, but this is
both an illustration and something you will probably need to do anyhow.
