It's not clear what you mean by "compare data on the last day of two different months".
Do you want to compare data for all donations for the Month of Feburary for two different years? Perhaps monthly totals or averages?
If that's what you have in mind, I don't see any need for working with multiple copies of data nor using a spread sheet program with exported data.
Yes, I want to compare data from two different months. Usually the months will be in different years. Some finds are easy, such as total donations in a range of dates. That's because each donation is a separate record that contains date and amount fields. It's easy to sum all donations in a date range.
But it's not easy if I want to find the number of active members on a certain date in the past, say the last day of Feb 2012. The contact record does have a field for membership status, but the only date fields on the contact record are for Date Record Created, Date Record Last Modified, and Month (and Year) Membership Expires.
In order to do such a search, I'd want to find all Active Members within a date range (assuming date-range can be narrowed to a single date). Trouble is, none of those date fields in a contact record seems relevant for such a search. The only alternative I see is to pull up a copy of the file archived on the last day of the month in question, then count the number of Active Member records.
So I think I'm left with the question, How can I change what I'm doing so tracking membership will be easier in the future? One simple option: Find the membership numbers at the end of each month, then export them to a spreadsheet. Accumulating future data every month in a single spreadsheet should be more efficient than case-basis searches of archived FMP files. Maybe there's a better way?
How can I change what I'm doing so tracking membership will be easier in the future?
I'm a bit confused here. You talk about comparing things on a monthly basis and then talk about specifying a specific date--such as the last day in February. If you want this for a specific day, your monthly exports will not work as this records member status on a month to month basis.
Assuming that you really need a monthly record of member status...
Add a new table to your database and use it to log membership status on a monthly basis. This can be record with three fields, the memberID, the status and the date for the first day of that month.
A script can use import records to import that data from all records (or a selected found set if you want to omit records for certain status types such as a member that has died...) in your membership table into this new table. If you host this file with FileMaker server a server schedule can control a script to do the monthly updates.
You can then query this new table for month specific membership data.
I want to do various comparisons of data. Finding the desired data is easy in some cases, hard in others. I needed help only with the hard cases. I like your suggestion of adding a new table that can store selected snapshots of data at a chosen point in time. Thanks.