multiple tables with same fields

[Jason Sands]

Hello all. I searched and found the following but do not think it
meets what I am attempting to do.
http://groups.google.com/group/micr...bdesign/browse_thread/thread/e2a70336e9a81462

There are five tables within my database: [1] inventory [2] to users
[3] from users [4] shipped to leasing company [5] received from Lenovo

One solution is to add a field and select which place the computer is
at, but the bosses do not like that idea. They want to keep the same
five tables. The problem, is that, when I create a new field (or
delete a field) in one table I have to do the same in all tables. In
there any way I can create a 'master table' and all other table fields
come from this one master table?

The following is a post from the thread I mentioned; I do not know
where to put it, even to try it.
Public Sub Foo(ATableName As String)
Dim db As DAO.Database

 

[John W. Vinson]

Hello all. I searched and found the following but do not think it
meets what I am attempting to do.
http://groups.google.com/group/micr...bdesign/browse_thread/thread/e2a70336e9a81462

There are five tables within my database: [1] inventory [2] to users
[3] from users [4] shipped to leasing company [5] received from Lenovo

One solution is to add a field and select which place the computer is
at, but the bosses do not like that idea. They want to keep the same
five tables. The problem, is that, when I create a new field (or
delete a field) in one table I have to do the same in all tables. In
there any way I can create a 'master table' and all other table fields
come from this one master table?

The following is a post from the thread I mentioned; I do not know
where to put it, even to try it.
Public Sub Foo(ATableName As String)
Dim db As DAO.Database

The fact that your bosses don't like the idea merely proves that they do not
understand the principles of relational database design. They're WRONG.

If you are copying all the fields from the Inventory into the "Received from
Lenovo" table or vice versa, you're misusing Access. The fields describing an
inventory item should exist in the inventory table AND NOPLACE ELSE. Any
shipping or receipt information should exist in another table, with an
InventoryID field as a link; the other fields in this table should contain
data about the act of shipping the item, NOT about the item itself.

What ARE the fields in your tables? Why do you find it necessary to routinely
add and delete fields? That should happen VERY rarely once an application is
in production!

 

[Jason Sands]

all tables contain the following fields:
PC# (primary key)
First Name
Last Name
AD Computer Name
PC Model
PC Serial
PC Lease End
PC Purcahsed (checkmark)
Monitor Model
Monitor Serial
Monitor Lease End
Monitor Purcahsed (checkmark)
Laptop Docking station (checkmark)
docking s/n
laptop floppy (checkmark)
MAC ADDRESS
Static IP (if it has one)
Location (Sales, Basement, Plant 1, Plant 2, Plant 3, Offiste, etc
etc)
Windows license - 2000, XP, Vista
License Number

I have added the mac address, static ip, location, and license
numbers. When I copy them around it will not copy correctly unless I
add the fields to the other tables.

Received from Lenovo are PCs/monitors that have arrived but not been
assigned to any individual.

Basically they want to know what has come in, what is ready to go out,
what has come back, and what have we sent to the leasing company.

Hello all. I searched and found the following but do not think it
meets what I am attempting to do.
http://groups.google.com/group/microsoft.public.access.tablesdbdesign...

There are five tables within my database: [1] inventory [2] to users
[3] from users [4] shipped to leasing company [5] received from Lenovo

One solution is to add a field and select which place the computer is
at, but the bosses do not like that idea. They want to keep the same
five tables. The problem, is that, when I create a new field (or
delete a field) in one table I have to do the same in all tables. In
there any way I can create a 'master table' and all other table fields
come from this one master table?

The following is a post from the thread I mentioned; I do not know
where to put it, even to try it.
Public Sub Foo(ATableName As String)
Dim db As DAO.Database

The fact that your bosses don't like the idea merely proves that they do not
understand the principles of relational database design. They're WRONG.

If you are copying all the fields from the Inventory into the "Received from
Lenovo" table or vice versa, you're misusing Access. The fields describing an
inventory item should exist in the inventory table AND NOPLACE ELSE. Any
shipping or receipt information should exist in another table, with an
InventoryID field as a link; the other fields in this table should contain
data about the act of shipping the item, NOT about the item itself.

What ARE the fields in your tables? Why do you find it necessary to routinely
add and delete fields? That should happen VERY rarely once an application is
in production!

 

[John W. Vinson]

all tables contain the following fields:
PC# (primary key)
First Name
Last Name
AD Computer Name
PC Model
PC Serial
PC Lease End
PC Purcahsed (checkmark)
Monitor Model
Monitor Serial
Monitor Lease End
Monitor Purcahsed (checkmark)
Laptop Docking station (checkmark)
docking s/n
laptop floppy (checkmark)
MAC ADDRESS
Static IP (if it has one)
Location (Sales, Basement, Plant 1, Plant 2, Plant 3, Offiste, etc
etc)
Windows license - 2000, XP, Vista
License Number

I have added the mac address, static ip, location, and license
numbers. When I copy them around it will not copy correctly unless I
add the fields to the other tables.

Received from Lenovo are PCs/monitors that have arrived but not been
assigned to any individual.

Basically they want to know what has come in, what is ready to go out,
what has come back, and what have we sent to the leasing company.

Your structure looks like a very good design... *for an Excel spreadsheet*.

You're not using an Excel spreadsheet though! You're using Access, which is
designed to work as a relational database.

I don't know your business model, nor do I clearly understand why you're
storing data in table names; but you should have one table for each kind of
Entity (real-life person, thing, or event). A computer does NOT HAVE A FIRST
NAME or a last name property (well, not yet, AI software is coming along
though); the act of assigning a computer to a person is not a computer nor is
it a person.

Just at first blush I would imagine the following tables:

Locations
Location <Text, Primary Key>

Employees
EmployeeID <primary key, probably a human-resources assigned employee
number>
LastName
FirstName
Location

Computers
PCSerial <primary key>
<the other attributes of the computer itself>

ComputerAssignment
EmployeeID <to whom has this computer been assigned>
PCSerial <which computer were they assigned>
AssignmentDate
<other fields relevant to THIS person and THIS computer>

My guess is that you will need more tables: but in a proper design you will
NOT have any two tables with the same set of fields. You're using a relational
database - use it relationally! You'll find it's much more powerful than what
you're trying to do now.

 

[Jason Sands]

Your structure looks like a very good design... *for an Excel spreadsheet*.

You're not using an Excel spreadsheet though! You're using Access, which is
designed to work as a relational database.

I don't know your business model, nor do I clearly understand why you're
storing data in table names; but you should have one table for each kind of
Entity (real-life person, thing, or event). A computer does NOT HAVE A FIRST
NAME or a last name property (well, not yet, AI software is coming along
though); the act of assigning a computer to a person is not a computer nor is
it a person.

Just at first blush I would imagine the following tables:

Locations
Location <Text, Primary Key>

Employees
EmployeeID <primary key, probably a human-resources assigned employee
number>
LastName
FirstName
Location

Computers
PCSerial <primary key>
<the other attributes of the computer itself>

ComputerAssignment
EmployeeID <to whom has this computer been assigned>
PCSerial <which computer were they assigned>
AssignmentDate
<other fields relevant to THIS person and THIS computer>

My guess is that you will need more tables: but in a proper design you will
NOT have any two tables with the same set of fields. You're using a relational
database - use it relationally! You'll find it's much more powerful than what
you're trying to do now.

How do I put a bandaid on this with all the tables having the same
fields until I do the above?
Do I just export it to an excel spreadsheet or is there a way to have
all tables with the same fields?

 

[John W. Vinson]

How do I put a bandaid on this with all the tables having the same
fields until I do the above?

Putting a bandaid on a compound fracture isn't responsible first aid, but...

Do I just export it to an excel spreadsheet or is there a way to have
all tables with the same fields?

You can run four Delete queries without criteria to empty the four duplicate
tables, and then run four Append queries to repopulate them. Compact your
database afterward if your tables are at all large - and of course MAKE GOOD
BACKUPS FIRST!!!!