How to implement payment for a social club ?

[Bob Quintal]

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

 

[Patrick]

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

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]

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!

 

[Patrick]

Dear John,

Is there any recommended book so that I can learn more about VBA ?

Regards,
Patrick

 

[John W. Vinson]

Dear John,

Is there any recommended book so that I can learn more about VBA ?

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

The first two links have Books references. Anything by John Viescas or Ken
Getz would be a keeper.