Easy if you know how

  • Thread starter Thread starter Macca1984
  • Start date Start date
M

Macca1984

Hi,

I am trying to compile a database obviously; i have created a table of
companies, with the company name as the primary key there is other basic
information in there such as address. My question is this, how do i use a
company name to auto complete the other fields? is this possible?

I.e if i set up company X and want to link client Y to company x i would
like to assign them to company x and for access to then auto complete the
address and postcode etc
 
First, Company Name is probably not a good choice for primary key, as it is
subject to change. You can cascade update related records in that case, but
you would be better off using something such as autonumber (or another
number or text field) that does not change.
You are on the right track when you say you want to link a client to a
company. If a company can have many clients but a client is associated with
only one company, the client table needs a field to link to the PK field in
the company table.

tblCompany
CompanyID (PK)
CompanyName
etc.

tblClient
ClientID (PK)
CompanyID
FirstName
etc.

There is a one-to-many relationship between CompanyID in the respective
tables. You can set up the user interface as a main form for Company, and a
subform for Client. Each form is bound (has as its record source) one of
the tables. To do this, create two separate forms. On the main form, use
the toolbox to create a subform control. Set its Source Object to the
Client form, and the Link Child and Link Master fields to CompanyID. Use
the three dots next to either Link Child or Link Master to bring up a
linking dialog box.

If each company may have many clients, and each client may be associated
with many companies, you will need a third table:

tblClientCompany
ClientCompanyID (PK)
CompanyID
ClientID

There is a one-to-many relationship between both tblCompany and tblClient,
and tblCompanyClient.

Create a form bound to tblClient. That is where you create Client records
(one per client). Base the main form on tblCompany, and the subform on
tblClientCompany, as described above. The subform can have a combo box that
uses tblClient as its row source. The wizard should be able to help you
create the combo box. This combo box is bound to ClientID in
tblCompanyClient. Add the subform to the main form as described above. Now
you can select any number of Clients for a company. Conversely, you can
view a listing of clients to see with which companies they are associated.

In any case, the linking field is all you store in the "child" table. You
don't copy other information to the Client table.
 
Back
Top