By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,240 Members | 791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,240 IT Pros & Developers. It's quick & easy.

Help With Table Design

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
The first one worked create once I joined the lines.

Thanks!

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.