Problem with Group By & Max Rcd

[Dennis]

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;

 

[Tom van Stiphout]

On Sat, 6 Feb 2010 09:28:01 -0800, Dennis

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

 

[Steve]

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

 

[Dennis]

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

 

[Dennis]

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]

Tom,


I now understand "Group By" queries are always read-only. I kind of figured
that when I could not enter data in the query.

My subform is trying to show the latest Transaction for the current
customer. It was suggested that I do a Group By query and get the
Max(ExpirationDate). However, since that approach does not allow to me enter
data. So the question is how do I determine the "latest transaction" for a
customer and still allow data entry?