Post to Second Number (Access 2000)

**THWatson** · 2004-01-12 01:19

Among other things, the database keeps track of members' names and donations. Each member (could be an individual or a couple) is assigned an Envelope Number under which their donations are made. In the great majority of cases where there is both a husband and wife, donations are made under the same EnvNbr.
UniqueID 1, Fred & Sally Carruthers, EnvNbr 12

In a few cases, for Income Tax purposes, husband and wife prefer to make separate donations, each having their own separate EnvNbr, thus to get individual receipts.
UniqueID 2, Jack & Susan Ford, EnvNbr 13 SecondNbr 14

I have worked out a way to add this second number, with a check box to indicate it is a second number. And in the query behind the Receipt report, I can assign the second number to Susan Ford through the following logic
IIf([SecondNbr]=True,[FirstName2] & " " & [LastName],[FirstName] & " & " & [FirstName2] & " " & [LastName])

So the donations for EnvNbr 13 would be assigned to Jack & Susan Ford.
The donations for EnvNbr 14 would be assigned to Susan Ford.

What I would like to do, in these cases would be to have...
Donations for EnvNbr 13 assigned to Jack Ford (not Jack & Susan)

Is there a way to do this? Keeping in mind that in the great majority of cases there is no SecondNbr? Or is there a better way to construct the process from the outset?

One way, of course, would be to separate Jack and Susan Ford into separate records. But this is not very practical because it makes things such as a members' directory look odd.

Thanks.
Tom

**HansV** · 2004-01-12 01:29

What do you mean by "assign", i.e. where is the expression you posted used? I see how this expression distinguishes between SecondNbr = True or False, but not how you determine that it belongs to one or the other number.

**THWatson** · 2004-01-12 01:47

Hi, Hans
This database is used in a church. By "assigned" I mean that each member record (sometimes couples, sometimes individuals) is assigned an EnvNbr through which their donations are credited. Thus, no 2 Records can have the same EnvNbr at the same time. Donations are entered by this EnvNbr.

I have a form for assigning these EnvNbr's. If it's a SecondNbr, there's a check box to indicate such, so then if an entry is made in the Donations form under EnvNbr 13, it will be indicated that donation belongs to Jack & Susan Ford, and if a donation is entered under EnvNbr 14 it will be indicated the donation also belongs to Jack & Susan Ford.

This is where the expression comes in. It is used in the query to separate out the donations that belong to Susan Ford (these will have the SecondNbr check box checked, EnvNbr 14) from those that belong to Jack & Susan Ford (no SecondNbr check box checked, EnvNbr 13).

Hans, I am in the early stages of reconstructing this entire database, so can take this any way that is the best.

Tom

**HansV** · 2004-01-12 01:54

Sorry, I don't understand this at all. I have to go offline now, I hope that somebody else picks up on this.

**THWatson** · 2004-01-12 02:03

Hans
I apologize if I didn't make it clear. I think it's hard to visualize.

In any event, thanks for trying.
Tom

**WendellB** · 2004-01-12 12:41

Having had some experience with church membership databases, I think I understand the problem. We ultimately abandoned the envelope number as a way of tracking contributions, though envelopes were still used. But for recording a contribution against a person or people, we required that it be recorded against a person or a family. We did that by allowing either spouse or the family to have contributions, and created reports for all three if necessary. I think in order to achieve what you want, you may need to assign an envelope to the couple, and one for each of the individuals.

**THWatson** · 2004-01-12 13:21

Wendell
Thanks for your reply. I am really interested in how you might have accomplished what you outlined, and is (I think) what I am trying to achieve.

Part of the problem is rooted in the fact that the entry of contributions is made by the envelope #. At the point of data entry, the user has a physical paper envelope which bears only a #. She does not have to know to whom that # belongs.

There is no difficulty at all in those cases (the majority) where both husband and wife make joint contributions. It's only in the cases where they want to make separate contributions that I am having difficulty. One way, of course, would be to make Jack Ford and Susan Ford separate records in the database, but this looks messy when it comes to directory print-outs etc.

So I am trying to figure out a method for allowing Jack to have one # and Susan to have another # without breaking them into separate records. I have worked out how to assign a SecondNbr and that contributions made to that SecondNbr get applied to Susan Ford, but contributions made to the first # show as credited to "Jack & Susan Ford" rather than just "Jack Ford."

Can you help me understand how you accomplished it in your church database?

Thanks.
Tom

**WendellB** · 2004-01-12 14:44

Hi Tom,
The difficulty with that situation is the ambiguity associated with it. We solved it by using a table structure that had both family records and person records in the same table, and used a linking table to establish family relationships. Then we insisted that if a person wanted to track contributions in a particular way, there had to be an envelope number associated with that record - so in your case we would have had an envelope number for Jack, one for Susan, and one for Jack and Susan. That way, if they want to do some contributions in one manner, and others in another, they controlled how it was recorded. Hope this helps.

**THWatson** · 2004-01-12 17:08

Wendell
Thanks for the information.

I'll take a look at that structure. I appreciate your input.

Tom

**THWatson** · 2004-01-16 05:10

Wendell
One of the difficulties I have with this Envelope # stuff is that the user enters contributions by this number, and does not have a name to which to refer when making the entries (without going through a list of some 350 names). Although I can see where you went with the table structure you outlined, I am not clear how you got around this rather "blind" entry problem.

So I have worked out an alternate process and wanted to get your opinion.

The table through which the Envelope #s are assigned has, in addition to the ID that links it to the Members table, these fields: EnvNumber, StartDate, EndDate and AssignedTo.

The "AssignedTo" field is a one character text field, limited to entries of A, B or C.
A is used when there is only 1 Envelope # in the family unit
B and C are used when two people in the family unit want separate Envelope #s. B is for Person 1, C is for Person 2.

Then in the query behind the Receipts report, I use the Switch function to separate out the A, B and C designations and apply them to the correct individual or persons.

My testing leads me to believe this will work fine. Does the method suggest any flaws to you?

Thanks.

Tom

**WendellB** · 2004-01-16 10:48

I don't see any reason why it shouldn't work - it's essentiallly the same concept we used. We actually spent a good deal of time wrestling with the table structure before we adopted the single table approach, as it isn't strictly normalized, but it allowed us to avoid entering the same address information for each member of a family, yet if we wanted, they could have separate addresses. We also dealt with the issue of some family members being members of the church and others not being members by using a member flag in the record, and by setting up a rule that a family couldn't be a member, only a person could be. Hope this makes sense and clarifies how we approached it. We also didn't attempt to reuse envelope numbers, so we had an envelope number simply carried in the person/family table. That way when contributions were entered, we immediately knew who the contribution belonged to behind the scenes, even though the person entering the data didn't. But I think your method will work.

**THWatson** · 2004-01-16 13:36

Wendell
Thanks for your reply. You have developed a very involved and very interesting structure, quite a bit more sophisticated than mine. But as near as I can determine, from the testing I have done, my method works for our situation.

You touched on an interesting issue when you said you didn't attempt to reuse envelope numbers. Unfortunately, I don't have that luxury in the Envelope # series to which an actual paper envelope is attached - there are other series such as PAR contributors who have an automatic monthly withdrawl from their bank - but in the actual paper Envelope series of 300 numbers there aren't a whole bunch of vacant numbers. So if a person switches categories, say from regular to PAR, the church has to be able to reassign that number to somebody else. I have solved that by applying a StartDate and EndDate to the numbers.

I don't think I will worry about a family member keeping the same family number connection if they move to a different address. I am just going to make them a new record then, with a different envelope etc.

Anyhow, thanks again for your input. I appreciate it.

Tom

Thread: Post to Second Number (Access 2000)

Thread Tools