 |
|
|
|
Next: Another startup problem
|
| Author |
Message |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 1) Posted: Fri Jan 29, 2010 7:25 pm
Post subject: How to implement payment for a social club ?
Archived from groups: microsoft>public>access (more info?)
|
|
|
We are developing a database for social club.
We have decided the payment method as follow:
Year 2010
========
Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be selected
For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012 checkbox will be
selected
Year 2011
========
Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected (Provided
that she hasn't paid for 2 or 3 yrs in Year 2010)
For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be selected
(Provided that she hasn't paid for 2 or 3 yrs in Year 2010)
Year 2012
========
Payment option - No Payment / Paid 1 Yr
For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected ((Provided
that she hasn't paid for Yr2012 in last 2 years)
Year 2013
========
It will provide those options as in Year 2010 (i.e. they are able to pay for
1 year, 2 year or 3 years again)
We are able to create a child table for "Year 2010". However, we would like
to seek your advice on how to implement for the coming two years (i.e. Year
2011 and Year 2012). Besides, how to implement year end processing - i.e.
reset payment to "No Payment", Money = $0.
Thanking you in anticipation |
|
| Back to top |
|
 | |
External
Since: Nov 26, 2008
Posts: 32
|
(Msg. 2) Posted: Fri Jan 29, 2010 7:25 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Patrick
If you want to get the best use of Access' relationally-oriented features
and functions, don't feed it 'sheet data.
When you say you have a table for Year2010, that implies you have (or would
have) another table for Year2011, etc. While you might need to handle this
situation that way if you were using a spreadsheet, you absolutely do NOT
want to do that in a relational database.
If "relational" and "normalization" are unfamiliar terms, plan on spending
some time coming up to speed on them before trying to get Access to work
well for you. Sure, you could try forcing Access to handle 'sheet data, but
you and Access will both regret it in the long run!
Good luck!
Regards
Jeff Boyce
Microsoft Access MVP |
|
| Back to top |
|
| |
External
Since: Nov 26, 2008
Posts: 32
|
(Msg. 3) Posted: Fri Jan 29, 2010 7:25 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Patrick
Be aware that these newsgroups are supported by volunteers, who provide free
assistance.
The code of conduct for these newsgroups prohibits advertising for fee-based
services.
Regards
Jeff Boyce
Microsoft Access MVP
"Patrick" wrote in message
> We are developing a database for social club.
>
> We have decided the payment method as follow:
>
> Year 2010
> ========
> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
> For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be selected
> For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012 checkbox will be
> selected
>
> Year 2011
> ========
> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected
> (Provided that she hasn't paid for 2 or 3 yrs in Year 2010)
> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be selected
> (Provided that she hasn't paid for 2 or 3 yrs in Year 2010)
>
> Year 2012
> ========
> Payment option - No Payment / Paid 1 Yr
> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected
> ((Provided that she hasn't paid for Yr2012 in last 2 years)
>
> Year 2013
> ========
> It will provide those options as in Year 2010 (i.e. they are able to pay
> for 1 year, 2 year or 3 years again)
>
>
> We are able to create a child table for "Year 2010". However, we would
> like to seek your advice on how to implement for the coming two years
> (i.e. Year 2011 and Year 2012). Besides, how to implement year end
> processing - i.e. reset payment to "No Payment", Money = $0.
>
> Thanking you in anticipation >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Aug 31, 2009
Posts: 6
|
(Msg. 4) Posted: Fri Jan 29, 2010 7:28 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Patrick,
I provide help with Access, Excel and Word applications. I would like to
offer to work with you to develop a set of tables to implement what you
describe here. My fee would be very reasonable. I have helped many customers
with their initial design of their database. If you want my help, contact
me.
Steve
santus.cut RemoveThis @penn.com
"Patrick" wrote in message
> We are developing a database for social club.
>
> We have decided the payment method as follow:
>
> Year 2010
> ========
> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
> For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be selected
> For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012 checkbox will be
> selected
>
> Year 2011
> ========
> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected
> (Provided that she hasn't paid for 2 or 3 yrs in Year 2010)
> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be selected
> (Provided that she hasn't paid for 2 or 3 yrs in Year 2010)
>
> Year 2012
> ========
> Payment option - No Payment / Paid 1 Yr
> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected
> ((Provided that she hasn't paid for Yr2012 in last 2 years)
>
> Year 2013
> ========
> It will provide those options as in Year 2010 (i.e. they are able to pay
> for 1 year, 2 year or 3 years again)
>
>
> We are able to create a child table for "Year 2010". However, we would
> like to seek your advice on how to implement for the coming two years
> (i.e. Year 2011 and Year 2012). Besides, how to implement year end
> processing - i.e. reset payment to "No Payment", Money = $0.
>
> Thanking you in anticipation >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 9, 2008
Posts: 23
|
(Msg. 5) Posted: Fri Jan 29, 2010 7:34 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
"Steve" wrote
> I provide help with Access, Excel and Word
> applications. I would like to offer to work
> with you to develop a set of tables to imple-
> ment what you describe here. My fee would
> be very reasonable. I have helped many customers
> with their initial design of their database. If
> you want my help, contact me.
Steve is the only poster in this newsgroup who consistently refuses to
recognize that it is for free peer-to-peer assistance, but treats it as
though it were just here so he can make contact to market his questionable
services. The answers that he provides here are of such inconsistent
quality that you should be cautious, no matter how low a fee he might quote
you.
Larry Linson
Microsoft Office Access MVP >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 4, 2008
Posts: 34
|
(Msg. 6) Posted: Fri Jan 29, 2010 8:11 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Nov 28, 2008
Posts: 14
|
(Msg. 7) Posted: Fri Jan 29, 2010 9:30 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
"Steve" wrote in message
>
> I provide help with Access, Excel and Word applications. I would like to
> offer to work with you to develop a set of tables to implement what you
> describe here. My fee would be very reasonable. I have helped many
> customers with their initial design of their database. If you want my
> help, contact me.
>
> Steve
> santus.Rmv DeleteThis @penn.com
Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.
John... Visio MVP >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 8) Posted: Sat Jan 30, 2010 1:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Dear Gina,
There is already a table of MEMBERS that contain details like: Surname /
First Name / Department / Phone Number / Employee ID / Indicator for payment
of year since 2005 (i.e. Yr2005, Yr2006, Yr2007, Yr2008 ...etc). Besides,
there is an indicator PAID indicating whether the member has paid for this
year or not.
Normally, during the year end, we will run Year End Processing - Reset the
Paid Indicator PAID from Yes to No and we will send a letter to those who
haven't paid in the second month of a year to remind them to pay the annual
fee.
Currently, we haven't created a child table for payment as we don't need to
record future year payment and amount of money being paid. These are new
requirements.
Thanks again
"Gina Whipp" wrote in message
> Patrick,
>
> You stated you are developing a database... What tables and fields do you
> have so far?
>
> --
> Gina Whipp
> 2010 Microsoft MVP (Access)
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> [URL="http://www.regina-whipp.com/index_files/TipList.htm"]http://www.regina-whipp.com/index_files/TipList.htm[/URL]
>
> "Patrick" wrote in message
> We are developing a database for social club.
>
> We have decided the payment method as follow:
>
> Year 2010
> ========
> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
> For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be selected
> For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012 checkbox will be
> selected
>
> Year 2011
> ========
> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected
> (Provided
> that she hasn't paid for 2 or 3 yrs in Year 2010)
> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be selected
> (Provided that she hasn't paid for 2 or 3 yrs in Year 2010)
>
> Year 2012
> ========
> Payment option - No Payment / Paid 1 Yr
> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected
> ((Provided
> that she hasn't paid for Yr2012 in last 2 years)
>
> Year 2013
> ========
> It will provide those options as in Year 2010 (i.e. they are able to pay
> for
> 1 year, 2 year or 3 years again)
>
>
> We are able to create a child table for "Year 2010". However, we would
> like
> to seek your advice on how to implement for the coming two years (i.e.
> Year
> 2011 and Year 2012). Besides, how to implement year end processing - i.e.
> reset payment to "No Payment", Money = $0.
>
> Thanking you in anticipation
>
> >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 10
|
(Msg. 9) Posted: Sat Jan 30, 2010 8:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
"Patrick" wrote in
> Dear Gina,
>
> There is already a table of MEMBERS that contain details like:
> Surname / First Name / Department / Phone Number / Employee ID /
> Indicator for payment of year since 2005 (i.e. Yr2005, Yr2006,
> Yr2007, Yr2008 ...etc). Besides, there is an indicator PAID
> indicating whether the member has paid for this year or not.
>
> Normally, during the year end, we will run Year End Processing -
> Reset the Paid Indicator PAID from Yes to No and we will send a
> letter to those who haven't paid in the second month of a year to
> remind them to pay the annual fee.
>
> Currently, we haven't created a child table for payment as we
> don't need to record future year payment and amount of money being
> paid. These are new requirements.
>
> Thanks again
>
Create a table with at least 3 columns, MemberID / PaymentYear/
PaymentAmt /, and maybe others like Payment_Comments / dtePaidOn.
Set the relationship between Members and Payments based on EmployeeID
Move the data from the YRnnnn columns to the new table, using append
queries.
Now with something like:
EmID / PayYear / Payment
123 2005 $26
123 2006 $26
123 2007 $26
You'll now be able to store payments for past, present and future
years, partial payments, etc.
And you can then query the results for Members with no entry for 2010
>
>
> "Gina Whipp" wrote in message
>> Patrick,
>>
>> You stated you are developing a database... What tables and
>> fields do you have so far?
>>
>> --
>> Gina Whipp
>> 2010 Microsoft MVP (Access)
>>
>> "I feel I have been denied critical, need to know, information!"
>> - Tremors II
>>
>> [URL="http://www.regina-whipp.com/index_files/TipList.htm"]http://www.regina-whipp.com/index_files/TipList.htm[/URL]
>>
>> "Patrick" wrote in message
>
>> We are developing a database for social club.
>>
>> We have decided the payment method as follow:
>>
>> Year 2010
>> ========
>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
>> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
>> For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be
>> selected For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012
>> checkbox will be selected
>>
>> Year 2011
>> ========
>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
>> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected
>> (Provided
>> that she hasn't paid for 2 or 3 yrs in Year 2010)
>> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be
>> selected (Provided that she hasn't paid for 2 or 3 yrs in Year
>> 2010)
>>
>> Year 2012
>> ========
>> Payment option - No Payment / Paid 1 Yr
>> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected
>> ((Provided
>> that she hasn't paid for Yr2012 in last 2 years)
>>
>> Year 2013
>> ========
>> It will provide those options as in Year 2010 (i.e. they are able
>> to pay for
>> 1 year, 2 year or 3 years again)
>>
>>
>> We are able to create a child table for "Year 2010". However, we
>> would like
>> to seek your advice on how to implement for the coming two years
>> (i.e. Year
>> 2011 and Year 2012). Besides, how to implement year end
>> processing - i.e. reset payment to "No Payment", Money = $0.
>>
>> Thanking you in anticipation
>>
>>
>
> >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Aug 31, 2009
Posts: 2
|
(Msg. 10) Posted: Sat Jan 30, 2010 8:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
"Steve" schreef in bericht
> Patrick,
>
> I provide help with Access, Excel and Word applications. I would like to offer to work with you to develop a set of tables to
> implement what you describe here. My fee would be very reasonable. I have helped many customers with their initial design of their
> database. If you want my help, contact me.
>
> Steve
> santus.cut RemoveThis @penn.com
You are dismissed $teve...
OP look at [URL="http://home.tiscali.nl/arracom/whoissteve.html"]http://home.tiscali.nl/arracom/whoissteve.html[/URL]
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)
Arno R >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 28, 2008
Posts: 104
|
(Msg. 11) Posted: Sat Jan 30, 2010 11:37 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
On Sat, 30 Jan 2010 16:16:56 +1100, "Patrick"
wrote:
>Dear Gina,
>
>There is already a table of MEMBERS that contain details like: Surname /
>First Name / Department / Phone Number / Employee ID / Indicator for payment
>of year since 2005 (i.e. Yr2005, Yr2006, Yr2007, Yr2008 ...etc).
So every year you need to change the design of your table, change all your
forms and reports, change all your queries...!? Ouch!
You're using a relational database, not a spreadsheet! Use it relationally: if
you have a One (member) to Many (payments) relationship you need *two tables*
in a One to Many relationship:
Members
MemberID <primary key>
LastName
FirstName
<other biographical data>
Payments
PaymentID <Primary key>
MemberID <link to Members>
PaymentDate <date/time, or Integer year if you don't care when during the
year they paid>
PaymentAmount
>Besides,
>there is an indicator PAID indicating whether the member has paid for this
>year or not.
That field should simply not exist, as you can determine whether they paid by
looking for the payment itself.
>Normally, during the year end, we will run Year End Processing - Reset the
>Paid Indicator PAID from Yes to No and we will send a letter to those who
>haven't paid in the second month of a year to remind them to pay the annual
>fee.
All this can be done very easily without any "year end processing" needed. The
letters can be generated based on a Query selecting those members with (or
without) a payment in the desired date range.
>Currently, we haven't created a child table for payment as we don't need to
>record future year payment and amount of money being paid. These are new
>requirements.
The child table isn't for future payments. It's for ALL payments, and to make
your job simpler! >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 12) Posted: Sun Jan 31, 2010 5:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Dear Bob and John,
Many thanks for your advice.
If I follow Bob's suggestion,
The member paid in Year 2010 for 1 year.
The table will become
EmID / PayYear / Payment
123 2010 $10
If she paid for 2 years, it will be
EmID / PayYear / Payment
123 2010 $18
123 2011
If she paid for 3 years, it will be
EmID / PayYear / Payment
123 2010 $25
123 2011
123 2012
In year 2011, if she had paid for 1 year in Year 2010, how can we display
the option for her (i.e. $10 for 1 year and $18 for 2 years). Is it
necessary for us to create another table storing the payment amount and how
should it be linked to the "Member Payment" table ?
If I follow John's suggestion to add a date for the payment, for previous
payments (i.e. for years from 2005 to 2009, should I assign a dummy day
like: 1 Jan 2005 for Year 2005, 1 Jan 2006 for Year 2006 payments) ?
Besides, do you think that I should use current day for the payment day OR
just enter the year of payment only ?
Thanks again for your help.
"Bob Quintal" wrote in message
> "Patrick" wrote in
>
>
>> Dear Gina,
>>
>> There is already a table of MEMBERS that contain details like:
>> Surname / First Name / Department / Phone Number / Employee ID /
>> Indicator for payment of year since 2005 (i.e. Yr2005, Yr2006,
>> Yr2007, Yr2008 ...etc). Besides, there is an indicator PAID
>> indicating whether the member has paid for this year or not.
>>
>> Normally, during the year end, we will run Year End Processing -
>> Reset the Paid Indicator PAID from Yes to No and we will send a
>> letter to those who haven't paid in the second month of a year to
>> remind them to pay the annual fee.
>>
>> Currently, we haven't created a child table for payment as we
>> don't need to record future year payment and amount of money being
>> paid. These are new requirements.
>>
>> Thanks again
>>
> Create a table with at least 3 columns, MemberID / PaymentYear/
> PaymentAmt /, and maybe others like Payment_Comments / dtePaidOn.
> Set the relationship between Members and Payments based on EmployeeID
>
> Move the data from the YRnnnn columns to the new table, using append
> queries.
>
> Now with something like:
> EmID / PayYear / Payment
> 123 2005 $26
> 123 2006 $26
> 123 2007 $26
>
>
> You'll now be able to store payments for past, present and future
> years, partial payments, etc.
>
> And you can then query the results for Members with no entry for 2010
>
>
>>
>>
>> "Gina Whipp" wrote in message
>
>>> Patrick,
>>>
>>> You stated you are developing a database... What tables and
>>> fields do you have so far?
>>>
>>> --
>>> Gina Whipp
>>> 2010 Microsoft MVP (Access)
>>>
>>> "I feel I have been denied critical, need to know, information!"
>>> - Tremors II
>>>
>>> [URL="http://www.regina-whipp.com/index_files/TipList.htm"]http://www.regina-whipp.com/index_files/TipList.htm[/URL]
>>>
>>> "Patrick" wrote in message
>>
>>> We are developing a database for social club.
>>>
>>> We have decided the payment method as follow:
>>>
>>> Year 2010
>>> ========
>>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
>>> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
>>> For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be
>>> selected For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012
>>> checkbox will be selected
>>>
>>> Year 2011
>>> ========
>>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
>>> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected
>>> (Provided
>>> that she hasn't paid for 2 or 3 yrs in Year 2010)
>>> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be
>>> selected (Provided that she hasn't paid for 2 or 3 yrs in Year
>>> 2010)
>>>
>>> Year 2012
>>> ========
>>> Payment option - No Payment / Paid 1 Yr
>>> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected
>>> ((Provided
>>> that she hasn't paid for Yr2012 in last 2 years)
>>>
>>> Year 2013
>>> ========
>>> It will provide those options as in Year 2010 (i.e. they are able
>>> to pay for
>>> 1 year, 2 year or 3 years again)
>>>
>>>
>>> We are able to create a child table for "Year 2010". However, we
>>> would like
>>> to seek your advice on how to implement for the coming two years
>>> (i.e. Year
>>> 2011 and Year 2012). Besides, how to implement year end
>>> processing - i.e. reset payment to "No Payment", Money = $0.
>>>
>>> Thanking you in anticipation
>>>
>>>
>>
>>
>
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address. >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 10
|
(Msg. 13) Posted: Sun Jan 31, 2010 10:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
"Patrick" wrote in
> Dear Bob and John,
>
> Many thanks for your advice.
>
> If I follow Bob's suggestion,
>
> The member paid in Year 2010 for 1 year.
> The table will become
> EmID / PayYear / Payment
> 123 2010 $10
>
> If she paid for 2 years, it will be
> EmID / PayYear / Payment
> 123 2010 $18
> 123 2011
>
> If she paid for 3 years, it will be
> EmID / PayYear / Payment
> 123 2010 $25
> 123 2011
> 123 2012
>
> In year 2011, if she had paid for 1 year in Year 2010, how can we
> display the option for her (i.e. $10 for 1 year and $18 for 2
> years). Is it necessary for us to create another table storing
> the payment amount and how should it be linked to the "Member
> Payment" table ?
>
> If I follow John's suggestion to add a date for the payment, for
> previous payments (i.e. for years from 2005 to 2009, should I
> assign a dummy day like: 1 Jan 2005 for Year 2005, 1 Jan 2006 for
> Year 2006 payments) ? Besides, do you think that I should use
> current day for the payment day OR just enter the year of payment
> only ?
>
> Thanks again for your help.
>
I would record the actual date of payment, and an explanation.
EmID / PayYear / Payment / DatePaid / Comment
123 2010 $25 2010-01-31
123 2011 $0 2010-01-31 Prepaid
123 2012 $0 2010-01-31 Prepaid
The dummy date for past years would be acceptable.
>
> "Bob Quintal" wrote in message
>> "Patrick" wrote in
>> DEL @TK2MSFTNGP05.phx.gbl:
>>
>>> Dear Gina,
>>>
>>> There is already a table of MEMBERS that contain details like:
>>> Surname / First Name / Department / Phone Number / Employee ID /
>>> Indicator for payment of year since 2005 (i.e. Yr2005, Yr2006,
>>> Yr2007, Yr2008 ...etc). Besides, there is an indicator PAID
>>> indicating whether the member has paid for this year or not.
>>>
>>> Normally, during the year end, we will run Year End Processing -
>>> Reset the Paid Indicator PAID from Yes to No and we will send a
>>> letter to those who haven't paid in the second month of a year
>>> to remind them to pay the annual fee.
>>>
>>> Currently, we haven't created a child table for payment as we
>>> don't need to record future year payment and amount of money
>>> being paid. These are new requirements.
>>>
>>> Thanks again
>>>
>> Create a table with at least 3 columns, MemberID / PaymentYear/
>> PaymentAmt /, and maybe others like Payment_Comments / dtePaidOn.
>> Set the relationship between Members and Payments based on
>> EmployeeID
>>
>> Move the data from the YRnnnn columns to the new table, using
>> append queries.
>>
>> Now with something like:
>> EmID / PayYear / Payment
>> 123 2005 $26
>> 123 2006 $26
>> 123 2007 $26
>>
>>
>> You'll now be able to store payments for past, present and future
>> years, partial payments, etc.
>>
>> And you can then query the results for Members with no entry for
>> 2010
>>
>>
>>>
>>>
>>> "Gina Whipp" wrote in message
>>
>>>> Patrick,
>>>>
>>>> You stated you are developing a database... What tables and
>>>> fields do you have so far?
>>>>
>>>> --
>>>> Gina Whipp
>>>> 2010 Microsoft MVP (Access)
>>>>
>>>> "I feel I have been denied critical, need to know,
>>>> information!" - Tremors II
>>>>
>>>> [URL="http://www.regina-whipp.com/index_files/TipList.htm"]http://www.regina-whipp.com/index_files/TipList.htm[/URL]
>>>>
>>>> "Patrick" wrote in message
>>>
>>>> We are developing a database for social club.
>>>>
>>>> We have decided the payment method as follow:
>>>>
>>>> Year 2010
>>>> ========
>>>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
>>>> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be
>>>> selected For Paid 2 Yr, it will show $18; Yr2010 & Yr2011
>>>> checkbox will be selected For Paid 3 Yr, it will show $25;
>>>> Yr2010, Yr2011 & Yr2012 checkbox will be selected
>>>>
>>>> Year 2011
>>>> ========
>>>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
>>>> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be
>>>> selected (Provided
>>>> that she hasn't paid for 2 or 3 yrs in Year 2010)
>>>> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will
>>>> be selected (Provided that she hasn't paid for 2 or 3 yrs in
>>>> Year 2010)
>>>>
>>>> Year 2012
>>>> ========
>>>> Payment option - No Payment / Paid 1 Yr
>>>> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be
>>>> selected ((Provided
>>>> that she hasn't paid for Yr2012 in last 2 years)
>>>>
>>>> Year 2013
>>>> ========
>>>> It will provide those options as in Year 2010 (i.e. they are
>>>> able to pay for
>>>> 1 year, 2 year or 3 years again)
>>>>
>>>>
>>>> We are able to create a child table for "Year 2010". However,
>>>> we would like
>>>> to seek your advice on how to implement for the coming two
>>>> years (i.e. Year
>>>> 2011 and Year 2012). Besides, how to implement year end
>>>> processing - i.e. reset payment to "No Payment", Money = $0.
>>>>
>>>> Thanking you in anticipation
>>>>
>>>>
>>>
>>>
>>
>>
>>
>> --
>> Bob Quintal
>>
>> PA is y I've altered my email address.
>
> >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 14) Posted: Tue Feb 02, 2010 5:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Dear Bob,
I have tried both Update and Append queries but both of them don't work.
Is there any wrong with my query ?
UPDATE ELDERS INNER JOIN Payment ON STAFF.ID = Payment.EmpID SET
Payment.PayYear = "2007", Payment.DatePaid = #1/31/2007#, Payment.EmpID =
[STAFF].[ID]
WHERE (((STAFF.[PAID 2007])=True));
Thanks again for your help
"Bob Quintal" wrote in message
> "Patrick" wrote in
> DEL @TK2MSFTNGP05.phx.gbl:
>
>> Dear Gina,
>>
>> There is already a table of MEMBERS that contain details like:
>> Surname / First Name / Department / Phone Number / Employee ID /
>> Indicator for payment of year since 2005 (i.e. Yr2005, Yr2006,
>> Yr2007, Yr2008 ...etc). Besides, there is an indicator PAID
>> indicating whether the member has paid for this year or not.
>>
>> Normally, during the year end, we will run Year End Processing -
>> Reset the Paid Indicator PAID from Yes to No and we will send a
>> letter to those who haven't paid in the second month of a year to
>> remind them to pay the annual fee.
>>
>> Currently, we haven't created a child table for payment as we
>> don't need to record future year payment and amount of money being
>> paid. These are new requirements.
>>
>> Thanks again
>>
> Create a table with at least 3 columns, MemberID / PaymentYear/
> PaymentAmt /, and maybe others like Payment_Comments / dtePaidOn.
> Set the relationship between Members and Payments based on EmployeeID
>
> Move the data from the YRnnnn columns to the new table, using append
> queries.
>
> Now with something like:
> EmID / PayYear / Payment
> 123 2005 $26
> 123 2006 $26
> 123 2007 $26
>
>
> You'll now be able to store payments for past, present and future
> years, partial payments, etc.
>
> And you can then query the results for Members with no entry for 2010
>
>
>>
>>
>> "Gina Whipp" wrote in message
>
>>> Patrick,
>>>
>>> You stated you are developing a database... What tables and
>>> fields do you have so far?
>>>
>>> --
>>> Gina Whipp
>>> 2010 Microsoft MVP (Access)
>>>
>>> "I feel I have been denied critical, need to know, information!"
>>> - Tremors II
>>>
>>> [URL="http://www.regina-whipp.com/index_files/TipList.htm"]http://www.regina-whipp.com/index_files/TipList.htm[/URL]
>>>
>>> "Patrick" wrote in message
>>
>>> We are developing a database for social club.
>>>
>>> We have decided the payment method as follow:
>>>
>>> Year 2010
>>> ========
>>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr / Paid 3 Yr
>>> For Paid 1 Yr, it will show $10; Yr2010 checkbox will be selected
>>> For Paid 2 Yr, it will show $18; Yr2010 & Yr2011 checkbox will be
>>> selected For Paid 3 Yr, it will show $25; Yr2010, Yr2011 & Yr2012
>>> checkbox will be selected
>>>
>>> Year 2011
>>> ========
>>> Payment option - No Payment / Paid 1 Yr / Paid 2 Yr
>>> For Paid 1 Yr, it will show $10; Yr2011 checkbox will be selected
>>> (Provided
>>> that she hasn't paid for 2 or 3 yrs in Year 2010)
>>> For Paid 2 Yr, it will show $18; Yr2011 & Yr2012 checkbox will be
>>> selected (Provided that she hasn't paid for 2 or 3 yrs in Year
>>> 2010)
>>>
>>> Year 2012
>>> ========
>>> Payment option - No Payment / Paid 1 Yr
>>> For Paid 1 Yr, it will show $10; Yr2012 checkbox will be selected
>>> ((Provided
>>> that she hasn't paid for Yr2012 in last 2 years)
>>>
>>> Year 2013
>>> ========
>>> It will provide those options as in Year 2010 (i.e. they are able
>>> to pay for
>>> 1 year, 2 year or 3 years again)
>>>
>>>
>>> We are able to create a child table for "Year 2010". However, we
>>> would like
>>> to seek your advice on how to implement for the coming two years
>>> (i.e. Year
>>> 2011 and Year 2012). Besides, how to implement year end
>>> processing - i.e. reset payment to "No Payment", Money = $0.
>>>
>>> Thanking you in anticipation
>>>
>>>
>>
>>
>
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address. >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 61
|
(Msg. 15) Posted: Tue Feb 02, 2010 7:18 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
You're using an INNER JOIN to join two tables ELDERS and Payment, yet your
ON clause is STAFF.ID = Payment.EmpID. You can't refer to a table that isn't
included in your list of tables.
Incidentally, since you're dealing with an INNER JOIN (which means you'll
only be dealing with rows where Payment.EmpID is already equal to STAFF.ID)
why are you trying to set Payment.EmpID to STAFF.ID?
Assuming that ELDERS should be STAFF, try the following instead:
UPDATE Payment
SET Payment.PayYear = "2007", Payment.DatePaid = #1/31/2007#
WHERE Payment.EmpID IN (SELECT DISTINCT ID
FROM STAFF
WHERE [PAID 2007]=True); >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
| Related Topics: | Microsoft Office - Can you update Microsoft office version 2003 to 2007 or do you have to buy the newer version
From access to word - Print - Hello, We open word letter from access with hiperlink code. . . .. . . Application.FollowHyperlink "h:\DRISHOT\" & lettername & ".doc" Is it possible to print the letter instead of open it ? How ? thank you
the setting for this property is too long - Hello. I was running the Access performance option and for some forms and reports there was a message with this: the setting for this property is too long what is this? what should I fix in order to not receive this error. Regards, Marco
Me.Name results in "Invalid procedure call or argument" - I am debugging an Access application written by someone else. In Access 2003 it behaves OK, but the client needs to upgrade to Access 2007 for various reasons. When the app is run in Access 2007 I get a few niggles that I have been working through, but..
converting row into column - I have a table with doctors' information Their addresses are linsted in differnt rows i would like to create a row off addresses with one unique record. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
