Tough Question

[Armyman]

I have been asked at my job to come up with an answer to this question:

we have a database that I designed that keeps track of basic inventory
items, the db contains 5 tables for 5 different inventories but all of the
tables contain the same columns.

when an record is inputted into a certain table, is there a way to create a
button on the form that will MOVE that specific record from any one of those
tables and ADD it to a 6th table (the 6th table will already be created, but
we want the item in question to moved when it's no longer needed in the
inventory)

I hope I've explained this well enough, I've tried working with the queries
and macros but I've not come up with a viable solution yet. I'm not
anywhere close to being very knowledgable about access but I can follow a
step-by-step direction very well.

any help is greatly appreciated.

Scott

 

[Rick Brandt]

I have been asked at my job to come up with an answer to this
question:

we have a database that I designed that keeps track of basic inventory
items, the db contains 5 tables for 5 different inventories but all
of the tables contain the same columns.

when an record is inputted into a certain table, is there a way to
create a button on the form that will MOVE that specific record from
any one of those tables and ADD it to a 6th table (the 6th table will
already be created, but we want the item in question to moved when
it's no longer needed in the inventory)

I hope I've explained this well enough, I've tried working with the
queries and macros but I've not come up with a viable solution yet.
I'm not anywhere close to being very knowledgable about access but I
can follow a step-by-step direction very well.

any help is greatly appreciated.

Scott

You should have one table with a column or columns that distinguish the type
and another column that is for "Status".

Having multiple tables with the same or very similar structures is usually
an inproper design.

 

[Vincent Johns]

Or, instead of moving the entire record somewhere, you might add a
[IsStillNeeded?] yes/no field to each record that you can use to declare
items obsolete.

I suggest you go easy on the Macros, Reports, &c., until after your
Tables and Queries appear to be working as you want them to. Then base
the Forms, Reports, &c., on the Queries that give you what you need.

You should have one table with a column or columns that distinguish the type
and another column that is for "Status".

Having multiple tables with the same or very similar structures is usually
an inproper design.

.... one reason for which is that, if you later have to make changes to
the design of one of your fields, you most likely don't want to have to
repeat that work multiple times. Also, it will be (much) easier to
write Queries based on your fields if you avoid duplications.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Armyman]

I am going to assume that my question was not made clear enough or else I do
not understand your responses...

My database is as follows:
tbl_a : tbl_b : tbl_c : tbl_d : tbl_e

each table has the same columns: ID : name : Date : Product :
dateturndin
each record in each table is different.
when I open the form for tbl_a, I would like to scroll through the records
and when I come to a record that is out of date and has a value in the
"dateturnin" field, I want to press a button to have all the records that
have data posted in the "dateturnin" column, to be removed from tbl_a and
placed in tbl_e...and the same for tbl's b, c and d. so that all the records
that have been turned in from the inventory do not show up in tbl_a's
inventory anymore, but show up in tbl_e (which is the master inventory table
that shows all the inv. items that are not placed in anyones inventory.)






--
Marcus Reed

Or, instead of moving the entire record somewhere, you might add a
[IsStillNeeded?] yes/no field to each record that you can use to declare
items obsolete.

I suggest you go easy on the Macros, Reports, &c., until after your
Tables and Queries appear to be working as you want them to. Then base
the Forms, Reports, &c., on the Queries that give you what you need.

You should have one table with a column or columns that distinguish the type
and another column that is for "Status".

Having multiple tables with the same or very similar structures is usually
an inproper design.

.... one reason for which is that, if you later have to make changes to
the design of one of your fields, you most likely don't want to have to
repeat that work multiple times. Also, it will be (much) easier to
write Queries based on your fields if you avoid duplications.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Rick Brandt]

I am going to assume that my question was not made clear enough or
else I do not understand your responses...

My database is as follows:
tbl_a : tbl_b : tbl_c : tbl_d : tbl_e

each table has the same columns: ID : name : Date : Product :
dateturndin [snip]

And what I said is that such a database design is incorrect. We often see new
users to Access who have created 12 identical tables for each month of the year
and then they have difficulties doing common database activities because they
have incorrectly spread their data over multiple tables when they should
actually be in a single table with an additional date field. What you are
describing is similar to this.

You are asking how to move data amongst multiple tables with the same structure
presumably because a record existing in one table means something different than
the same record being stored in another table. This means that the table
structures themselves are now part of the data and this is not proper database
design.

I will concede that I might be incorrect in my assessment, but you would have to
describe in more detail exactly what kind of data you are storing and why you
are using six tables to do so.

 

[BruceM]

In addition to what Rick has written, I will add that a table should contain
information about a single entity. You should be able to describe a table's
purpose in a single sentence without using the word "and". You seem to have
records that contain a person's name, a product, and tracking information
(date turned in) about that product. That means you probably have multiple
records containing the same person's name, and multiple records containing
the same product, but you make no mention of a Names table or a Products
table. Or maybe the names are from the general public, and would repeat
only rarely, in which case you may well wish to store the information in the
same table as the ProductTurnedIn table or whatever exactly it is. This is
where some details would help.
There is every indication that you are pursuing an incorrect design, one
that would be better suited to a spreadsheet. Perhaps if you explain why
you see the need for five tables you could get some specific assistance.
Are you familiar with queries? If you base a query on a table and use Is
Null as the criteria for DateTurnedIn you will not see records for which
there is a value in that field. In other words, if it has been turned in
and that fact has been logged, those records will not appear. Unless you
have several tens of thousands of records (at least) there is no problem
with keeping a lot of data in a single table.

I am going to assume that my question was not made clear enough or else I
do
not understand your responses...

My database is as follows:
tbl_a : tbl_b : tbl_c : tbl_d : tbl_e

each table has the same columns: ID : name : Date : Product :
dateturndin
each record in each table is different.
when I open the form for tbl_a, I would like to scroll through the records
and when I come to a record that is out of date and has a value in the
"dateturnin" field, I want to press a button to have all the records that
have data posted in the "dateturnin" column, to be removed from tbl_a and
placed in tbl_e...and the same for tbl's b, c and d. so that all the
records
that have been turned in from the inventory do not show up in tbl_a's
inventory anymore, but show up in tbl_e (which is the master inventory
table
that shows all the inv. items that are not placed in anyones inventory.)






--
Marcus Reed

Armyman wrote:

I have been asked at my job to come up with an answer to this
question:

we have a database that I designed that keeps track of basic inventory
items, the db contains 5 tables for 5 different inventories but all
of the tables contain the same columns.

when an record is inputted into a certain table, is there a way to
create a button on the form that will MOVE that specific record from
any one of those tables and ADD it to a 6th table (the 6th table will
already be created, but we want the item in question to moved when
it's no longer needed in the inventory)

Or, instead of moving the entire record somewhere, you might add a
[IsStillNeeded?] yes/no field to each record that you can use to declare
items obsolete.

I hope I've explained this well enough, I've tried working with the
queries and macros but I've not come up with a viable solution yet.
I'm not anywhere close to being very knowledgable about access but I
can follow a step-by-step direction very well.

I suggest you go easy on the Macros, Reports, &c., until after your
Tables and Queries appear to be working as you want them to. Then base
the Forms, Reports, &c., on the Queries that give you what you need.

any help is greatly appreciated.

Scott


You should have one table with a column or columns that distinguish the
type
and another column that is for "Status".

Having multiple tables with the same or very similar structures is
usually
an inproper design.

.... one reason for which is that, if you later have to make changes to
the design of one of your fields, you most likely don't want to have to
repeat that work multiple times. Also, it will be (much) easier to
write Queries based on your fields if you avoid duplications.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Armyman]

Actually, each table in the database represents and employee and what items
is in each persons inventory, so the table structure I believe is correct.
so what's here is when an employee has something given to them, they goto
their table and input the appropiate information, each item that they input
is different, but the gathered information is the same (ie: name of item,
SO#, Date recieved)

there is also a field for (date turned in) and when this date turned in
field has something inputed in it, this item no longer belongs to this
employee's inventory, so that record needs to be moved out of that table and
into another table that list all the items that are not in any employee's
inventory.
--
Marcus Reed

In addition to what Rick has written, I will add that a table should contain
information about a single entity. You should be able to describe a table's
purpose in a single sentence without using the word "and". You seem to have
records that contain a person's name, a product, and tracking information
(date turned in) about that product. That means you probably have multiple
records containing the same person's name, and multiple records containing
the same product, but you make no mention of a Names table or a Products
table. Or maybe the names are from the general public, and would repeat
only rarely, in which case you may well wish to store the information in the
same table as the ProductTurnedIn table or whatever exactly it is. This is
where some details would help.
There is every indication that you are pursuing an incorrect design, one
that would be better suited to a spreadsheet. Perhaps if you explain why
you see the need for five tables you could get some specific assistance.
Are you familiar with queries? If you base a query on a table and use Is
Null as the criteria for DateTurnedIn you will not see records for which
there is a value in that field. In other words, if it has been turned in
and that fact has been logged, those records will not appear. Unless you
have several tens of thousands of records (at least) there is no problem
with keeping a lot of data in a single table.

I am going to assume that my question was not made clear enough or else I
do
not understand your responses...

My database is as follows:
tbl_a : tbl_b : tbl_c : tbl_d : tbl_e

each table has the same columns: ID : name : Date : Product :
dateturndin
each record in each table is different.
when I open the form for tbl_a, I would like to scroll through the records
and when I come to a record that is out of date and has a value in the
"dateturnin" field, I want to press a button to have all the records that
have data posted in the "dateturnin" column, to be removed from tbl_a and
placed in tbl_e...and the same for tbl's b, c and d. so that all the
records
that have been turned in from the inventory do not show up in tbl_a's
inventory anymore, but show up in tbl_e (which is the master inventory
table
that shows all the inv. items that are not placed in anyones inventory.)






--
Marcus Reed

Rick Brandt wrote:

Armyman wrote:

I have been asked at my job to come up with an answer to this
question:

we have a database that I designed that keeps track of basic inventory
items, the db contains 5 tables for 5 different inventories but all
of the tables contain the same columns.

when an record is inputted into a certain table, is there a way to
create a button on the form that will MOVE that specific record from
any one of those tables and ADD it to a 6th table (the 6th table will
already be created, but we want the item in question to moved when
it's no longer needed in the inventory)

Or, instead of moving the entire record somewhere, you might add a
[IsStillNeeded?] yes/no field to each record that you can use to declare
items obsolete.

I hope I've explained this well enough, I've tried working with the
queries and macros but I've not come up with a viable solution yet.
I'm not anywhere close to being very knowledgable about access but I
can follow a step-by-step direction very well.

I suggest you go easy on the Macros, Reports, &c., until after your
Tables and Queries appear to be working as you want them to. Then base
the Forms, Reports, &c., on the Queries that give you what you need.


any help is greatly appreciated.

Scott


You should have one table with a column or columns that distinguish the
type
and another column that is for "Status".

Having multiple tables with the same or very similar structures is
usually
an inproper design.

.... one reason for which is that, if you later have to make changes to
the design of one of your fields, you most likely don't want to have to
repeat that work multiple times. Also, it will be (much) easier to
write Queries based on your fields if you avoid duplications.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[BruceM]

Your design is incorrect. It would be fine if you were building a
spreadsheet, with each person having their own spreadsheet tab, but it is
not correct for a relational database. I said that you should be able to
describe a table's purpose in a single sentence without using the word
"and", yet you write back and say that each table represents an employee AND
what items are in that person's inventory.
Further, people should not be enetering data directly into tables. That's
what forms are for.
You need an employee table that contains ONLY employee information such as
FirstName, LastName, EmployeeID, etc. Other than that you need at least one
other table, but it is difficult to know what is needed there. If each item
given to an employee is unique, and will never be given to another employee,
or will never be given for a second time to the original employee, then you
need just one other table for ItemInformation. However, if the situation is
other than what I just described, you will need three tables.
Rather than trying to cover every contingency I will see how you respond.

Actually, each table in the database represents and employee and what
items
is in each persons inventory, so the table structure I believe is correct.
so what's here is when an employee has something given to them, they goto
their table and input the appropiate information, each item that they
input
is different, but the gathered information is the same (ie: name of item,
SO#, Date recieved)

there is also a field for (date turned in) and when this date turned in
field has something inputed in it, this item no longer belongs to this
employee's inventory, so that record needs to be moved out of that table
and
into another table that list all the items that are not in any employee's
inventory.
--
Marcus Reed

In addition to what Rick has written, I will add that a table should
contain
information about a single entity. You should be able to describe a
table's
purpose in a single sentence without using the word "and". You seem to
have
records that contain a person's name, a product, and tracking information
(date turned in) about that product. That means you probably have
multiple
records containing the same person's name, and multiple records
containing
the same product, but you make no mention of a Names table or a Products
table. Or maybe the names are from the general public, and would repeat
only rarely, in which case you may well wish to store the information in
the
same table as the ProductTurnedIn table or whatever exactly it is. This
is
where some details would help.
There is every indication that you are pursuing an incorrect design, one
that would be better suited to a spreadsheet. Perhaps if you explain why
you see the need for five tables you could get some specific assistance.
Are you familiar with queries? If you base a query on a table and use Is
Null as the criteria for DateTurnedIn you will not see records for which
there is a value in that field. In other words, if it has been turned in
and that fact has been logged, those records will not appear. Unless you
have several tens of thousands of records (at least) there is no problem
with keeping a lot of data in a single table.

I am going to assume that my question was not made clear enough or else
I
do
not understand your responses...

My database is as follows:
tbl_a : tbl_b : tbl_c : tbl_d : tbl_e

each table has the same columns: ID : name : Date : Product :
dateturndin
each record in each table is different.
when I open the form for tbl_a, I would like to scroll through the
records
and when I come to a record that is out of date and has a value in the
"dateturnin" field, I want to press a button to have all the records
that
have data posted in the "dateturnin" column, to be removed from tbl_a
and
placed in tbl_e...and the same for tbl's b, c and d. so that all the
records
that have been turned in from the inventory do not show up in tbl_a's
inventory anymore, but show up in tbl_e (which is the master inventory
table
that shows all the inv. items that are not placed in anyones
inventory.)






--
Marcus Reed


:

Rick Brandt wrote:

Armyman wrote:

I have been asked at my job to come up with an answer to this
question:

we have a database that I designed that keeps track of basic
inventory
items, the db contains 5 tables for 5 different inventories but all
of the tables contain the same columns.

when an record is inputted into a certain table, is there a way to
create a button on the form that will MOVE that specific record from
any one of those tables and ADD it to a 6th table (the 6th table
will
already be created, but we want the item in question to moved when
it's no longer needed in the inventory)

Or, instead of moving the entire record somewhere, you might add a
[IsStillNeeded?] yes/no field to each record that you can use to
declare
items obsolete.

I hope I've explained this well enough, I've tried working with the
queries and macros but I've not come up with a viable solution yet.
I'm not anywhere close to being very knowledgable about access but I
can follow a step-by-step direction very well.

I suggest you go easy on the Macros, Reports, &c., until after your
Tables and Queries appear to be working as you want them to. Then
base
the Forms, Reports, &c., on the Queries that give you what you need.


any help is greatly appreciated.

Scott


You should have one table with a column or columns that distinguish
the
type
and another column that is for "Status".

Having multiple tables with the same or very similar structures is
usually
an inproper design.

.... one reason for which is that, if you later have to make changes
to
the design of one of your fields, you most likely don't want to have
to
repeat that work multiple times. Also, it will be (much) easier to
write Queries based on your fields if you avoid duplications.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Actually, each table in the database represents and employee and what items
is in each persons inventory, so the table structure I believe is correct.

You can believe anything you want. What I claim is that organizing your
information this way will lead to lots of extra work on your part.
OTOH, Access won't complain about it, and you should be able to get some
value out of it, just maybe not enough to make it worth your time to use
Access. Tables in Word might do most of what you want. (OK, I'm kind
of joking here, but I really do use Word tables on occasion.)

If you mean that each Table contains some (one) employee's information
and there's no commonality among them, then you may indeed have a good
design. Maybe for the first employee you have only salary information
and badge number, and for the next employee you store only the birth
dates and hair colors of his children. For that, it would make sense to
have multiple Tables. However, if there IS some common information,
such as that for each employee you know a first name and a last name,
then you're missing something -- the common information belongs in one
Table unless there's a reason to separate it.

so what's here is when an employee has something given to them, they goto
their table and input the appropiate information, each item that they input
is different, but the gathered information is the same (ie: name of item,
SO#, Date recieved)

If the type of information is the same (e.g., [Date received]), then it
makes no sense to store it in two (or a dozen) different places. You
will have a hard time analyzing the results if you try doing it that
way. (Although, as I said, Access won't complain at you for having a
suboptimal design.)

If users are accessing Tables directly, that is probably poor practice
-- it's difficult to avoid ruining existing records. I suggest that you
use your Tables for data entry only when you're initially populating
them. It may be OK to let your users use Queries to display information
in Query Datasheet View, but for data entry, Forms are way preferable.
You can check for some kinds of mistakes (though the process cannot be
made foolproof) and protect already-entered records from accidental
changes. For display, Reports are likely easier to read than Query
Datasheet View, though the datasheets are easier to design.

there is also a field for (date turned in) and when this date turned in
field has something inputed in it, this item no longer belongs to this
employee's inventory,

OK so far...

so that record needs to be moved out of that table and
into another table that list all the items that are not in any employee's
inventory.

.... but this doesn't follow. If you didn't like my suggestion in an
earlier message of adding a field to keep track of status, do something
else. But moving records from one Table to another, although it will
work, involves more effort than you need to put into this. I think
you'd be better off spending your time working on Queries, Forms, and
Reports that will actually add value to what you're doing, than you
would shuffling data around (and having to maintain more Tables than
necessary).

I suppose (full disclosure here) that I should mention that I sometimes
do what you're describing. I have a database in which I frequently
import records from other files, and there are frequent errors. I
import them into a Table that contains a proper subset of the fields in
my main Table. This allows me to edit the imported data, or discard and
re-import them, until I am satisfied that they are accurate. Then I
copy the corrected version to the main Table. However, I don't use the
small Table for permanent storage of anything.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.