copy/move *single* records from Table#1 to Table#2

[Helen]

I've looked through the last month or so for help with
this and found nothing that clearly met my question.

I run a small club library and need to be able to move
(or copy & delete original) records from the Reservation
table to the On Loan table. The two tables have the same
field names but I can't get anything to work!

I think the broard syntax is something like this:
if Reservation:send=yes then move record to On Loan

I've tried working it out with Access' own help but
havent found anything I can make sense of.

I'd appreciate any help.

Helen

 

[Nikos Yannacopoulos]

Helen,

Assuming you are handling the books through a form, you could set up a
Delete query (on the source table) and an Append query (on the target
table), both using as criterion the book_ID, read from the form (the book_ID
from the current record, and use a command button on the form to run the two
queries in sequence (in the reverse order!), either through a macro or
through code. If you use code, you could use SQL statements within the code
itself, and not make / save the two queries at all, or you could even use
recordsets.

I have to say, though, that I suspect your design can be improved; I would
not use separate tables, I would do it in one table, just adding a field for
status and changing that from Reserved to OnLoan. All further development
would be a lot easier, so you might want to consider that option.

HTH,
Nikos

 

[Guest]

I would if I could work out how to set up a formula that
auto calculated the return date, Nikos!

But there is a possibility that I'm going to need to do
it on a membership database so I'm trying it out on the
tiny library database first!

 

[Robin Proctor]

Helen

Why not just have one table holding all records, with an additional
Status field showing the records as either Reserved or On Loan. You then
just need to update this field to record a change in atatus.

One table is easier to maintain, from a design point of view, then two.


Robin

 

[Nikos Yannacopoulos]

Helen,

If you could be more specific on "setting up a formula to auto calculate the
return dat", as well as provide details of your current design, you might
get some help on that!
As a general rule, never compromize the quality of your design just so you
work around a formula you can't crack. Bad designs have this habit of
manifesting themselves in the form of problems of increasing frequency and
complexity, as development progresses.

Regards,
Nikos