how to store values from 1 record to transfer 2 another table ?

S

Steven

I run a Library database and I have 2 tables CATA and PRODUCTIONS.
When browsing/searching thru CATA and I find a record of an item I want to
issue to staff.. How can I pass certain data from CATA to PRODUCTIONS - to
save opening both tables and cuting and pasting.. I am using access 97 at the
moment.
Thanks in advance
 
T

Tom Wickerath

Hi Steven,

Please explain in greater detail why you need to transfer records from one
table to another. Generally speaking, storing the same type of data in more
than one table is not good database design. Is there a reason that you cannot
add a boolean field (Yes/No datatype) to the CATA table to indicate whether
or not an item is checked out? You could use this field as a criteria in a
query to filter the recordset for items checked out versus items not checked
out.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I run a Library database and I have 2 tables CATA and PRODUCTIONS.
When browsing/searching thru CATA and I find a record of an item I want to
issue to staff.. How can I pass certain data from CATA to PRODUCTIONS - to
save opening both tables and cuting and pasting.. I am using access 97 at the
moment.
Thanks in advance
 
S

Steven

Hi Tom
I am running a Records Mgt Database based on 3 tables :
Cata - list of all files held in the store
Cons - list of record depostors details and housekeeping details
Productions - list of files reissued to depositors (Each production is given
a unique reference number.

When a depositor asks to retrieve a file/record .. I find the Data Record in
CATA to check they are authorised and details are correct.
Then I input a new record to PRODUCTIONS which is a combination of some of
the files details and the requestors details and allocate the unique
production number(PRN).

The productions table is used for Stats, queries of files still out etc.
I do relate the tables as much as possible.

I had a similar database in a previous job but I wrote it in Foxbase
and using the GATHER command was able to store the data from any CATA record
to memory
and then copy the desired fields to a new record in Productions
and then complete the record with requestor details.

Hope that helps
 
T

Tom Wickerath

Hi Steven,

You stated in your first message that you have 2 tables, CATA and
PRODUCTIONS. You then wrote:
"Then I input a new record to PRODUCTIONS which is a combination
of some of the files details and the requestors details and allocate
the unique production number(PRN)."

It appears as if your PRODUCTIONS table contains data for more than one
subject. The requestors data should probably be in a separate REQUESTORS
table, so that you do not have to repeat this data each time a new record is
added to PRODUCTIONS for a given requestor.

You can certainly do what you want using an append query that is created
with VBA code behind a form. It would grab the values from the various
textboxes that you wanted to add to the PRODUCTIONS table, along with any new
data in textboxes that you assigned. However, before you go too far down that
road, my suggestion is to reconsider your database design. I think what you
really want to do is add a record to the PRODUCTIONS table that includes
foreign keys that are the same values as respective primary keys from the
CATA table and a new REQUESTORS table. Then you would add additional data to
this table, such as your unique production number(PRN) and perhaps the
date(s) involved.

Here are some links to documents that you may find helpful:

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi Tom
I am running a Records Mgt Database based on 3 tables :
Cata - list of all files held in the store
Cons - list of record depostors details and housekeeping details
Productions - list of files reissued to depositors (Each production is given
a unique reference number.

When a depositor asks to retrieve a file/record .. I find the Data Record in
CATA to check they are authorised and details are correct.
Then I input a new record to PRODUCTIONS which is a combination of some of
the files details and the requestors details and allocate the unique
production number(PRN).

The productions table is used for Stats, queries of files still out etc.
I do relate the tables as much as possible.

I had a similar database in a previous job but I wrote it in Foxbase
and using the GATHER command was able to store the data from any CATA record
to memory
and then copy the desired fields to a new record in Productions
and then complete the record with requestor details.

Hope that helps
__________________________________________

:

Hi Steven,

Please explain in greater detail why you need to transfer records from one
table to another. Generally speaking, storing the same type of data in more
than one table is not good database design. Is there a reason that you cannot
add a boolean field (Yes/No datatype) to the CATA table to indicate whether
or not an item is checked out? You could use this field as a criteria in a
query to filter the recordset for items checked out versus items not checked
out.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I run a Library database and I have 2 tables CATA and PRODUCTIONS.
When browsing/searching thru CATA and I find a record of an item I want to
issue to staff.. How can I pass certain data from CATA to PRODUCTIONS - to
save opening both tables and cuting and pasting.. I am using access 97 at the
moment.
Thanks in advance
 
S

Steven

Hi there
you say in your reply :
You can certainly do what you want using an append query that is created
with VBA code behind a form. It would grab the values from the various
textboxes that you wanted to add to the PRODUCTIONS table, along with any new
data in textboxes that you assigned
Click to expand...

I think this is what I am after .. the Access equivilent of the old DBASE
SCATTER and GATHER commands.

Whilst I note your comments on design.. the Database is going to be
converted soon to HTML-DB by our IT section so I have included a wish list of
functionality :)
But if I can solve this problem it would be a great benefit for the moment.

Thanks for the help so far.. I have downloaded the suggested article etc for
reading.

Steven
 
T

Tom Wickerath

Hi Steven,

Try using this article, from Allen Browne's web site, as a guide:

Archive: Move Records to Another Table
http://allenbrowne.com/ser-37.html

I'm not sure if you want to move records from the CATA table to the
PRODUCTIONS table, or simply copy them. If you want to copy only, then don't
execute the delete query in step 3. This example is intended to move all
fields of a record from one table to another table in an archive database.
You can eliminate the IN clause in step 2 ( "IN ""C:\My
Documents\MyArchive.mdb"" " & _ ) since you need to append records in the
PRODUCTIONS table in the same database.

I think you indicated previously that you want to add some additional
information prior to writing the record to the PRODUCTIONS table. One
approach that might work for you is to bind your form to the CATA table, and
add unbound textboxes to the form for any new fields that you want to add to
the record. Then modify the INSERT INTO and the SELECT portions of the strSql
statement shown in Step 2.

Suggestion: Try the example as written, but without the IN clause and the
delete query in step 3, on your database first. Get practice copying a record
from the CATA table to the PRODUCTIONS table first, without trying to add new
data.

Once you have success with that much, then try incremental modifications of
the INSERT INTO and the SELECT portions of the strSql statement in step 2 to
copy (or move) + add new data. Try adding the data from one new textbox at a
time, such as the unique production number(PRN) that you mentioned previously.

Good Luck,

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi there
you say in your reply :
You can certainly do what you want using an append query that is created
with VBA code behind a form. It would grab the values from the various
textboxes that you wanted to add to the PRODUCTIONS table, along with any new
data in textboxes that you assigned
Click to expand...

I think this is what I am after .. the Access equivilent of the old DBASE
SCATTER and GATHER commands.

Whilst I note your comments on design.. the Database is going to be
converted soon to HTML-DB by our IT section so I have included a wish list of
functionality :)
But if I can solve this problem it would be a great benefit for the moment.

Thanks for the help so far.. I have downloaded the suggested article etc for
reading.

Steven
 
Top