R
Robert Landers via AccessMonster.com
Hi all.
I'm new to this forum, and a newbie to database programming generally.
I'm trying to create a database to track clients using MS Access 2000. At
the moment, I've decided to implement a supertype/subtype structure, with
the following table schema:
Clients Table (the supertype table):
ClientID (pk) - autonumber
ClientType - stores values 1 for "Individual" and 2 for "Company". These
values identify the relevant subtype table as outlined below.
Address
Suburb
State
PostCode
Country
Tel
Fax
Email
CompanyClients
ClientID (fk) - integer - references ClientID in Clients Table
CompanyName
CompanyNumber
IndividualClients
ClientID (fk) - integer - references ClientID in Clients Table
Title
FirstName
MiddleName
Surname
Suffix
Obviously I've chosen this schema because there are attributes that apply
generally to clients (like Address etc) and other attributes that apply
only to specific types of clients (like CompanyNumber).
Having modeled and implemented this schema though, I'm stuck in terms of
how to perform INSERTS; ie, say I have a new new corporate client named
Smith Enterprises Pty Ltd with a company number ACN 123 456 789 which has
its offices at 1 River Road, Brisbane, Qld 4122 (Australia). In order to
record this new company's details I obviously need to create a new entry in
the Clients Table first so as to generate a new ClientID (and inserting the
address details while I'm at it), and then use that ClientID to create a
new record in the CompanyClients subtype table. Could someone please give
me an example of how I would structure the required INSERT sql command?
In case its relevant, I'm planning to enter the client details via a vb.net
windows form. The form will contain a drop-down list which will enable the
user to select the relevant ClientType.
Thanks in advance
Robert
I'm new to this forum, and a newbie to database programming generally.
I'm trying to create a database to track clients using MS Access 2000. At
the moment, I've decided to implement a supertype/subtype structure, with
the following table schema:
Clients Table (the supertype table):
ClientID (pk) - autonumber
ClientType - stores values 1 for "Individual" and 2 for "Company". These
values identify the relevant subtype table as outlined below.
Address
Suburb
State
PostCode
Country
Tel
Fax
CompanyClients
ClientID (fk) - integer - references ClientID in Clients Table
CompanyName
CompanyNumber
IndividualClients
ClientID (fk) - integer - references ClientID in Clients Table
Title
FirstName
MiddleName
Surname
Suffix
Obviously I've chosen this schema because there are attributes that apply
generally to clients (like Address etc) and other attributes that apply
only to specific types of clients (like CompanyNumber).
Having modeled and implemented this schema though, I'm stuck in terms of
how to perform INSERTS; ie, say I have a new new corporate client named
Smith Enterprises Pty Ltd with a company number ACN 123 456 789 which has
its offices at 1 River Road, Brisbane, Qld 4122 (Australia). In order to
record this new company's details I obviously need to create a new entry in
the Clients Table first so as to generate a new ClientID (and inserting the
address details while I'm at it), and then use that ClientID to create a
new record in the CompanyClients subtype table. Could someone please give
me an example of how I would structure the required INSERT sql command?
In case its relevant, I'm planning to enter the client details via a vb.net
windows form. The form will contain a drop-down list which will enable the
user to select the relevant ClientType.
Thanks in advance
Robert