Append + Delete Query - Single Record

[PPat]

Hi everyone,

I am a college student working on an Access database for my job. To give a quick overview, I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.

Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.

However, I cannot get figure out how to make the append query move only one student's record at a time. I am competent in VBA, but not as much in SQL, so a simplified explanation would really appreciated. If you need other pieces of information, please feel free to ask and I will provide.

Thank you for your time.

 

[plog]

That's not how databases are to work. Records don't move from table to table. They get created in one table, then their ID gets used in other tables to record data relevant to that ID.

I don't know what to tell you, other than you need to start from scratch. Your entire process is incorrect. Look into normalization (http://en.wikipedia.org/wiki/Database_normalization), try and find some example databases and rethink how your data should be organized based on that.

If you want to post your data along with what is to occur with it we can help, but the path you are going down now is wrong.

 

[PPat]

That's not how databases are to work. Records don't move from table to table. They get created in one table, then their ID gets used in other tables to record data relevant to that ID.

I don't know what to tell you, other than you need to start from scratch. Your entire process is incorrect. Look into normalization, try and find some example databases and rethink how your data should be organized based on that.

If you want to post your data along with what is to occur with it we can help, but the path you are going down now is wrong.

I know it's not ideally how the database is suppose to work, but the way this database is going to be used is more of a filing system than anything else. There are physical files, and this database is used as a back-up for that information. It's the way they wanted it designed.