Post Exp Date in Master Rcd (row) from trans record

[Dennis]

Hi,

I'm using Access 2003 via Xp Office Pro.

I'm working on a annual membership database. Each member has to renew each
year if they want to stay a member. I want to keep a history of their
membership.

I have a membership table and will have a renewal table where I create a
record / row each time a member renews. Key to both tables are automatic
generated numbers.

When I post the renewal transactions, I would like to change the expiration
date that is on the master record. I realize that I would have the same data
in both records, but for simplicity of the rest of the system, I'm willing to
violate the 3rd normal form in this particular case.

I thought about trying to join the two tables, but I'm not sure how to do
that. Yes, I know that I can join by member number (which will be in the
renewal table), but then that gets me all of the renewal records, when I want
to have just the most current. If a member does not renew, then the most
current is the last time the renewed even if it was 5 year ago.

So my questions are:

1. How can I post the expiration date to the member's record when I post
the renewal record?

Or

2. How can I join the master record and the proper renewal record so I can
access the renewal information from the master record?

The member's expiration date appears in the member's information form and on
reports.

Thanks for your assitance.

 

[Piet Linden]

Hi,

I'm using Access 2003 via Xp Office Pro.

I'm working on a annual membership database.  Each member has to renew each
year if they want to stay a member.  I want to keep a history of their
membership.

I have a membership table and will have a renewal table where I create a
record / row each time a member renews.  Key to both tables are automatic
generated numbers.

When I post the renewal transactions, I would like to change the expiration
date that is on the master record.  I realize that I would have the same data
in both records, but for simplicity of the rest of the system, I'm willing to
violate the 3rd normal form in this particular case.

I thought about trying to join the two tables, but I'm not sure how to do
that. Yes, I know that I can join by member number (which will be in the
renewal table), but then that gets me all of the renewal records, when I want
to have just the most current.  If a member does not renew, then the most
current is the last time the renewed even if it was 5 year ago.

So my questions are:

1.  How can I post the expiration date to the member's record when I post
the renewal record?

Or

2.  How can I join the master record and the proper renewal record so Ican
access the renewal information from the master record?

The member's expiration date appears in the member's information form andon
reports.

Thanks for your assitance.

I see no reason why doing a totals query and getting the MAX
(RenewalDate) won't solve your problem. Say you have your table of
renewals

Renewal(
MemberID int,
RenewDate date)

Then you can just use this to get the latest renewal date. You could
do something fun on your form and use DMAX() to show the latest
renewal date.

SELECT MemberID, MAX(RenewDate)
FROM Renewals
GROUP BY MemberID
ORDER BY MemberID;

 

[KARL DEWEY]

Make the'member number' the primary key in the tblMembers and foreign key in
the tblRenewal.
Create a one-to-many relationship between them selecting Referential
Integerity and Cascade Update.
In your query use Max([RenewalDate]) for last date.

 

[Dennis]

Karl,

I've tried to do what you suggested, but I do not see where I can create a
one to many relationsihp. Nor do I see where I can select Referential
Integretiy and Cascase Update.

I have a query where I've joined the tblMember and tblRenewal. The join
options are:

Inlcude only rows where the join fields from both tables are equal.
Include all records from tblMember and only those from tblRenewal are equal
Include all records from tblRenewal and only those from tblMember are equal.


I do not have any other otpions. Obviously, I'm looking in the wrong place.
Where should I be looking so I can set Referential Integrety?

 

[John W. Vinson]

I do not have any other otpions. Obviously, I'm looking in the wrong place.
Where should I be looking so I can set Referential Integrety?

In the Relationships window. If you're in table design view it's on the
toolbar - looks like three little datasheets with diagonal lines between them.

 

[Dennis]

Karl,

I found where I can set up relationships. However, when I setup a
relationship the Enforce Referential Integrity and Cascade Update Related
Fiels are both grayed out.

The tables I want to join are linked tables. I don't know if that affects
anything.

Dennis

 

[Dennis]

Piet,

How would I use the SQL statement

SELECT MemberID, MAX(RenewDate)
FROM Renewals
GROUP BY MemberID
ORDER BY MemberID;

in my form to display the member's expiration date?
I would also want to display the last paid date, paid amount, and paid by
code on the member information form.

Dennis

 

[John W. Vinson]

Karl,

I found where I can set up relationships. However, when I setup a
relationship the Enforce Referential Integrity and Cascade Update Related
Fiels are both grayed out.

The tables I want to join are linked tables. I don't know if that affects
anything.

Dennis

It does indeed affect it. Relationships can only be established in the backend
database, where the tables themselves actually reside.

 

[Dennis]

John,

I will setup my relationship in the database that has the actual tables.
Thanks for the info.