Best table or form design scenario for multiple values of the same type?

[c_stauber]

Please accept my apologies in advance for anything that is not clear.

I am trying to set up a table that will allow me to track how people are paid. I have employees that are often paid from multiple accounts. Also the accounts have specified start and end dates. So, here is an example of a specific entry:

EForm# (Generated by SAP)
Start date (Date that this particular pay distribution begins)
End date (Date that this distribution ends)


ID# (Employee identifier)
Salary (Hourly, monthly or annually)
Account1 - 50% - End date of account
Account 2 - 23.7% End date of account
Account 3 - 5 % End date of account
Account 4 - 21.3 End date of account

The total (of course) should always be 100%

Sometimes, only 1 or 2 accounts are used, and sometimes it can go up to 10 or 15

My end result will be to pull up a report given a range of dates that will show who was getting paid, how much from which accounts.

Any ideas how best to accomplish this?

Thanks!

[June7]

Need a related dependent table for the pay period account distribution details.

[Dal Jeanis]

First, is it tracked by percentage, or by dollar amount?

Second, you do NOT want to set up your record to have Account 1 to Account N buckets, or you'll have major headaches for the rest of the application's life due to the lack of normalization. Use a separate table with the by-account information, and you'll be able to get all the reports you like.

Here's a first shot at a normalized version of the tables for storing the distro and subdistro information. Note that I used the "currency type" both for dollar amount and percentages, because Access can make math errors if you don't.

tblDistros
  DistID        autokey  
  EForm#        text - from SAP
  DistRecipient foreign key to recipient of distribution
  DistStartDt   Date
  DistEndDt     Date
  DistAmt       Currency
  (any other information about the aggregate Distribution)

tblSubDistros
  SubDistID     autokey
  DistID        foreign key to tblDistros
  AcctID        foreign Key to Account
  SubDistPct    currency - Percent
  SubDistAmt    currency - Amount
  (any other information specific to the by-account distribution)

Purists would say that either the amount or the percent is redundant in the subdistro record, and/or the total amount is redundant in the overall record, but my background in business accounting and auditing says that storing the total amount up at the distro record and both the percentages and dollar amount at the subdistro (because they are different accounts and you REALLY have paid money out of them) is an important feature of auditability.

If anything, I would do without storing the percentage field, and recalculate it on the fly, but myself, I'd rather have it stored and simplify my queries later.

All of which is a long way of saying what June said.

[June7]

If you do save calculated data (data dependent on other data) be aware of the risk that data can get 'out of sync'. If the raw values are changed then the calculated values must be recalculated and resaved.

[Dal Jeanis]

Absolutely correct, June... but then again, if you're dealing with account payment information in real money, and someone is adding or deleting the underlying "raw" records, then you have a whole lot worse problem than out-of-sync-ness.

It's a matter of perspective. If processing power is cheap, fast and unlimited, and query-programmer time is cheap and high quality, then don't store the redundant data, recalculate it on the fly. If your machine is pegged, and your query programmers are beginners, then calculate and store the summary data, but make sure your lead programmer/system designer is accounting for any required periodic recalculations. And if you are dealing with real money in any way, then ensure that "delete" means "archive" and there is a darn good auditing trail, no matter which of the above methods are used.

My 2p.

[c_stauber]

Thanks June!

[c_stauber]

By both depending on who I'm reporting to, but the $$ figure will be the most commonly used. Thanks for the input - this is helpful!

[c_stauber]

No real money - tracking only! Thanks!