Summing up 3 fields in a record

[DevRev]

Hi,
I am trying to figure out the best way to design a table. The table is going to contain data concerning checks that have been given to a church. Each check could be broken down into 3 different categories; Tithes, Offering, Other. In other words one check for $100 dollars might be Tithes $40.00, Offering $30.00, Other $30.00. I need to be able to enter these three amounts into the record and then have them totaled in another field.

Is it best to do this all in one record or should I have two tables? If I have two tables how do I get one form to enter the break down into one table and the total in another table. Fyi, if I do this in one record I don't want them to have to enter in the total. I want the total that is entered to be calculated by the three previous fields.

Thanks for any help you can be on this.

 

[KenHigg]

I think I would have three fields in one table and total them as needed.


ken

 

[DevRev]

Ken,
So you think I should have a table with 4 fields;

Tithes, Offering, Other, Total

and total the first three columns in the fourth column?

The issuee is I need a record not only of the the first three columns but I also need a record of the total.

 

[KenHigg]

I would just derive the total as needed, no total field.

 

[DevRev]

Ken,
I think your right. I reworked the way my forms handle this and your suggestion seems the best way to do it.

Thanks
Tenney

 

[KenHigg]

Glad to help -

ken

 

[pbaldy]

I disagree, based on normalization rules. You have in essence repeating fields. I'd have a table of categories, and a table with a record for each type represented on the donation (think invoice header & detail tables). Your way seems simple enough now, but someday someone is going to ask for a 4th category, and with your design that will require design changes all over the place. The correct normalized design will handle it simply by adding a record to the category table.

 

[KenHigg]

There is no 'correct' design, maybe a better one . Your suggestion is more robust but if the user doubts they will ever need any more categories then I can see how he should satisfy his busness requiements from the way he has it designed.

On the other hand if they do anticipate a need for more detailed report (if the question is ever asked 'What's going on inside the 'other' category), then I think as pbadly does that more normalization should be done...


ken

 

[pbaldy]

I'll concede the "correct" point, and perhaps should simply have said that a normalized design would handle change better. The "1 table" design does seem simpler to start with, but perhaps the alternative should have been offered as well.

I can't tell you how often during initial design I've been told "these will be the only options" and down the road someone wanted to add to them. I would design for the possibility.

 

[KenHigg]

I guess my main point was to steer him away from a total field and overlooked the other norm. issue. I would guess some stewardship member is going to want to drill down into the 'other' data to see where people are giving -

ken

 

[DevRev]

Hmmm, you've both given me something to think about. if I were to use the normalized version of this solution wouldn't that require a separate record for each portion of a donation? In other words multiple records for one check? If that is the case what would I use as a unique key? My first thought was I could use the check number but then this would not account for cash donations which would not have a check number. Therefore if a ran a query that grouped by check number all of my cash totals would be group together as opposed to separate records. I may be making this more complicated than it is. Please advise.

 

[pbaldy]

Yes, the normalized version would have separate records for each portion of a donation. Again, the easiest way to visualize it is an invoice situation, where the header table is customer, date, etc (your donor), and the detail table has a record for each item purchased (your donation categories). Your foreign key for the detail would be the primary key field of the header table. You wouldn't use check number anyway, as different people could use the same number. You could have an autonumber field if you have no other candidate.

 

[DevRev]

So this sounds like I would have one form that would enter data into two separate tables? I'm not sure how to make one for enter data into two different tables. I'm assuming I would have to use a sub form solution, right?

 

[pbaldy]

One typical way is a form/subform, where the form is the header record and the subform (in continuous forms view) has the detail records for the displayed main record.

 

[DevRev]

Thank you both for your help. I have decided to use the normalized way. This handles my data better.