Copy record to a different table in Access 2007

[CindySue]

I would like to be able to copy the current record to a different table. I am tracking donations and I create a new table of donations each year--some of the records will be the same or slightly different. I used to do this with a macro in Access 2003, but it doesn't work in Access 2007 the way it was, and I can't find other commands that will work. I would appreciate any help.

[errol999]

Why not just store all data for all years in one table. Have a field for year, and then run a query that selects only for the year you want to see. This saves duplicating work and would use the database to its best potential.

[patjones]

Hi Cindy,

errol999 is correct here. It violates accepted database design rules to have multiple tables that store the same information. If your goal is to run reports off your data, you would simply create a query that picks out the year you want information for, and then base your report on that query.

Pat

[NeoPa]

Cindy,

While I also echo what the others have said (See Normalisation and Table structures for further info), I wonder if your question relates simply to moving data from one table to another simply due to its year.

If not, perhaps you could explain a little more clearly and we may be able to be of some assistance.

[CindySue]

This database is for a charity and is used for about 3 months of each year. We have two tables, one is called donor possibilities, and the other is auction items. At the beginning of each year we make a backup copy of the table and call it by the year (as we infrequently need to look back at previous years donations, but we do sometimes), and then move the records from auction items table, leaving it empty, and append them to the donor possibilities table. The two tables have many of the same fields, but some are different. The donor possibilities table always has some records left in it, either from businesses that donated in the past but not the previous year, or are perhaps new businesses that didn’t exist the previous year. We run a report on the donor possibilities table based upon the volunteer that is to contact them. In this report, I want the business to be listed only once, and only with what they gave the previous year, not everything they have given in years past. I can’t select the records by year as some may have not donated the previous year, but had asked to be contacted again, or some may not have been in business last year. I really don’t want to have to have a field that I have to keep updated to mark them as active. As businesses go out of business or ask to not be contacted again, there is no reason for us to keep the record. If they do donate something, that record is moved to the auction items table and the additional fields are updated. It is nice to just be able to create a button on the form to click to make this happen.

[CindySue]

@NeoPa
Please see my post. I am having trouble figuring out if I need to reply by clicking the reply after the person's comment, or to click post reply. It seems like either my post is not saved, or the person is never notified of my most recent post. Please excuse my difficulties in posting correctly.

[NeoPa]

No worries Cindy. They are all possible ways of replying. Reply by a post takes you to a quick-reply area on the same page (but also saves which post you clicked in order to quote it) where you have another option to go to the Advanced reply page, and Post Reply takes you to the Advanced reply page directly.

Why do you think the person may not be notified of the post though? Or that the post may not have been saved?

[CindySue]

Thanks for your reply. I think it because after I spent several minutes typing the reply, it never showed up in the thread, and no one ever repsonded. I'm not sure what's going on. When I click reply, it takes me to a page that says to sign up, or login in if you are already a member. When I login at the current member part, it then says "we show you are already a member" and then it never takes me to where I can reply. So then I start over, then finally it lets me reply.

It's been long enough that for this year I've finished copying all my records manually, but I'd still like to be able to do this for next year, assuming I'm still the one working on the database.

[NeoPa]

I need to revisit this. I haven't had much time to look at any complicated threads recently so I've had to leave this aside until I get a bit more than a minute to sit down and check properly.

[NeoPa]

There is much that can be done in a situation like yours Cindy, but to point you in the right direction I'd need to understand you a little better. I've read through your post #5 and I can understand you have two tables [Donor Possibilities] & [Auction Items]. What I don't understand is how these tables can contain similar data. Without further explanation I can only go by the names. It would seem clear that [Donor Possibilities] contains data on individuals or enterprises that may have, or may be constrained to, donate in some way. [Auction Items] on the other hand, would appear to be a table to maintain those items that could be auctioned to raise funds. I see no possibility of overlap here (short of auctioning a meal with some of the more attractive donors to raise funds - but I somehow doubt you're talking about that). You say the two tables have many of the same fields.

As far as archiving the data goes, this is relatively straightforward, but does depend absolutely on knowing precisely what you want archived. It's no good having a general understanding of what needs doing that a human may understand. It must be based on solid and explained logic. If you have this logic then it needs to be explained clearly and precisely. Precisely enough that any two people reading the logic would always be in a position to determine what needed archiving in all possible circumstances.

This logic is normally expressed as criteria relative to the fields contained within the specific table. I could guess that the table to be archived is [Donor Possibilities], but that would need to be clarified and some fields (particularly the relevant ones at least) would need to be shared with us before we could even start to think about going forward.

[CindySue]

My apologies in advance for this being so lengthy, but when I tried to be brief, apparently the information was not sufficient to explain why I wanted to be able to move a record from one table to another.

Each year we have a fundraiser and we collect items from local businesses to place in a silent auction. We have many businesses that donate something year after year. Sometimes they donate the same thing (like a gift certificate) and sometimes they may donate something different (like a lamp one year, and a picture the next). Some of these businesses decide they can’t donate this year, but asked to be contacted in the future, and some go out of businesses. Sometimes there are new businesses that we want to ask to donate.

We start off with a table (donor possibilities) that has some general info like the business name, address, phone number, contact person and a place for notes, like when we called, what they said, did they need a request on letterhead, when we are to pick it up or if they are going to mail it, etc. All things that pertain only to acquiring the donation but have little significance after we actually get an item donated. We then assign a volunteer to contact each business. Some donate, some don’t. If they do donate, then we want to move the record into the auction items table, and add (or update) some other info, like the value and description of the item (which may or may not change), but leaving off the notes in the memo field about the steps that led up to getting the donation. This auction items table also has a place for an item number that we assign to track the item, whether the item will be in the live or silent auction, the winning bid amount and winning bid number. After the auction is over we enter the winning bidder numbers and amounts and then print receipts as well as event reports.

The next year we then have both tables. The records still in the donor possibilities tables are only those that didn’t donate but asked to be contacted in the future or are new businesses that we added as we got ready to start the process all over. I select all the records from the auction items database and move them back to the donor possibilities table, minus the fields that pertain only to the auction, like item number, live or silent auction, etc. Now the auction items table is empty and the donor possibilities table has everyone that donated last year, plus new businesses, plus any that didn’t donate last year but asked to be contacted in the future. We then print the list, assign the businesses to volunteers, and delete any that anyone notices that has gone out of business. I print a list for each person of their possibilities so that the volunteer can tell the business what they donated the last year if they ask, and we start the whole process over again.

I have several reasons that I don’t want to try to use one single table for this. I don’t want to add a year field and select from that, as some donate each year, some don’t, and some are new. I don’t want to have to add a field that says if they are active or not as it would have to be manually updated each year in order to be of use. I don’t want to have to list all the records from over several years, as most people don’t care what they gave 2 or 3 years ago, some businesses are no longer in businesses and it would just make a much longer and less manageable list. Since some fields are only relevant during the time we are seeking the donation ( like the notes memo field where we note to ourselves when we called, what they said, did they need something in writing) and some are only relevant during and after the auction (whether we assigned it to the live or silent auction, the item number, the amount the item is valued at, the winning bidder number and bid amount) I really don’t want to use only one table.

But—if I can find the record in the donor possibilities table, select the fields for donor name, address, city, state, zip, contact, phone, item, and volunteer then move that into to a new records in the auction items table (where I fill in updated item info, then later winning bidder numbers and amounts) and deleting the record in donor possibilities by just clicking a button on the form, it would make things much easier and faster for me than selecting the entire records, cutting it, replying yes to the info about losing the record in the donor possibilities table, closing the form, opening a different form, selecting an empty records, pasting, and then answering yes that it’s OK that not all field match to just paste the ones that do.

Again, my apologies for the length—but I’ve tried twice to get an answer for this with brief descriptions to no avail, and it’s now too late to help me this year, but if I can still get an answer it will help me next year. My most gracious thanks to anyone that helps. I know enough to know the kinds of things code and Access can do, but have little opportunity to actually learn how to make these things happen. It's so helpful to be able to ask others.

[NeoPa]

I will look at this when I have a little more time. I appreciate that many people find expressing the pertinent details succinctly and unambiguously very hard, so this explanation will be fine. Clearly you've gone to a great effort to be as clear as you can be, so I'm happy to read through it to understand as well as I can. It may take a while to have the time though. Probably later today, but I will certainly look sometime soon. From there I hope to be able to guide you to a solution that you're comfortable with.

[NeoPa]

Right. As we are now talking of moving data both ways, but at the same time losing certain fields as they may be relevant for one but not the other, let me first start by suggesting you consider the possibility of adding certain fields to the table they are not relevant to, simply to facilitate moving them back later, in preparation for the next year. These fields needn't be included on the form that you use to display or edit these records, but thay can be kept in the background in reserve for when they next may be copied across. This is not necessary for your main process to work, but I think it may prove worth considering to make the job a little easier. You'd need to decide which fields would be useful to have the following year.

On to the main process. SQL can be used to move the data, or Recordset processing can be. SQL is often used for handling multiple records at once, which it does well, but can be used for a single record too. Moving data (as opposed to copying it) is done in a database as a two-stage process.

1. Create a new record in the Destination table with the details taken from the Source.
2. Delete the record from the Source table.

To do this of course, you'd need to specify each field to be copied across. In SQL you can specify a * to mean all fields, but that will only work if the fields are the same in both tables. IE. Not something we can use in this case.

SQL commands stored in a string can be executed in sequence behind the Command Button, so the whole moving process can be executed seamlessly once the Command Button is clicked.

Does this sound like the sort of thing you're after? If so, then please refer back to post #10 where I suggested some things we'd need before we could progress. Details of how your data is structured are indispensable at this stage. We cannot proceed meaningfully without this information.