On Sat, 6 Feb 2010 09:28:01 -0800, Dennis
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;