I need to reduce the number of fields in my database. The database is for a charity which I successfully imported from Panorama 6. That database worked extremely well in Panorama 6. However, after I switched to Panorama X, I got the ‘spinning colour wheel of death’ and after contacting ProVUE support back in September, I was told that I had too many fields and should redesign my database to use vastly fewer fields, either by separating it into multiple databases or combining information in multiple fields. As I am a lecturer, I have not had time to redesign my database until now.
The database contains 3,256 records (a bloated mailing list with standard mailing list information – names, addresses, phone numbers, email addresses, etc.) with 859 fields. The reason for the high number of fields is that the database, in addition to the mailing list information, contains information on donations (for a much smaller sub-set of the total number of records). The information on donations is for each month going back to 1985 with additional fields for monthly amounts, yearly number of donations, yearly totals and yearly averages (16+ fields/year). The database also contains correspondence information (received, sent, date) for a small number of the total records.
I need to import donation information each month from a csv/tsv text file which I download from a server. I would like to have one place (i.e., different forms on the mailing list/main database) where I can both enter/edit monthly financial information as well as any correspondence. To do that, I need to see the name and address of the individual/organisation making the donation or with whom there is correspondence to process. I would also like to be able to have a form with a table with the monthly financial information for the last 10 years indicated by columns for each year and rows for each month. Additionally, for each donor, I would also like to be able to see the figures for the last 12 month period (not just calendar year counts, totals and averages) for: 1) number of donations, 2) the total amount of the donations and 3) the average donation . There are also several categories of donors in the database and I would like to be able to search/select a given category and see the information (counts, totals, averages) for that specific category.
Now to my questions. I am not a developer, but I’m not a novice with Panorama either. I’m looking for a fairly easy/straightforward solution, but it doesn’t have to be ‘pretty’. How might I reduce the number of fields? Could I somehow put the donations, and other figures for a given year into an array of some kind in order to reduce the number of fields? If so, how do I then easily access the information stored in the arrays. If that won’t work, is there an easy way to break the database into multiple databases and use lookup functions to pull information from the separate databases to display on the mailing list database? I had thought of creating separate databases for each year with the financial information for that year. Would that work? How do I easily access that information while in the main (mailing list) database? Will having multiple databases (minimum of 11) open at the same time impact memory use/speed? Is there a way to simultaneously open multiple (associated) databases when you open one (main) database, so that you can perform lookup operations while in the main database? Is there a way to lookup information from multiple databases at one time, or do the lookups each need to be separate operations?
Any other suggestions you have would be very gratefully received. Thank you in advance.