saving records from one table to another table

[bluesage]

hi, im planning a database for a boarding house containing 10 residents and 10 rooms. i need some sample code or if someone can post a sample database it would be most helpful.

here is a description of what i want to do:
================================

tblRESIDENT
id_person (PK)
name

tblROOM
id_room (PK)
id_person (FK)
arrive_date
depart_date

at the end of one year, i want the user through a form, to be given the option to save certain records from the RESIDENT table. Since only 10 slots are available (validation rule <= 10) and each year there are new comers, we only work with the current records. but if the user wants to keep the old residents for reference i want to make it so they can click a button and it takes all the records in which the departure date (depart_date) is not NULL and it creates a new table (ie.tblOLDRESIDENTS) containing these records. so that in RESIDENT we can now add new residents.

* i would also want to make it so it prompts the user to choose a name for the new table before saving it. (but this is not really important)
================================================
i hope its clear up to here.

i have no idea how to do all this, so i hope someone can help.

i know we can do update or insert into table. but i don't know if its possible to cut from a table and paste into another table.

the reason why i need this is that this program will be for billing purposes, and for the users convienience, only the current residents of the house are viewed in the forms. but i plan on having a form with the OLD residents in case the user wishes to view who lived when for statistical or some other reason.


thanks

 

[kramerica]

How about a different approach.

Let's assume you have a table - store the dates of the 'lease' for the room. Have all your forms filter on the current year. Don't worry about moving data in and out of your tables or archiving.. with only 10 rooms leave everything in 1 table and simple filter everything on dates.

This way if you want to see who was in the room in '2002' your main form says 'select the year' - by default the dropdown would be populated to the current year.

As the calendar rolls to 2005 all you need to do is have a form that allows them to 'duplicate last years residents'.

Make sense?

This should simplify EVERYTHING.. forms / reports / queries, because all you have to do is fire everything with a SQL where statement to get the appropriate filter. By having everything in 1 table, it would be very simple to do a full "history" report as well.

As far as the validation rule goes - since there are only 10 rooms. You may consider another design change.

Have a table called tblRooms another table called tblResidents. Then a 3rd table which is tblRoomxResident.

This 3rd table stores roomid / residentid and then lease info for when they have the room.. This way in the future you can lookup rooms and residents.

Your room table would be static. (10 rooms is 10 rooms)

Sorry for the long response.. I just started thinking it through as I was typing it up.

Kramerica

 

[bluesage]

thanks,

but what if i don't want to re-design all my tables? i have the resident id set with the validation rule so i can't have a resident with an id greater than 10. for the rooms the same, the rooms id is 1 to 10.

you think its possible to work with that.

i understand what u were saying though, i may consider re-designing my tables.

thanks again