A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problem with Group By & Max Rcd



 
 
Thread Tools Display Modes
  #1  
Old February 6th, 2010, 06:28 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default 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  
Old February 6th, 2010, 08:43 PM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default 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;

  #3  
Old February 6th, 2010, 09:40 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Problem with Group By & Max Rcd

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






"Dennis" wrote in message
...
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



  #4  
Old February 9th, 2010, 02:51 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default 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  
Old February 9th, 2010, 03:43 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default 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  
Old February 9th, 2010, 03:54 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.