Moving item from one database to another

[hlm2001]

I am a new user to Access 2003 and need assistance please. I have an
inventory table which I need to filter the obsolete parts out of into another
table. I need the information to automatically update once the "inactive"
box is checked in the main table. Can you walk me through this process.

 

[Jeff Boyce]

Perhaps your task is simpler than you thought.

There's rarely a need to actually move items from one table to another or
from one database to another.

If your table has a field for [Inactive] (?yes/no), or a field into which
you enter the [InactiveDate], you can create a query that returns all of the
"active" records by excluding those marked inactive.

This way, all your data stays in one place, but only that "active" data you
want to see (for normal daily operations) shows up.

Use that query that only shows "active" records as the source for the form
(and reports) that folks need for daily operations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredg]

I am a new user to Access 2003 and need assistance please. I have an
inventory table which I need to filter the obsolete parts out of into another
table. I need the information to automatically update once the "inactive"
box is checked in the main table. Can you walk me through this process.

Why?
Why do you think you "need" to move obsolete parts to a different
table.

1) You should not be using the table itself for data entry. You should
be using a form.

2) Using a form, a better solution would be to simply check the
Inactive box (as it appears you are now doing) on the form, but use a
query based upon your table as the record source for the form. Set the
criteria of the query's [Inactive] check box field to
= 0

Only unchecked, current parts will appear in your form, yet all of the
data is readily available in the table if you need to see the old
well as the new parts in the future. Just delete the query criteria.

 

[hlm2001]

I am referencing 'need' because my boss wants this information in two
seperate databases? He has one form that information is pulled out of SYSPRO
and input into an active form and from that form he wants the inactive parts
to be a seperate database completely than the active parts. Am I confused?
yes slightly.
--
HLM

I am a new user to Access 2003 and need assistance please. I have an
inventory table which I need to filter the obsolete parts out of into another
table. I need the information to automatically update once the "inactive"
box is checked in the main table. Can you walk me through this process.

Why?
Why do you think you "need" to move obsolete parts to a different
table.

1) You should not be using the table itself for data entry. You should
be using a form.

2) Using a form, a better solution would be to simply check the
Inactive box (as it appears you are now doing) on the form, but use a
query based upon your table as the record source for the form. Set the
criteria of the query's [Inactive] check box field to
= 0

Only unchecked, current parts will appear in your form, yet all of the
data is readily available in the table if you need to see the old
well as the new parts in the future. Just delete the query criteria.

 

[JimS]

I know you're confused, but you need to think like a database person. Parts
are parts, obsolete or not. Obsolescence is a characteristic of a part (a
column). If you offer a query showing all the columns in the parts table, but
selecting only obsolete parts, it's functionally equivalent to having them in
a separate db, but you don't have to maintain separate maintenance screens,
queries, etc.

Listen to these guys...they're right.
--
Jim

I am referencing 'need' because my boss wants this information in two
seperate databases? He has one form that information is pulled out of SYSPRO
and input into an active form and from that form he wants the inactive parts
to be a seperate database completely than the active parts. Am I confused?
yes slightly.
--
HLM

I am a new user to Access 2003 and need assistance please. I have an
inventory table which I need to filter the obsolete parts out of into another
table. I need the information to automatically update once the "inactive"
box is checked in the main table. Can you walk me through this process.

Why?
Why do you think you "need" to move obsolete parts to a different
table.

1) You should not be using the table itself for data entry. You should
be using a form.

2) Using a form, a better solution would be to simply check the
Inactive box (as it appears you are now doing) on the form, but use a
query based upon your table as the record source for the form. Set the
criteria of the query's [Inactive] check box field to
= 0

Only unchecked, current parts will appear in your form, yet all of the
data is readily available in the table if you need to see the old
well as the new parts in the future. Just delete the query criteria.

 

[George]

It's the boss who wants to impose this inappropriate requirement who is
confused, not you.

In the form which presents information on parts, simply apply a filter which
selects only active parts, as has previously been suggested.

I am referencing 'need' because my boss wants this information in two
seperate databases? He has one form that information is pulled out of
SYSPRO
and input into an active form and from that form he wants the inactive
parts
to be a seperate database completely than the active parts. Am I
confused?
yes slightly.
--
HLM

I am a new user to Access 2003 and need assistance please. I have an
inventory table which I need to filter the obsolete parts out of into
another
table. I need the information to automatically update once the
"inactive"
box is checked in the main table. Can you walk me through this
process.

Why?
Why do you think you "need" to move obsolete parts to a different
table.

1) You should not be using the table itself for data entry. You should
be using a form.

2) Using a form, a better solution would be to simply check the
Inactive box (as it appears you are now doing) on the form, but use a
query based upon your table as the record source for the form. Set the
criteria of the query's [Inactive] check box field to
= 0

Only unchecked, current parts will appear in your form, yet all of the
data is readily available in the table if you need to see the old
well as the new parts in the future. Just delete the query criteria.

 

[Jeff Boyce]

Following a spreadsheet approach when using a relational database is a
little like driving nails with a chainsaw... can you do it, yes. Is it a
good idea/use of the tool, nope!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am referencing 'need' because my boss wants this information in two
seperate databases? He has one form that information is pulled out of
SYSPRO
and input into an active form and from that form he wants the inactive
parts
to be a seperate database completely than the active parts. Am I
confused?
yes slightly.
--
HLM

I am a new user to Access 2003 and need assistance please. I have an
inventory table which I need to filter the obsolete parts out of into
another
table. I need the information to automatically update once the
"inactive"
box is checked in the main table. Can you walk me through this
process.

Why?
Why do you think you "need" to move obsolete parts to a different
table.

1) You should not be using the table itself for data entry. You should
be using a form.

2) Using a form, a better solution would be to simply check the
Inactive box (as it appears you are now doing) on the form, but use a
query based upon your table as the record source for the form. Set the
criteria of the query's [Inactive] check box field to
= 0

Only unchecked, current parts will appear in your form, yet all of the
data is readily available in the table if you need to see the old
well as the new parts in the future. Just delete the query criteria.