Members & Memberships issue

January 28, 200916 yr

Hi All,

I have a members & memberships database, there is 2 tables:

1- tbl_Members:

Memberid

MemberName

MemberStatus

some other members data (e.g. date of birth, telephone)

2- tbl_Memberships:

Membershipid

MembershipPeriod

Membership Payment

Memberid(FK)

These two tables are related members(One) membeships (many) as one member can have more than one membership specially when membership expired and he renewed for another membership.

What I am trying to do is to build a report to show each member with all the memberships he has whether it's one or more, this is where i'm facing the problem, i tried 2 scenarios:

Scenario 1:

I build the report and based it on the tbl_members table, when I do this it shows only 1 membership for each member even if this member has more than one.

Scenario 2:

I build the report based on the tbl_memberships, and grouped the data by memberid, so it's showing fine, but when I try to use a script to show only the members with the "Active" in memberstatus table it doesn't see to filter, here is the script i'm using

Go To Layout [tbl_members]

Enter Find Mode []

SetField [tbl_members::memberstatus;"Active"]

Perform Find[]

Enter Browse Mode[]

Go To Layout [rpt_MembershipsAll(tbl_memberships)]

Sort Records [Restore;No Dialog]

Enter Preview Mode [Pause]

Enter Browse Mode

Go To Layout [Original Layout]

Is it because the report is based on tbl_memberships I can't filter on tbl_members::memberstatus;"active" or what? I'd appreciate any help or direction.

Thanks.