 |
|
|
|
Next: Another startup problem
|
| Author |
Message |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 16) Posted: Wed Feb 03, 2010 5:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: microsoft>public>access (more info?)
|
|
|
Dear Douglas,
Many thanks for your advice. However, since the PAYMENT table is empty, the
Payment.EmpID field has to be updated to STAFF.ID first before I can run
your script.
I have attempted to update Payment.EmpID as follow but not successful
UPDATE Payment
SET Payment.EmpID = [STAFF].[ID]
Thanks again
"Douglas J. Steele" wrote in message
> 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);
>
> --
> Doug Steele, Microsoft Access MVP
> [URL="http://www.AccessMVP.com/DJSteele"]http://www.AccessMVP.com/DJSteele[/URL]
> (no e-mails, please!)
>
> "Patrick" wrote in message
>> 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
>>>
>>>
>>>> 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.
>>
>
> |
|
| Back to top |
|
 | |
External
Since: Nov 27, 2008
Posts: 61
|
(Msg. 17) Posted: Wed Feb 03, 2010 9:07 am
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: 104
|
(Msg. 18) Posted: Wed Feb 03, 2010 9:55 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
On Tue, 2 Feb 2010 20:31:15 +1100, "Patrick"
wrote:
>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));
The Update query updates records which already exist. You want an Append query
instead: try
INSERT INTO Payment (EmpID, PayYear, DatePaid)
SELECT STAFF.EmpID, "2007", #1/31/2007# FROM STAFF
WHERE (((STAFF.[PAID 2007])=True));
However, I'd really question storing *both* the PayYear and the DatePaid! The
DatePaid contains the year. Would it be legitimate to have a PayYear of 2007
and a DatePaid in 2009? Maybe it would, but it is something to think about! >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 10
|
(Msg. 19) Posted: Wed Feb 03, 2010 10:06 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
John W. Vinson wrote in
> On Tue, 2 Feb 2010 20:31:15 +1100, "Patrick"
> wrote:
>
>>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));
>
> The Update query updates records which already exist. You want an
> Append query instead: try
>
> INSERT INTO Payment (EmpID, PayYear, DatePaid)
> SELECT STAFF.EmpID, "2007", #1/31/2007# FROM STAFF
> WHERE (((STAFF.[PAID 2007])=True));
>
>
> However, I'd really question storing *both* the PayYear and the
> DatePaid! The DatePaid contains the year. Would it be legitimate
> to have a PayYear of 2007 and a DatePaid in 2009? Maybe it would,
> but it is something to think about!
Actually, the original poster said he wanted to receive a payment
today applicable to this year and next year, I suggested he add the
date of payment in order to be able to determine when payment was
made for future years.
And you are absolutely correct in pointing out that it's an append
and not an update query required.
Bob >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 10
|
(Msg. 20) Posted: Wed Feb 03, 2010 10:09 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
John W. Vinson wrote in
> On Tue, 2 Feb 2010 20:31:15 +1100, "Patrick"
> wrote:
>
>>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));
>
> The Update query updates records which already exist. You want an
> Append query instead: try
>
> INSERT INTO Payment (EmpID, PayYear, DatePaid)
> SELECT STAFF.EmpID, "2007", #1/31/2007# FROM STAFF
> WHERE (((STAFF.[PAID 2007])=True));
>
>
> However, I'd really question storing *both* the PayYear and the
> DatePaid! The DatePaid contains the year. Would it be legitimate
> to have a PayYear of 2007 and a DatePaid in 2009? Maybe it would,
> but it is something to think about!
Actually, the original poster said he wanted to receive a payment today
applicable to this year and next year, I suggested he add the date of
payment in order to be able to determine when payment was made for
future years.
I'd also add the amount paid, or the partial amount covering a year
from the multi-year payment.
And you are absolutely correct in pointing out that it's an append and
not an update query required.
Bob >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 10
|
(Msg. 21) Posted: Wed Feb 03, 2010 10:13 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
John W. Vinson wrote in
> On Tue, 2 Feb 2010 20:31:15 +1100, "Patrick"
> wrote:
>
>>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));
>
> The Update query updates records which already exist. You want an
> Append query instead: try
>
> INSERT INTO Payment (EmpID, PayYear, DatePaid)
> SELECT STAFF.EmpID, "2007", #1/31/2007# FROM STAFF
> WHERE (((STAFF.[PAID 2007])=True));
>
>
> However, I'd really question storing *both* the PayYear and the
> DatePaid! The DatePaid contains the year. Would it be legitimate
> to have a PayYear of 2007 and a DatePaid in 2009? Maybe it would,
> but it is something to think about!
Actually, the original poster said he wanted to receive a payment today
applicable to this year and next year, I suggested he add the date of
payment in order to be able to determine when payment was made for
future years.
I'd also add the amount paid, or the partial amount covering a year
from the multi-year payment.
And you are absolutely correct in pointing out that it's an append and
not an update query required.
Bob >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 27, 2008
Posts: 10
|
(Msg. 22) Posted: Wed Feb 03, 2010 10:22 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
John W. Vinson wrote in
> On Tue, 2 Feb 2010 20:31:15 +1100, "Patrick"
> wrote:
>
>>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));
>
> The Update query updates records which already exist. You want an
> Append query instead: try
>
> INSERT INTO Payment (EmpID, PayYear, DatePaid)
> SELECT STAFF.EmpID, "2007", #1/31/2007# FROM STAFF
> WHERE (((STAFF.[PAID 2007])=True));
>
>
> However, I'd really question storing *both* the PayYear and the
> DatePaid! The DatePaid contains the year. Would it be legitimate
> to have a PayYear of 2007 and a DatePaid in 2009? Maybe it would,
> but it is something to think about!
Actually, the original poster said he wanted to receive a payment today
applicable to this year and next year, I suggested he add the date of
payment in order to be able to determine when payment was made for
future years.
I'd also add the amount paid, or the partial amount covering a year
from the multi-year payment.
And you are absolutely correct in pointing out that it's an append and
not an update query required.
Bob >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 23) Posted: Thu Feb 04, 2010 6:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Dear John,
Many thanks for your advice and I have already created the Append Query.
We have a number of questions:
1) Does it mean that we have to use Form and Subform to present staff and
payment details ?
2) If we have to enter new payment, does it mean that we can go to the
subform and enter 2010 (for current year) ?
3) End user would like to keep the old interface, i.e. checkboxes shown -
Year2005 / Year2006 / Year2007 / Year2008 ? I have tried but it seems that
with a single query, we are not able to get all checkboxes showing whether
there is payment at that year. Similarly, it seems that it cannot be shown
in reports ?
4) If the requirement is to get a combo box with choice of "No Payment" /
"Paid 1 Yr" / "Paid 2 Yrs" / "Paid 3 Yrs" and we have to update the subform
(for up to three rows if they pay for 3 years), can it be achieved by just
using a Form / Subform layout ?
Thanks again
"John W. Vinson" wrote in message
> On Tue, 2 Feb 2010 20:31:15 +1100, "Patrick"
> wrote:
>
>>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));
>
> The Update query updates records which already exist. You want an Append
> query
> instead: try
>
> INSERT INTO Payment (EmpID, PayYear, DatePaid)
> SELECT STAFF.EmpID, "2007", #1/31/2007# FROM STAFF
> WHERE (((STAFF.[PAID 2007])=True));
>
>
> However, I'd really question storing *both* the PayYear and the DatePaid!
> The
> DatePaid contains the year. Would it be legitimate to have a PayYear of
> 2007
> and a DatePaid in 2009? Maybe it would, but it is something to think
> about!
> --
>
> John W. Vinson [MVP] >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 28, 2008
Posts: 104
|
(Msg. 24) Posted: Thu Feb 04, 2010 9:10 pm
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
On Thu, 4 Feb 2010 21:18:46 +1100, "Patrick"
wrote:
>Dear John,
>
>Many thanks for your advice and I have already created the Append Query.
>
>We have a number of questions:
>
>1) Does it mean that we have to use Form and Subform to present staff and
>payment details ?
I'd say so. It's the right tool for the job.
>2) If we have to enter new payment, does it mean that we can go to the
>subform and enter 2010 (for current year) ?
Any year you like. It's just data!
>3) End user would like to keep the old interface, i.e. checkboxes shown -
>Year2005 / Year2006 / Year2007 / Year2008 ? I have tried but it seems that
>with a single query, we are not able to get all checkboxes showing whether
>there is payment at that year. Similarly, it seems that it cannot be shown
>in reports ?
The checkbox interface would be a HUGE amount of unnecessary work. You'ld need
to redesign the form or the report every year, basically. This is a very good
case for user training. "This is how it works now".
>4) If the requirement is to get a combo box with choice of "No Payment" /
>"Paid 1 Yr" / "Paid 2 Yrs" / "Paid 3 Yrs" and we have to update the subform
>(for up to three rows if they pay for 3 years), can it be achieved by just
>using a Form / Subform layout ?
With some VBA code and an append query, if you really want to do it that way.
It's not *necessarily* true that "new is evil", as much as users tend to
believe this dictum! >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Dec 15, 2008
Posts: 8
|
(Msg. 25) Posted: Fri Feb 05, 2010 5:25 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
Dear John,
Is there any recommended book so that I can learn more about VBA ?
Regards,
Patrick
"John W. Vinson" wrote in message
> On Thu, 4 Feb 2010 21:18:46 +1100, "Patrick"
> wrote:
>
>>Dear John,
>>
>>Many thanks for your advice and I have already created the Append Query.
>>
>>We have a number of questions:
>>
>>1) Does it mean that we have to use Form and Subform to present staff and
>>payment details ?
>
> I'd say so. It's the right tool for the job.
>
>>2) If we have to enter new payment, does it mean that we can go to the
>>subform and enter 2010 (for current year) ?
>
> Any year you like. It's just data!
>
>>3) End user would like to keep the old interface, i.e. checkboxes shown -
>>Year2005 / Year2006 / Year2007 / Year2008 ? I have tried but it seems that
>>with a single query, we are not able to get all checkboxes showing whether
>>there is payment at that year. Similarly, it seems that it cannot be
>>shown
>>in reports ?
>
> The checkbox interface would be a HUGE amount of unnecessary work. You'ld
> need
> to redesign the form or the report every year, basically. This is a very
> good
> case for user training. "This is how it works now".
>
>>4) If the requirement is to get a combo box with choice of "No Payment" /
>>"Paid 1 Yr" / "Paid 2 Yrs" / "Paid 3 Yrs" and we have to update the
>>subform
>>(for up to three rows if they pay for 3 years), can it be achieved by just
>>using a Form / Subform layout ?
>
> With some VBA code and an append query, if you really want to do it that
> way.
> It's not *necessarily* true that "new is evil", as much as users tend to
> believe this dictum!
> --
>
> John W. Vinson [MVP] >> Stay informed about: How to implement payment for a social club ? |
|
| Back to top |
|
| |
External
Since: Nov 28, 2008
Posts: 104
|
(Msg. 26) Posted: Fri Feb 05, 2010 11:55 am
Post subject: Re: How to implement payment for a social club ?
Archived from groups: per prev. post (more info?)
|
|
|
On Fri, 5 Feb 2010 20:13:14 +1100, "Patrick"
wrote:
>Dear John,
>
>Is there any recommended book so that I can learn more about VBA ?
Jeff Conrad's resources page:
[URL="http://www.accessmvp.com/JConrad/accessjunkie/resources.html"]http://www.accessmvp.com/JConrad/accessjunkie/resources.html[/URL]
The Access Web resources page:
[URL="http://www.mvps.org/access/resources/index.html"]http://www.mvps.org/access/resources/index.html[/URL]
Roger Carlson's tutorials, samples and tips:
[URL="http://www.rogersaccesslibrary.com/"]http://www.rogersaccesslibrary.com/[/URL]
A free tutorial written by Crystal:
[URL="http://allenbrowne.com/casu-22.html"]http://allenbrowne.com/casu-22.html[/URL]
A video how-to series by Crystal:
[URL="http://www.YouTube.com/user/LearnAccessByCrystal"]http://www.YouTube.com/user/LearnAccessByCrystal[/URL]
MVP Allen Browne's tutorials:
[URL="http://allenbrowne.com/links.html#Tutorials"]http://allenbrowne.com/links.html#Tutorials[/URL]
The first two links have Books references. Anything by John Viescas or Ken
Getz would be a keeper. >> 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
