Mebership DB Question

[Michelle]

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.

 

[John W. Vinson]

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!

 

[Fred]

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]

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);

 

[Fred]

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

 

[Michelle]

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]

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]

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.

 

[Fred]

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?

 

[Stockwell43]

Michelle,

See if this helps
http://office.microsoft.com/en-us/templates/TC010185841033.aspx?CategoryID=CT102144001033

This is a database on Microsofts template page. Seems like what your looking
for.