Excessively Complicated Database

[McCormick]

Perhaps somebody can provide the advice I need to keep from slipping further into an unnecessarily messy database. This is a timesheet/ payroll database. First, my parameters:

- We have only a few employees, but I like to build things that can adapt in the future when we get more.

- Each employee has multiple possible pay rates (depending on their activity). I don't want to hard-code these, since they may change in the future.

- My boss wants me to track our activities in rough chunks - say, 2-4 different activities each day. (Thus, my basic unit isn't the day, but the "entry".

- We are paid twice a month, which means payday wanders all over the calendar.

I built a system to acommodate all that, but it's a bit clumsy. Now, I need to add in an overtime feature. Overtime, of course, is measured in weeks, but the pay periods are measured in half-months. My (clumsy) solution to all the other conflicting demands was to build three attached databases. One tracks individual entries. One tracks individual employees (and their pay options). The third uses the other two and when I ask for totals it literally counts (in a loop) each entry. The summary fields don't seem to allow for what I want so I loop through, adding each new entry to a temporary field (and marking it as "counted"). As it stands, I can click a button, the system loops through about 15 days worth of entries for each person, pastes the answers into the master database, and prints out a record.

Now that I want to add an automatic overtime function, I'm stuck. Pay periods generally start in the middle of a week, so I can't just look at the total hours. I considered a separate script, that would loop through the entries all over again with a different criteria, stopping at each Friday to consider OT. But with multiple entries per day, it would stop each time. I know I could script around that by adding some more steps, but it's just getting embarrassing. I feel like there must be some whole different arrangement I'm missing that would work this out more elegantly.

I hope that made some sense. Any advice would be greatly appreciated. Anyone interested in more information or looking at the whole thing can email me directly ([email protected]).

[Ugo DI LUCA]

Hi,

I had some difficulties figuring all this out from what you said , but this is what I retain

My (clumsy) solution to all the other conflicting demands was to build three attached databases. One tracks individual entries. One tracks individual employees (and their pay options). The third uses the other two and when I ask for totals it literally counts (in a loop) each entry.

The structure seems logical, but I don't think you need any script. You're talking about summary fields but the whole thing could be solved with some relationships and calculations.

At least, from what I've just read.

[Vaughan]

The bad news: I've worked on a system similar to this and it's 18 files. Admittedly it does a bit more like invoices and projects as well as time sheets, but in the end it's what you want.

To get there, you're going to have to learn more about relationships: particularly "one to many" and "many to many" relationships. Many to many relationships require a "join" file which seems frightening at first until you get the idea. You'll need quite a few before you're finished!

Unfortunately the database I mentioned above isn't my own so I cannot offer it to you to examine.

[danjacoby]

It might also help if you can get your boss to make paydays on, say, the 2nd and 4th (or 2nd & last) Friday of the month. Then you don't have a problem with overtime weeks, since every payday will be an exact number of weeks.

It's just a thought.

[McCormick]

Thanks for the advice. Many-to-many is definitely an issue, and I am not familiar with "join" files - I'll look for old postings on that.

Summaries, relationships, and calculations I have in spades, and while I am sure there are plenty of ways mine could be improved, I think the difficulties lie in the many-to-many aspects. Many many-to-many aspects.