If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with Group By & Max Rcd
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 |
#2
|
|||
|
|||
Problem with Group By & Max Rcd
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; |
#4
|
|||
|
|||
Problem with Group By & Max Rcd
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 |
#5
|
|||
|
|||
Problem with Group By & Max Rcd
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 |
#6
|
|||
|
|||
Problem with Group By & Max Rcd
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? --- Dennis |
Thread Tools | |
Display Modes | |
|
|