Help With Table Design

[BerkshireGuy]

Hello everyone,

I want to create an employee license plate database and need help with
the best table design.

I was thinking three tables:

1) tblEmployees
EmployeeID
EmployeeName
EmployeeDepartment (this should be a lookup to tblDepartments)
EmployeeTitle

2) tblDepartments (this table uses so that Departments in tblEmployees
is not free formed and user must select from list)

3) tblVehicles
VehiclePlate# (Primary Key since license plates arent the same?)
VehicleMake
VehicleModel
Here is where I am having the problem.

How can I connect the tables with this in mind. An employee can work in
one department. However, an employee can have several different
vehicles?

Keep in mind that the vehicles arent always entered when the record for
the employee is created. Sometimes, an employee may add a vehicle or
change a vehicle.

Thank you,

Brian

[FreeviewClipper]

On 17 Jul 2005 05:19:27 -0700, "BerkshireGuy" <bd*****@yahoo.com>
wrote:

Hello everyone,

I want to create an employee license plate database and need help with
the best table design.

I was thinking three tables:

1) tblEmployees
EmployeeID
EmployeeName
EmployeeDepartment (this should be a lookup to tblDepartments)
EmployeeTitle

2) tblDepartments (this table uses so that Departments in tblEmployees
is not free formed and user must select from list)

3) tblVehicles
VehiclePlate# (Primary Key since license plates arent the same?)
VehicleMake
VehicleModel
Here is where I am having the problem.

How can I connect the tables with this in mind. An employee can work in
one department. However, an employee can have several different
vehicles?

Keep in mind that the vehicles arent always entered when the record for
the employee is created. Sometimes, an employee may add a vehicle or
change a vehicle.

Thank you,

Brian

Hi
Try running this code in a module (eg click inside the code and press
f5)
rejoin any lines which have been split by posting

Public Function aa()
CurrentDb.Execute "CREATE TABLE [tblDepartments] ([DepartmentId] LONG,
[DepartmentName] TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON
tblDepartments(DepartmentId) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblEmployees] ([Employeeid] LONG,
[EmployeeName] TEXT(50), [EmployeeDepartment] LONG, [EmployeeJobTitle]
TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON
[tblEmployees]([Employeeid]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblVehicles] ([VehiclePlateNumber]
TEXT(50), [VehicleMake] TEXT(50), [VehicleModel] TEXT(50),
[EmployeeId] LONG)"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON
[tblVehicles]([VehiclePlateNumber]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "ALTER TABLE tblEmployees ADD CONSTRAINT
tblDepartmentstblEmployees FOREIGN KEY (EmployeeDepartment) REFERENCES
tblDepartments(DepartmentId)"
CurrentDb.Execute "ALTER TABLE tblVehicles ADD CONSTRAINT
tblEmployeestblVehicles FOREIGN KEY (Employeeid) REFERENCES
tblEmployees(Employeeid)"
End Function

[FreeviewClipper]

On 17 Jul 2005 05:19:27 -0700, "BerkshireGuy" <bd*****@yahoo.com>
wrote:

Hello everyone,

I want to create an employee license plate database and need help with
the best table design.

I was thinking three tables:

1) tblEmployees
EmployeeID
EmployeeName
EmployeeDepartment (this should be a lookup to tblDepartments)
EmployeeTitle

2) tblDepartments (this table uses so that Departments in tblEmployees
is not free formed and user must select from list)

3) tblVehicles
VehiclePlate# (Primary Key since license plates arent the same?)
VehicleMake
VehicleModel
Here is where I am having the problem.

How can I connect the tables with this in mind. An employee can work in
one department. However, an employee can have several different
vehicles?

Keep in mind that the vehicles arent always entered when the record for
the employee is created. Sometimes, an employee may add a vehicle or
change a vehicle.

Thank you,

Brian

Hi
Try running this code in a module (eg click inside the code and press
f5)
rejoin any lines which have been split by posting

Public Function aa()
CurrentDb.Execute "CREATE TABLE [tblDepartments] ([DepartmentId] LONG,
[DepartmentName] TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON
tblDepartments(DepartmentId) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblEmployees] ([Employeeid] LONG,
[EmployeeName] TEXT(50), [EmployeeDepartment] LONG, [EmployeeJobTitle]
TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON
[tblEmployees]([Employeeid]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblVehicles] ([VehiclePlateNumber]
TEXT(50), [VehicleMake] TEXT(50), [VehicleModel] TEXT(50),
[EmployeeId] LONG)"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON
[tblVehicles]([VehiclePlateNumber]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "ALTER TABLE tblEmployees ADD CONSTRAINT
tblDepartmentstblEmployees FOREIGN KEY (EmployeeDepartment) REFERENCES
tblDepartments(DepartmentId)"
CurrentDb.Execute "ALTER TABLE tblVehicles ADD CONSTRAINT
tblEmployeestblVehicles FOREIGN KEY (Employeeid) REFERENCES
tblEmployees(Employeeid)"
End Function

[FreeviewClipper]

On 17 Jul 2005 10:30:03 -0500, watching@tv (FreeviewClipper) wrote:

Hi
I thought sending SQL would be a neat way of sending table definitions to a text newsgroup
but I forgot that if you paste split strings into an Access module it adds extra quotes, which
is too big a mess to meddle with.
You would have to join the lines in a text file before pasting into Access.
Here is a version posted with unsplit lines, not sure whether your newreader will still split them.

Public Function aa()
CurrentDb.Execute "CREATE TABLE [tblDepartments] ([DepartmentId] LONG,[DepartmentName] TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON tblDepartments(DepartmentId) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblEmployees] ([Employeeid] LONG,[EmployeeName] TEXT(50), [EmployeeDepartment] LONG, [mployeeJobTitle] TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON [tblEmployees]([Employeeid]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblVehicles] ([VehiclePlateNumber] TEXT(50), [VehicleMake] TEXT(50), [VehicleModel] TEXT(50),[EmployeeId] LONG)"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON [tblVehicles]([VehiclePlateNumber]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "ALTER TABLE tblEmployees ADD CONSTRAINT tblDepartmentstblEmployees FOREIGN KEY (EmployeeDepartment) REFERENCES tblDepartments(DepartmentId)"
CurrentDb.Execute "ALTER TABLE tblVehicles ADD CONSTRAINT tblEmployeestblVehicles FOREIGN KEY (Employeeid) REFERENCES tblEmployees(Employeeid)"
End Function

[FreeviewClipper]

On 17 Jul 2005 10:30:03 -0500, watching@tv (FreeviewClipper) wrote:

Hi
I thought sending SQL would be a neat way of sending table definitions to a text newsgroup
but I forgot that if you paste split strings into an Access module it adds extra quotes, which
is too big a mess to meddle with.
You would have to join the lines in a text file before pasting into Access.
Here is a version posted with unsplit lines, not sure whether your newreader will still split them.

Public Function aa()
CurrentDb.Execute "CREATE TABLE [tblDepartments] ([DepartmentId] LONG,[DepartmentName] TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON tblDepartments(DepartmentId) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblEmployees] ([Employeeid] LONG,[EmployeeName] TEXT(50), [EmployeeDepartment] LONG, [mployeeJobTitle] TEXT(50))"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON [tblEmployees]([Employeeid]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "CREATE TABLE [tblVehicles] ([VehiclePlateNumber] TEXT(50), [VehicleMake] TEXT(50), [VehicleModel] TEXT(50),[EmployeeId] LONG)"
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON [tblVehicles]([VehiclePlateNumber]) WITH PRIMARY DISALLOW NULL "
CurrentDb.Execute "ALTER TABLE tblEmployees ADD CONSTRAINT tblDepartmentstblEmployees FOREIGN KEY (EmployeeDepartment) REFERENCES tblDepartments(DepartmentId)"
CurrentDb.Execute "ALTER TABLE tblVehicles ADD CONSTRAINT tblEmployeestblVehicles FOREIGN KEY (Employeeid) REFERENCES tblEmployees(Employeeid)"
End Function

[BerkshireGuy]

The first one worked create once I joined the lines.

Thanks!