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.