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
|
|||
|
|||
Mebership DB Question
I have a mebership DB I created. I am haveing and issue with how I would
note that they paid for a certin year. Say they paid this year but not the next but to again the following. Would a list of check boxes do this or is there a way that at the end of the year I could copy all 2008 members over to an archive table keeping all the information in the main table and then clear out the year and member card number field so that if they paid for 2009 you could search for them and add the new card # and 2009 for the year. If not would sub forms with years and card #'s work better. |
#2
|
|||
|
|||
Mebership DB Question
On Wed, 12 Mar 2008 11:33:02 -0700, Michelle
wrote: I have a mebership DB I created. I am haveing and issue with how I would note that they paid for a certin year. Say they paid this year but not the next but to again the following. Would a list of check boxes do this or is there a way that at the end of the year I could copy all 2008 members over to an archive table keeping all the information in the main table and then clear out the year and member card number field so that if they paid for 2009 you could search for them and add the new card # and 2009 for the year. If not would sub forms with years and card #'s work better. WHOAAAA!! You're WAY off track here. You should have a table of Members, related one to many to a table of Payments. The Payments table would have a memberID, a field for the date paid, amount paid, perhaps for what the payment was for (2007 dues let's say). Bear in mind: you know what a card # is, what your membership policies are, etc. We do not. I can make some guesses (that the Card # is the Primary Key of the members table) but that's only a guess and if I rely on it I'll mislead you! -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Mebership DB Question
John,
I do a lot of membership DB work. I often thought about doing membership ("payment") history the way you describe but instead just make a column for each year in the main table. One reason is that under your method I don't see an easy way to query based on memberhship history. For example "Show me everybody who was a member in both 2005 and 2006 but not in 2007." Or the simpler but common "Show me all 2007 members who haven't yet renewed for 2008" Do you know a straightforward way to do that with the structure that you described? Sincerely, Fred "John W. Vinson" wrote: On Wed, 12 Mar 2008 11:33:02 -0700, Michelle wrote: I have a mebership DB I created. I am haveing and issue with how I would note that they paid for a certin year. Say they paid this year but not the next but to again the following. Would a list of check boxes do this or is there a way that at the end of the year I could copy all 2008 members over to an archive table keeping all the information in the main table and then clear out the year and member card number field so that if they paid for 2009 you could search for them and add the new card # and 2009 for the year. If not would sub forms with years and card #'s work better. WHOAAAA!! You're WAY off track here. You should have a table of Members, related one to many to a table of Payments. The Payments table would have a memberID, a field for the date paid, amount paid, perhaps for what the payment was for (2007 dues let's say). Bear in mind: you know what a card # is, what your membership policies are, etc. We do not. I can make some guesses (that the Card # is the Primary Key of the members table) but that's only a guess and if I rely on it I'll mislead you! -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Mebership DB Question
On Wed, 12 Mar 2008 14:56:05 -0700, Fred
wrote: John, I do a lot of membership DB work. I often thought about doing membership ("payment") history the way you describe but instead just make a column for each year in the main table. One reason is that under your method I don't see an easy way to query based on memberhship history. For example "Show me everybody who was a member in both 2005 and 2006 but not in 2007." Or the simpler but common "Show me all 2007 members who haven't yet renewed for 2008" Do you know a straightforward way to do that with the structure that you described? Well, you'll be getting a bit deeper into query design in SQL than is common in Access but it's quite doable: SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2005) AND EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2006) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007); SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2008); -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Mebership DB Question
John,
Thanks for the response. I'm weak on SQL, and also on mixing it with Access. The only way I know to use such an sql statement in access is to join the tables in the relationship screen, create a query with the already-joined tables, and then edit the sql view statement to what I plan to use, and then see if Access accepts it. And if not, figure that I displeased the Access god and give up. I tried doing this with what you wrote and it gave me a vague error message. Any pointers / thoughts would be appreciated, otherwise I'll just use what you wrote in a longer term learning process. Thanks again Fred "John W. Vinson" wrote: On Wed, 12 Mar 2008 14:56:05 -0700, Fred wrote: John, I do a lot of membership DB work. I often thought about doing membership ("payment") history the way you describe but instead just make a column for each year in the main table. One reason is that under your method I don't see an easy way to query based on memberhship history. For example "Show me everybody who was a member in both 2005 and 2006 but not in 2007." Or the simpler but common "Show me all 2007 members who haven't yet renewed for 2008" Do you know a straightforward way to do that with the structure that you described? Well, you'll be getting a bit deeper into query design in SQL than is common in Access but it's quite doable: SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2005) AND EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2006) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007); SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2008); -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Mebership DB Question
I did end up doing something similar to what you had suggested.
I created a second table and related it back to the first based on Member Num. Card # can not be the primary key as you get a new one each year and each year they start with 001 and up. Thanks for the help. I did read what you wrote to Fred and that will also help me for when I start to do my reports section but I do have one question. Can you make a query that will ask you the year instead of having to have it listed in the code? "John W. Vinson" wrote: On Wed, 12 Mar 2008 11:33:02 -0700, Michelle wrote: I have a mebership DB I created. I am haveing and issue with how I would note that they paid for a certin year. Say they paid this year but not the next but to again the following. Would a list of check boxes do this or is there a way that at the end of the year I could copy all 2008 members over to an archive table keeping all the information in the main table and then clear out the year and member card number field so that if they paid for 2009 you could search for them and add the new card # and 2009 for the year. If not would sub forms with years and card #'s work better. WHOAAAA!! You're WAY off track here. You should have a table of Members, related one to many to a table of Payments. The Payments table would have a memberID, a field for the date paid, amount paid, perhaps for what the payment was for (2007 dues let's say). Bear in mind: you know what a card # is, what your membership policies are, etc. We do not. I can make some guesses (that the Card # is the Primary Key of the members table) but that's only a guess and if I rely on it I'll mislead you! -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Mebership DB Question
On Thu, 13 Mar 2008 04:52:02 -0700, Fred
wrote: John, Thanks for the response. I'm weak on SQL, and also on mixing it with Access. The only way I know to use such an sql statement in access is to join the tables in the relationship screen, create a query with the already-joined tables, and then edit the sql view statement to what I plan to use, and then see if Access accepts it. And if not, figure that I displeased the Access god and give up. I tried doing this with what you wrote and it gave me a vague error message. Any pointers / thoughts would be appreciated, otherwise I'll just use what you wrote in a longer term learning process. You can open any query (or create a new query) and select SQL from the three options on the leftmost tool in the query design toolbar (or use the menu, View... SQL). So you can start the query in the query grid, and go to SQL view when you need to do something fancy like a triple subquery. It's tough learning all the rules - the Access god is evidently rather like the Jehovah of the Old Testament, with a great many rules and no mercy! If you want some help with the particular query, feel free to post the field defs of your table, the SQL that you tried, and the text of the error message. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Mebership DB Question
On Thu, 13 Mar 2008 05:07:01 -0700, Michelle
wrote: I did end up doing something similar to what you had suggested. I created a second table and related it back to the first based on Member Num. Card # can not be the primary key as you get a new one each year and each year they start with 001 and up. Thanks for the help. I did read what you wrote to Fred and that will also help me for when I start to do my reports section but I do have one question. Can you make a query that will ask you the year instead of having to have it listed in the code? Sure!! In fact such Parameter Queries are universal, and should always be used in preference to hardcoding criteria in the query. Instead of putting 2007 on the criteria line, you can just put [Enter year:] Whatever is in the brackets will be used as a prompt. Even better, you can create an unbound Form - frmCrit let's call it - with a textbox txtYear. Your criterion would then be [Forms]![frmCrit]![txtYear] and you would put a command button on frmCrit to open whatever report or form is based on the query. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Mebership DB Question
John,
Thanks again for the response and offer. I made a test DB intended to exactly match that implied by your SQL: Table: Members Text Field: Lname PK Autonumber field: MemberID Table: Payments Long Integer Field: MemberID Date field: PaymentDate Entered some data (including a couple people with payment entries for 2007 dates and none for 2008 dates.) and then literally pasted in your second sql statment as the total SQL statement in the SQL view for that query: SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2008); No error message. It runs and shows the correct fields, but returns zero records. Any ideas on what I did wrong? "Fred" wrote: John, Thanks for the response. I'm weak on SQL, and also on mixing it with Access. The only way I know to use such an sql statement in access is to join the tables in the relationship screen, create a query with the already-joined tables, and then edit the sql view statement to what I plan to use, and then see if Access accepts it. And if not, figure that I displeased the Access god and give up. I tried doing this with what you wrote and it gave me a vague error message. Any pointers / thoughts would be appreciated, otherwise I'll just use what you wrote in a longer term learning process. Thanks again Fred "John W. Vinson" wrote: On Wed, 12 Mar 2008 14:56:05 -0700, Fred wrote: John, I do a lot of membership DB work. I often thought about doing membership ("payment") history the way you describe but instead just make a column for each year in the main table. One reason is that under your method I don't see an easy way to query based on memberhship history. For example "Show me everybody who was a member in both 2005 and 2006 but not in 2007." Or the simpler but common "Show me all 2007 members who haven't yet renewed for 2008" Do you know a straightforward way to do that with the structure that you described? Well, you'll be getting a bit deeper into query design in SQL than is common in Access but it's quite doable: SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2005) AND EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2006) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007); SELECT * FROM Members WHERE EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2007) AND NOT EXISTS(Select MemberID From Payments WHERE Year(PaymentDate) = 2008); -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Mebership DB Question
Michelle,
See if this helps: http://office.microsoft.com/en-us/te...CT102144001033 This is a database on Microsofts template page. Seems like what your looking for. "Michelle" wrote: I did end up doing something similar to what you had suggested. I created a second table and related it back to the first based on Member Num. Card # can not be the primary key as you get a new one each year and each year they start with 001 and up. Thanks for the help. I did read what you wrote to Fred and that will also help me for when I start to do my reports section but I do have one question. Can you make a query that will ask you the year instead of having to have it listed in the code? "John W. Vinson" wrote: On Wed, 12 Mar 2008 11:33:02 -0700, Michelle wrote: I have a mebership DB I created. I am haveing and issue with how I would note that they paid for a certin year. Say they paid this year but not the next but to again the following. Would a list of check boxes do this or is there a way that at the end of the year I could copy all 2008 members over to an archive table keeping all the information in the main table and then clear out the year and member card number field so that if they paid for 2009 you could search for them and add the new card # and 2009 for the year. If not would sub forms with years and card #'s work better. WHOAAAA!! You're WAY off track here. You should have a table of Members, related one to many to a table of Payments. The Payments table would have a memberID, a field for the date paid, amount paid, perhaps for what the payment was for (2007 dues let's say). Bear in mind: you know what a card # is, what your membership policies are, etc. We do not. I can make some guesses (that the Card # is the Primary Key of the members table) but that's only a guess and if I rely on it I'll mislead you! -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|