How to implement payment for a social club ?

[Patrick]

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

 

[Jeff Boyce]

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

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Steve]

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
(e-mail address removed)

 

[Jeff Boyce]

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

 

[Gina Whipp]

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

http://www.regina-whipp.com/index_files/TipList.htm

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

 

[Larry Linson]

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

 

[John... Visio MVP]

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
(e-mail address removed)

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

 

[Patrick]

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

 

[Bob Quintal]

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

 

[John W. Vinson]

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!

 

[Patrick]

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]

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.

 

[Patrick]

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

 

[Douglas J. Steele]

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
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

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

 

[Patrick]

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

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
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

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

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




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

http://www.regina-whipp.com/index_files/TipList.htm

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

 

[Douglas J. Steele]

Again, unless you have the table included somewhere in your SQL, you cannot
refer to fields in it. The only table you're mentioning in that SQL is
Payment.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

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

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
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

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


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




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

http://www.regina-whipp.com/index_files/TipList.htm

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

 

[John W. Vinson]

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!

 

[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.

And you are absolutely correct in pointing out that it's an append
and not an update query required.

Bob

 

[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

 

[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