Relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a property management company with 100 properties in inventory. We
have 3 contractors that maintain these properties. Each contractor has a
specific county and/or zip code. I have set up a master table with all of
the properties. What I want to do is set up a table or query for each vendor
so that when I enter in a new property it will then automatically go to the
correct vendor table or query. Everything I have tried will just list all of
the properties in each vendor. How can I break it down? Thanks for your
help.
 
Having all the properties in one table is perfect.
Whether you need another table or query from here will depend on some other
factors.

You say, each contractor has a specific zip code. Questions:
1. Just one zip code per contractor? Or could a contractor cover more than
one?

2. Could there ever be a situation where you wanted to assign a property to
a contractor who is not the normal contractor for that zip code?

3. Could there ever be a time when you might change to a different
contractor in the future? If so, would you still want your database to
correctly retain the old contractor info for your old records?

If one contractor could handle more than one zip code, you will need a
ContractorZip table, with fields:
Zip the zip code this contractor handles. Primary key.
(Text data type.)
ContractorID relates to Contractor.ContractorID
Interface this with a subform on your Contractor form, so the user can enter
multiple zip codes for the contractor (one per row.)

If the answer to question 2 or 3 is yes, you also need a ContractorID field
in your Property table, since the contractor could be different from the
current default contractor for the zip code. You can use the AfterUpdate
event of the Zip text box to DLookup() the contractor, and assign the value
to ContractorID
Private Sub Zip_AfterUpdate()
If Not IsNull(Me.Zip) Then
Me.Zip = DLookup("ContractorID", "ContractorZip", "Zip = """ &
Me.Zip & """")
End If
End Sub
 
Back
Top