New Database or New Category?

[GCS]

Last year I designed a database for my position as development director at a Christian school. The database is used to run our school auction and keep track of donors and donations. My question is which way is the best way to handle the new auction donations for this year. Should I make a copy of the whole database and clear the information from the donations table and use the new database or make new tables in the current database and categorize them by year?



I have several tables that will be used every year such as Contacts, Donation Category, Donation Status, etc., so the only tables that change or the items/donations.

As you can tell, I am quite the novice at this and need some help knowing which way to go. I know eventually I may want to run reports that would pull from each of the auctions so I want to be sure I do this right.

[jzwp11]

Should I make a copy of the whole database and clear the information from the donations table and use the new database or make new tables in the current database and categorize them by year?

Neither.

You would alter your current design to account for multiple auctions, but in order to help you do that we need to understand your organization's current process involving items and donations.

We would also need more detail on your current table structure (tables, field names & relationships between the tables).

[GCS]

All items (donations) are entered into the LOTS FORM/TABLE.
Some item (donations) are their own lot and some items are bundled with others to create a lot.
The LOTS FORM has an ITEMS subform in it.
The LOTS form has Lot#(Lot Table Key), Lot Name, Lot Value, School Family (Drop down menu from FAMILY table - Family that brought in the donation), Donor (From the CONTACTS table - Business that made the donation), Category (Drop down menu to allow us to categorize the type of donation- Sports- Travel - Dining, etc), Status (Drop down menu shows where in the process the donation is - CATALOGED (done), HOLD (holding to bundle with something else), ISS (has an issue that needs taken care of), etc), and a Check Box for Bundles (This box gets checked if the Lot is a bundle of different items)

The ITEMS table/subform has all the same fields except it also has an ITEM #(Auto number) field which is the Key for the table.

Since the majority of individual donations are their own lot, I enter the donation into the LOT form and leave the BUNDLE check box empty. IF the lot is made from a bundle of individual donations, I enter the lot information in the lot form, check the bundle box and then enter the individual item information in the subform. At the end of the day I do a back up and then run an update query that takes each record from the LOT table with a null in the bundle box and adds it to the ITEMS table.

I know this way is redundant but being a complete novice this was the only way I could figure out to be able track the donations in the way I needed to track them. I need to be able to track the donations by donor so I can send a tax receipt. I also need to track them by school family as we have contests between our classes to bring in more donations.

There are several other fields that I have but this is the main processing procedure. I know the design is VERY far from perfect but this is the only way I could figure out.

[jzwp11]

The ITEMS table/subform has all the same fields except it also has an ITEM #(Auto number) field which is the Key for the table.

The Items table has all the same fields as what? Duplicating information that is already in another table violates normalization rules.

At this point, I am only concerned with the tables. Could you present your table information like this?

LOTS
-Lot#(Lot Table Key),
-Lot Name
-Lot Value
-School Family (Drop down menu from FAMILY table - Family that brought in the donation),
-Donor (From the CONTACTS table - Business that made the donation)
-Category (Drop down menu to allow us to categorize the type of donation- Sports- Travel - Dining, etc),
-Status (Drop down menu shows where in the process the donation is - CATALOGED (done), HOLD (holding to bundle with something else), ISS (has an issue that needs taken care of), etc), and a
-Check Box for Bundles

When you mention drop down menus above, do you have the field in the table setup as a combo/list box or are you referring to a drop down on a form or both?

Some general recommendations:

1. Do not use spaces or special characters (&,#,!, etc.) in your table or field names (they can cause issues when building queries or when you use code)
2. Do not used reserved words as field or table names. This site has a list of reserved words and symbols
3. Do not use lookups (list/combo boxes) at the table level; they are best left for forms. This site explains why table-level lookups are not a good idea.