If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Membership database updates
I have created a membership database for our local branch of a charity and
designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#2
|
|||
|
|||
Membership database updates
Why not just name your table "tblMembers" or "Members" without storing any
data values in the table name? -- Duane Hookom Microsoft Access MVP "Pennington" wrote: I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#3
|
|||
|
|||
Membership database updates
Duane is correct. Having multiple tables with the same data is never a good
idea. You are already seeing the side effects. If you need to separate the data by quarter (can't imagine why), you can add a field to your table that defines the Year and Quarter. I have such a field in a table in one of my apps. I carry it as Text in this format: yyyyq (for first quarter 2008 it would be 20081. I use an input mask and format of @@@@-@ -- Dave Hargis, Microsoft Access MVP "Pennington" wrote: I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#4
|
|||
|
|||
Membership database updates
Of course the other folks are correct, but you might want to start with a
couple fundamental questions: Is membership status something that is tied to a particular quarter (i.e. they are members for one quarter at a time) or is it just that you received updates quarterly but membership is for a longer period (e.g. for 1 year, until the end of the calendar year, indefinitely until modified etc.) What specifically are you recording? Obviously, "membership", but that's too vague. Answer in the context in the previous and next paragraph. I run membership databases for a lot of organizations. For most situations the best solution is table with fields for the other information such as membership status. The table can either be such that the presence of a record means somehtin g(like they are a current member) or, preferably, be a list of current and past members with fields that contain relevant membership information. Is it just "who is currently a member". "Pennington" wrote: I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#5
|
|||
|
|||
Membership database updates
I'm guessing that when you receive your list, you don't know if the members
in your current table are also in the new list. If you do have a way of identifying members (other than name and Date of birth, which is never 100% reliable) like a unique membership number, we can help you to make a query which sets a CurrentMember tickbox to True if the member appears in your new list and all the other members in your Current Table to False. If you need to Archive your old records (because there are loads of names), you can use an Append query to add all the 'False member's to an archive table which will have a Year field so that you know which year they were members. The False members will be deleted from your main table using a simple delete query. The Archive Table is created by copy/pasting your current table (without the data). Add a Year field to both tables to contain the year number. This method means that you have the option to retrieve a member from the Archive table and append him back if you still want his data and even use a Union Query to unite the Archive and normal table so that you can view eg a membership history of your charity. You can now use your table without any name changes for reports and queries. You can also copy and pasted of some of your reports and queries, changing their name to eg QryArchiveMembers, RptArchivePayments adjusting the table in the query grid to your Archive table and adjusting the Record Source of the reports to point to these 'Archive' queries The only alteration you will need to do to reports is to add a Year field and group them by that. Evi "Pennington" wrote in message ... I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#6
|
|||
|
|||
Membership database updates
Yes you are correct. Each member does have a unique membership number which I
make the primary key. There is also a field Date Joined but unfortunately the new list I receive has new members but not suspended members who may return or those who have resigned. From your explanation I think it is what I am seeking but I am not sure I understand it completely. Could your solution mean I have one list of members with a field for indicating "Date Removed" so that after importing the updated list into a new table I run a query that adds new members to the members table and for members not found in the update it adds the current month/year in the Date Removed field. Some of those members that do not appear in an updated list may simply be suspended because they have not paid and are reinstated once they have paid up to date so I need to keep their details on record. I don't think I need to archive the data as we have only 1300 members in the branch and we don't expect it to go beyond 10,000. In a Report that I have created using the wizard the record source field is blank and when I have added a different source record from the one on which it was created I find it does not work. Why is this? I do need help writing these queries as I am not an expert in Access, just a guy who volunteered to do the job as I have some experience in using it. "Evi" wrote: I'm guessing that when you receive your list, you don't know if the members in your current table are also in the new list. If you do have a way of identifying members (other than name and Date of birth, which is never 100% reliable) like a unique membership number, we can help you to make a query which sets a CurrentMember tickbox to True if the member appears in your new list and all the other members in your Current Table to False. If you need to Archive your old records (because there are loads of names), you can use an Append query to add all the 'False member's to an archive table which will have a Year field so that you know which year they were members. The False members will be deleted from your main table using a simple delete query. The Archive Table is created by copy/pasting your current table (without the data). Add a Year field to both tables to contain the year number. This method means that you have the option to retrieve a member from the Archive table and append him back if you still want his data and even use a Union Query to unite the Archive and normal table so that you can view eg a membership history of your charity. You can now use your table without any name changes for reports and queries. You can also copy and pasted of some of your reports and queries, changing their name to eg QryArchiveMembers, RptArchivePayments adjusting the table in the query grid to your Archive table and adjusting the Record Source of the reports to point to these 'Archive' queries The only alteration you will need to do to reports is to add a Year field and group them by that. Evi "Pennington" wrote in message ... I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#7
|
|||
|
|||
Membership database updates
The reason your report's record source is blank is beccause the table on
which they were based no longer exists. If you click next to RecordSource you can then choose a different table or query from the list on which to base your report. When you stop renaming your tables, this will no longer happen. If you don't have to remove non-current members then it is even easier - no need for an archive table. You definitely *don't* need a different field for current members, just filter using your DateRemoved field or even a tickbox Yes/No field if you need also need some other way to indicate someone has left. Your unique membership number will ensure that you don't accidentally add member twice. I really don't understand why you have been renaming tables. Is it because you need to look back to who was your member on any one year? I can see why that could be tricky if a member is suspended and then re-instated but there will be a way of doing that if it is needed. You will create a query based on your members table. It can have all the same fields if you wish. You could call it QryCurrentMembers In the criteria line under Date Removed, type Is Null (for past members your criteria will be Is Not Null) In Design View of your report, click next to Record Source and choose this query instead of your Table. Which are the queries you are unsure about creating? We can 'talk' you through them if we know what you don't already know. Evi "Pennington" wrote in message ... Yes you are correct. Each member does have a unique membership number which I make the primary key. There is also a field Date Joined but unfortunately the new list I receive has new members but not suspended members who may return or those who have resigned. From your explanation I think it is what I am seeking but I am not sure I understand it completely. Could your solution mean I have one list of members with a field for indicating "Date Removed" so that after importing the updated list into a new table I run a query that adds new members to the members table and for members not found in the update it adds the current month/year in the Date Removed field. Some of those members that do not appear in an updated list may simply be suspended because they have not paid and are reinstated once they have paid up to date so I need to keep their details on record. I don't think I need to archive the data as we have only 1300 members in the branch and we don't expect it to go beyond 10,000. In a Report that I have created using the wizard the record source field is blank and when I have added a different source record from the one on which it was created I find it does not work. Why is this? I do need help writing these queries as I am not an expert in Access, just a guy who volunteered to do the job as I have some experience in using it. "Evi" wrote: I'm guessing that when you receive your list, you don't know if the members in your current table are also in the new list. If you do have a way of identifying members (other than name and Date of birth, which is never 100% reliable) like a unique membership number, we can help you to make a query which sets a CurrentMember tickbox to True if the member appears in your new list and all the other members in your Current Table to False. If you need to Archive your old records (because there are loads of names), you can use an Append query to add all the 'False member's to an archive table which will have a Year field so that you know which year they were members. The False members will be deleted from your main table using a simple delete query. The Archive Table is created by copy/pasting your current table (without the data). Add a Year field to both tables to contain the year number. This method means that you have the option to retrieve a member from the Archive table and append him back if you still want his data and even use a Union Query to unite the Archive and normal table so that you can view eg a membership history of your charity. You can now use your table without any name changes for reports and queries. You can also copy and pasted of some of your reports and queries, changing their name to eg QryArchiveMembers, RptArchivePayments adjusting the table in the query grid to your Archive table and adjusting the Record Source of the reports to point to these 'Archive' queries The only alteration you will need to do to reports is to add a Year field and group them by that. Evi "Pennington" wrote in message ... I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#8
|
|||
|
|||
Membership database updates
Many thanks, this is most helpful.
Yes you are correct the Reports do have a source record but the Charts do not even though the query on which they are based still exists. The reason I was renaming tables is that I built the database using the Dec 07 members list I received. I received an update in Feb 08 and created a new table. Although I could easily establish who the new members were from the DateJoined field I used the "Unmatched Query Wizard" to find the members that were not in the list as there is no MembersRemoved field in the lists I am sent (I have asked for this data but as yet I am not being sent it) Now I have gone back to the first table I created and have created a new query as you suggested and presumably I simply produce another copy with different criteria depending on whether I want a list of new members or a list of ex-members. Now, how do I import the updated lists for Feb and Mar as there is no MemberRemoved field? Even if I create such a field before I import it, it will be blank. If I import the data into the existing table I won't know if any members have been removed from the later list "Evi" wrote: The reason your report's record source is blank is beccause the table on which they were based no longer exists. If you click next to RecordSource you can then choose a different table or query from the list on which to base your report. When you stop renaming your tables, this will no longer happen. If you don't have to remove non-current members then it is even easier - no need for an archive table. You definitely *don't* need a different field for current members, just filter using your DateRemoved field or even a tickbox Yes/No field if you need also need some other way to indicate someone has left. Your unique membership number will ensure that you don't accidentally add member twice. I really don't understand why you have been renaming tables. Is it because you need to look back to who was your member on any one year? I can see why that could be tricky if a member is suspended and then re-instated but there will be a way of doing that if it is needed. You will create a query based on your members table. It can have all the same fields if you wish. You could call it QryCurrentMembers In the criteria line under Date Removed, type Is Null (for past members your criteria will be Is Not Null) In Design View of your report, click next to Record Source and choose this query instead of your Table. Which are the queries you are unsure about creating? We can 'talk' you through them if we know what you don't already know. Evi "Pennington" wrote in message ... Yes you are correct. Each member does have a unique membership number which I make the primary key. There is also a field Date Joined but unfortunately the new list I receive has new members but not suspended members who may return or those who have resigned. From your explanation I think it is what I am seeking but I am not sure I understand it completely. Could your solution mean I have one list of members with a field for indicating "Date Removed" so that after importing the updated list into a new table I run a query that adds new members to the members table and for members not found in the update it adds the current month/year in the Date Removed field. Some of those members that do not appear in an updated list may simply be suspended because they have not paid and are reinstated once they have paid up to date so I need to keep their details on record. I don't think I need to archive the data as we have only 1300 members in the branch and we don't expect it to go beyond 10,000. In a Report that I have created using the wizard the record source field is blank and when I have added a different source record from the one on which it was created I find it does not work. Why is this? I do need help writing these queries as I am not an expert in Access, just a guy who volunteered to do the job as I have some experience in using it. "Evi" wrote: I'm guessing that when you receive your list, you don't know if the members in your current table are also in the new list. If you do have a way of identifying members (other than name and Date of birth, which is never 100% reliable) like a unique membership number, we can help you to make a query which sets a CurrentMember tickbox to True if the member appears in your new list and all the other members in your Current Table to False. If you need to Archive your old records (because there are loads of names), you can use an Append query to add all the 'False member's to an archive table which will have a Year field so that you know which year they were members. The False members will be deleted from your main table using a simple delete query. The Archive Table is created by copy/pasting your current table (without the data). Add a Year field to both tables to contain the year number. This method means that you have the option to retrieve a member from the Archive table and append him back if you still want his data and even use a Union Query to unite the Archive and normal table so that you can view eg a membership history of your charity. You can now use your table without any name changes for reports and queries. You can also copy and pasted of some of your reports and queries, changing their name to eg QryArchiveMembers, RptArchivePayments adjusting the table in the query grid to your Archive table and adjusting the Record Source of the reports to point to these 'Archive' queries The only alteration you will need to do to reports is to add a Year field and group them by that. Evi "Pennington" wrote in message ... I have created a membership database for our local branch of a charity and designed various queries and reports to analyze particular trends. The source table I called Members Jan 08 but I receive updates every quarter and when I import the new membership list naming it Members Mar 08 and change references to Jan 08 to Mar 08 in the queries and reports several of them fail to work. I have had to recreate the queries and reports all over again. I don't want to have to do this every quarter so is there an easier way of doing this like running a find/replace query? |
#9
|
|||
|
|||
Membership database updates
If you don't mind bluntness that only intended to try to be helpful.
I run membership data / dabases for 6 organizations, 2 of them for over 20 years. You never explained the key points, but they are finally starting to come out. Now it looks like there is something fundamentally wrong with what's happening. It sounds your organization is running two databases that are, doing the same thing. And they are sending you "updates" in the worst possible way (just the whole list, with no useful fields about additions / deletions) and then you are trying to go through contortions trying to deal with this fundamentally mixed up situations. So, your efforts to deal with that mess are a good tough workout for learning Access, but is much more difficult that it should be. The best thing would be for your organization to decide who is running the membership database, and for that function there should only be one database. All CHANGES should then get entered in THAT database. And all data and reports that people need should come from that database. Sincerley, Fred "Pennington" wrote: Many thanks, this is most helpful. Yes you are correct the Reports do have a source record but the Charts do not even though the query on which they are based still exists. The reason I was renaming tables is that I built the database using the Dec 07 members list I received. I received an update in Feb 08 and created a new table. Although I could easily establish who the new members were from the DateJoined field I used the "Unmatched Query Wizard" to find the members that were not in the list as there is no MembersRemoved field in the lists I am sent (I have asked for this data but as yet I am not being sent it) Now I have gone back to the first table I created and have created a new query as you suggested and presumably I simply produce another copy with different criteria depending on whether I want a list of new members or a list of ex-members. Now, how do I import the updated lists for Feb and Mar as there is no MemberRemoved field? Even if I create such a field before I import it, it will be blank. If I import the data into the existing table I won't know if any members have been removed from the later list "Evi" wrote: The reason your report's record source is blank is beccause the table on which they were based no longer exists. If you click next to RecordSource you can then choose a different table or query from the list on which to base your report. When you stop renaming your tables, this will no longer happen. If you don't have to remove non-current members then it is even easier - no need for an archive table. You definitely *don't* need a different field for current members, just filter using your DateRemoved field or even a tickbox Yes/No field if you need also need some other way to indicate someone has left. Your unique membership number will ensure that you don't accidentally add member twice. I really don't understand why you have been renaming tables. Is it because you need to look back to who was your member on any one year? I can see why that could be tricky if a member is suspended and then re-instated but there |
#10
|
|||
|
|||
Membership database updates
I don't mind bluntness but I did explain that the list I am handling is for a
local branch of a charity. The main database is in HQ and they send me a list of members in my branch. HQ can do all sorts of things with the database but the branch needs to understand its members so that is why I want to look at local trends. I was able to create all the necessary Reports and Charts but the difficulty I have is when I receive an update from HQ. that omits ex-members but may have address changes and other changes that means I cannot use the members table I have currently. Perhaps what I need to do is a) import the update into a new table and name it NewMembers, b) run the "unmatched query wizard" with the CurrentMembers table to identify the ex-members , c) check the MembersRemoved field and delete the rest d) merge this list with the NewMembers table e) delete the CurrentMembers table and rename the NewMembers table as CurrentMembers so that the Reports and Charts work. Incidentally, when I rename a table does it automatically update all references to that table because I tried this and it didn't seem to work. If you can help me write a query to do this in less steps it would be brilliant. "Fred" wrote: If you don't mind bluntness that only intended to try to be helpful. I run membership data / dabases for 6 organizations, 2 of them for over 20 years. You never explained the key points, but they are finally starting to come out. Now it looks like there is something fundamentally wrong with what's happening. It sounds your organization is running two databases that are, doing the same thing. And they are sending you "updates" in the worst possible way (just the whole list, with no useful fields about additions / deletions) and then you are trying to go through contortions trying to deal with this fundamentally mixed up situations. So, your efforts to deal with that mess are a good tough workout for learning Access, but is much more difficult that it should be. The best thing would be for your organization to decide who is running the membership database, and for that function there should only be one database. All CHANGES should then get entered in THAT database. And all data and reports that people need should come from that database. Sincerley, Fred "Pennington" wrote: Many thanks, this is most helpful. Yes you are correct the Reports do have a source record but the Charts do not even though the query on which they are based still exists. The reason I was renaming tables is that I built the database using the Dec 07 members list I received. I received an update in Feb 08 and created a new table. Although I could easily establish who the new members were from the DateJoined field I used the "Unmatched Query Wizard" to find the members that were not in the list as there is no MembersRemoved field in the lists I am sent (I have asked for this data but as yet I am not being sent it) Now I have gone back to the first table I created and have created a new query as you suggested and presumably I simply produce another copy with different criteria depending on whether I want a list of new members or a list of ex-members. Now, how do I import the updated lists for Feb and Mar as there is no MemberRemoved field? Even if I create such a field before I import it, it will be blank. If I import the data into the existing table I won't know if any members have been removed from the later list "Evi" wrote: The reason your report's record source is blank is beccause the table on which they were based no longer exists. If you click next to RecordSource you can then choose a different table or query from the list on which to base your report. When you stop renaming your tables, this will no longer happen. If you don't have to remove non-current members then it is even easier - no need for an archive table. You definitely *don't* need a different field for current members, just filter using your DateRemoved field or even a tickbox Yes/No field if you need also need some other way to indicate someone has left. Your unique membership number will ensure that you don't accidentally add member twice. I really don't understand why you have been renaming tables. Is it because you need to look back to who was your member on any one year? I can see why that could be tricky if a member is suspended and then re-instated but there |
Thread Tools | |
Display Modes | |
|
|