I have a table (tblActivity) which shows a members in/out activity from a club. "In" activities would be Initiated or Reinstated, and "Out" activities could include Suspension, Demits, Expulsions or Deaths.

I've designed the table with 4 fields

ActivityID (one-to-many with MemberID)
ActivityDate
ActivityChapter
ActivityType (list box with all possible types)

I'm now trying to formulate a query that will take the differences (in days) between In and Out dates and then total them up to show a calculated field called "TimeInService" on a form.

All these dates used to be stored in a tblMembers table and I used "DateDiff" between field names, but was told that storing the dates in a seperate table as I have them now would be more efficient. With the list box now taking the place of the seperate fields, how would get it to calculate using the list box as the criteria?

If you need more information please feel free to email me.

Bluez