Prev: Excel Embedded Object not Updating
Next: Order Entry type form to add to three tables
From: Dennis on 6 Feb 2010 12:28
Hi,

This is the second post concerning this topic. The first post game me the
direction to go in, but now that I'm in the details I have a couple of
problems.

I have a membership table that is keyed by an automatically assigned member
number, CustAcctNo. At the end of each calendar year they members must renew
their membership for the next year. I have the following tables:

tblCustomer - 1 row per customer.
Key: CustAcctNo


tblRenew – 1 row per customer and calendar year
Key: RenewalID Automatically assigned number
Fields: CustAcctNo
Transaction (N = New, or R = Renewal)
MembershipYear
DatePaid
DateExpires
Date Card Sent

There are other fields, but these are the important one for this discussion.

I want to:

1. Display on the member screen, I want to display the latest Transaction,
Date Paid, and current membership expiration date (DateExpires), and there
status (Expired or Curr Member).

2. Have a continuous data entry form something like:

CustAcctNo Trans MemYear DtExpires DtPaid Date Card Sent


The problem I am having is creating a query that joins the tblCustomer and
tblRenew that allows me to enter data into the tblRenew table.

The query need to select the most current membership renew record from the
tblRenew. The most current record is the one with the maximum DateExpires.
It should allow me to enter data into it. Yes, I will have a form for the
user but I have found if you can not enter data into the query, you will not
be able to create a input form for that query.

Here is my current query. The only problem with it is it does not allow
data entry:



SELECT tblRenew.AcctNo,
Last(tblRenew.RenewalId) AS LastOfRenewalId,
Last(tblRenew.NewRenew) AS LastOfNewRenew,
Last(tblRenew.MemYear) AS LastOfMemYear,
Last(tblRenew.DtPaid) AS LastOfDtPaid,
Last(tblRenew.PaidBy) AS LastOfPaidBy,
Last(tblRenew.AmtPaid) AS LastOfAmtPaid,
Last(tblRenew.RenPrtDt) AS LastOfRenPrtDt,
Last(tblRenew.DtCardRcvd) AS LastOfDtCardRcvd,
Max(tblRenew.DateExpires) AS MaxOfDateExpires
FROM tblRenew
GROUP BY tblRenew.AcctNo;



--
Dennis
From: Tom van Stiphout on 6 Feb 2010 14:43
On Sat, 6 Feb 2010 09:28:01 -0800, Dennis
<Dennis(a)discussions.microsoft.com> wrote:

GROUP BY queries are ALWAYS read-only. Makes sense if you think about
it.
One solution would be to have a subform that (perhaps readonly) shows
the latest Transaction, for the current Customer.

-Tom.
Microsoft Access MVP



>Hi,
>
>This is the second post concerning this topic. The first post game me the
>direction to go in, but now that I�m in the details I have a couple of
>problems.
>
>I have a membership table that is keyed by an automatically assigned member
>number, CustAcctNo. At the end of each calendar year they members must renew
>their membership for the next year. I have the following tables:
>
>tblCustomer - 1 row per customer.
>Key: CustAcctNo
>
>
>tblRenew � 1 row per customer and calendar year
>Key: RenewalID Automatically assigned number
>Fields: CustAcctNo
> Transaction (N = New, or R = Renewal)
> MembershipYear
> DatePaid
> DateExpires
> Date Card Sent
>
>There are other fields, but these are the important one for this discussion.
>
>I want to:
>
>1. Display on the member screen, I want to display the latest Transaction,
>Date Paid, and current membership expiration date (DateExpires), and there
>status (Expired or Curr Member).
>
>2. Have a continuous data entry form something like:
>
>CustAcctNo Trans MemYear DtExpires DtPaid Date Card Sent
>
>
>The problem I am having is creating a query that joins the tblCustomer and
>tblRenew that allows me to enter data into the tblRenew table.
>
>The query need to select the most current membership renew record from the
>tblRenew. The most current record is the one with the maximum DateExpires.
>It should allow me to enter data into it. Yes, I will have a form for the
>user but I have found if you can not enter data into the query, you will not
>be able to create a input form for that query.
>
>Here is my current query. The only problem with it is it does not allow
>data entry:
>
>
>
>SELECT tblRenew.AcctNo,
> Last(tblRenew.RenewalId) AS LastOfRenewalId,
> Last(tblRenew.NewRenew) AS LastOfNewRenew,
> Last(tblRenew.MemYear) AS LastOfMemYear,
> Last(tblRenew.DtPaid) AS LastOfDtPaid,
> Last(tblRenew.PaidBy) AS LastOfPaidBy,
> Last(tblRenew.AmtPaid) AS LastOfAmtPaid,
> Last(tblRenew.RenPrtDt) AS LastOfRenPrtDt,
> Last(tblRenew.DtCardRcvd) AS LastOfDtCardRcvd,
> Max(tblRenew.DateExpires) AS MaxOfDateExpires
>FROM tblRenew
>GROUP BY tblRenew.AcctNo;
From: Steve on 6 Feb 2010 15:40
TblMember
MemberID
FirstName
Lastname
MemberNumber
<other member fields>

TblMembershipYear
MembershipYear

TblMembershipTransaction
MembershipTransactionID
MembershipYear
MemberID
TransactionType (N = New, or R = Renewal)
DatePaid
DateCardSent

Note that I did not include DateExpires in the above table. DateExpires is a
calculated field.

I'm suggesting the above tables because you can nuse a form/subform where
the main form is based on
TblMembershipYear and the subform is based on TblMembershipTransaction. For
data entry, you will be able to select a membership year and when you enter
a transaction in the subform, MembershipYear in the subform will
automatically be entered. You avoid entering it for every transaction and
you avoid the inherent chance of a typo. For viewing transactions, you will
be able to select a MembershipYear on the main form and display all
transactions in the subform. With the right queries you will be able to
create the continuous form you want in the subform where all the member
names will be displayed and all you need to enter is the DatePaid and
DateCardSent. It's a very efficient data entry system.

Steve
santus(a)penn.com





"Dennis" <Dennis(a)discussions.microsoft.com> wrote in message
news:907E0690-86A1-410B-9A2F-9127DCC00AD0(a)microsoft.com...
> Hi,
>
> This is the second post concerning this topic. The first post game me the
> direction to go in, but now that I'm in the details I have a couple of
> problems.
>
> I have a membership table that is keyed by an automatically assigned
> member
> number, CustAcctNo. At the end of each calendar year they members must
> renew
> their membership for the next year. I have the following tables:
>
> tblCustomer - 1 row per customer.
> Key: CustAcctNo
>
>
> tblRenew - 1 row per customer and calendar year
> Key: RenewalID Automatically assigned number
> Fields: CustAcctNo
> Transaction (N = New, or R = Renewal)
> MembershipYear
> DatePaid
> DateExpires
> Date Card Sent
>
> There are other fields, but these are the important one for this
> discussion.
>
> I want to:
>
> 1. Display on the member screen, I want to display the latest
> Transaction,
> Date Paid, and current membership expiration date (DateExpires), and there
> status (Expired or Curr Member).
>
> 2. Have a continuous data entry form something like:
>
> CustAcctNo Trans MemYear DtExpires DtPaid Date Card Sent
>
>
> The problem I am having is creating a query that joins the tblCustomer and
> tblRenew that allows me to enter data into the tblRenew table.
>
> The query need to select the most current membership renew record from the
> tblRenew. The most current record is the one with the maximum
> DateExpires.
> It should allow me to enter data into it. Yes, I will have a form for the
> user but I have found if you can not enter data into the query, you will
> not
> be able to create a input form for that query.
>
> Here is my current query. The only problem with it is it does not allow
> data entry:
>
>
>
> SELECT tblRenew.AcctNo,
> Last(tblRenew.RenewalId) AS LastOfRenewalId,
> Last(tblRenew.NewRenew) AS LastOfNewRenew,
> Last(tblRenew.MemYear) AS LastOfMemYear,
> Last(tblRenew.DtPaid) AS LastOfDtPaid,
> Last(tblRenew.PaidBy) AS LastOfPaidBy,
> Last(tblRenew.AmtPaid) AS LastOfAmtPaid,
> Last(tblRenew.RenPrtDt) AS LastOfRenPrtDt,
> Last(tblRenew.DtCardRcvd) AS LastOfDtCardRcvd,
> Max(tblRenew.DateExpires) AS MaxOfDateExpires
> FROM tblRenew
> GROUP BY tblRenew.AcctNo;
>
>
>
> --
> Dennis


From: Dennis on 8 Feb 2010 20:51
Steve

I think I understand your approach. However, I'm not quite sure how to
implement it. I have a couple of questions.

1. How do I change the Membership Year? I guess I would have to have some
process that changes the membership year field.

2. I have a Memberhips form which is where I add the New / Renew Member
form. I will put this form in it own tab / page on the Membership Form. How
would I create a new / renewal transaction from the membership input form?

How would I get the year from the tblMembershipYear table in the Membership
Form / New - Renewal Tab / page?

Dennis


From: Dennis on 8 Feb 2010 21:43
Steve,

I figured out the tblMemberYear table.

Key: MembershipYear
Fld 1 New Membership Expiration Date
Fld 2 Renewal Membership Expiration Date.

I will have store 30 years worth of years so I won't have to worry about it
and I'll use the current calendar year to obtain the current year.

So I answered my first question.

Dennis





--
Dennis


"Dennis" wrote:

> Steve
>
> I think I understand your approach. However, I'm not quite sure how to
> implement it. I have a couple of questions.
>
> 1. How do I change the Membership Year? I guess I would have to have some
> process that changes the membership year field.
>
> 2. I have a Memberhips form which is where I add the New / Renew Member
> form. I will put this form in it own tab / page on the Membership Form. How
> would I create a new / renewal transaction from the membership input form?
>
> How would I get the year from the tblMembershipYear table in the Membership
> Form / New - Renewal Tab / page?
>
> Dennis
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Excel Embedded Object not Updating
Next: Order Entry type form to add to three tables