Exporting text file for wages

[ckai]

Now I really hope I can explain myself well enough to not confuse anyone.

We're needing to export some data of shifts worked by staff as a text file for a payroll software.

There are two formats it can be:

1. staffID, rate1, rate2....,rate6, allowance, units, allowance, units etc.

2. A "style" code, staffID, ref, units

The first format will have 1 line per staff with all their ordinary hours, public holiday hours, mileage expense etc but needs to have blank fields if there is nothing for the rate fields (e.g. BOB1, 32,,1,,,,)

The 2nd may have several lines per staff, 1 each for ordinary hours, public holiday etc

I'm thinking the first format will be the easiest but I'm a little unsure as to how to go about "building" it. I know I'll need to create summary fields to total all the required items (ordinary, overtime etc). But I'm unsure whether I should make a temporary table and have totals transferred to that or create a layout.

My shift table is structured:

shiftID

clientID

staffID

Date

staff designation

start

finished

hours

public holiday

travel

The total for ordinary hours is kinda simple: basically total of "hours" that "public holiday" does not equal 1

And same goes for public holiday hours - just the opposite of above.

Travel is just the total amount of travel entered.

There are some other complications but I'm just looking to get the ball rolling so I can work through it.

I considered relationships but wasn't sure how it would actually help.

Any ideas, guidance basis for using one format over the other would be a huge help.

Thanks

[LaRetta]

69 views and no replies means that others see it as I do ... we cannot answer without seeing your file. Can you clone it (create empty clone) and upload it? If not, provide a sample file showing your structure.

I see two issues:

• Summarizing hours based upon a date range (which would match your payroll period).
• A csv export of those fields

But we can't really assist unless we see what you have. :wink2:

[ckai]

I was having the same thoughts

I thought I had parts of it figured out in the weekend but don't seem to have my brain in gear this mourning either.

I may have to change a few things around in our payroll software to make it easier to get around a few issues, which isn't that much of a big deal. We'll see though. I'm avoiding that as it will change some in-house reports.

The database I've attached is a clone and be warned...is messy and madly confusing in places only because I'm trying new things (and teaching myself).

I'm having a look over this again during the day to see what I can work out so hopefully so people will be able to lead me down the best path (holding my hand )

PS. The database name is a long-standing company joke

God_Clone.fp7.zip

Edited July 18, 2010 by Guest

[ckai]

Looks like I confused everyone...even with an example

Even I've stepped back from it for a few days to clear the brain.

[Jeff M]

If you're doing payroll reporting, generally that is done on a pay period type basis. This would be represented by a time card record or a collection of hour entry records that are related to the staff.

In my solutions I have a 'punches' table where I store employee time clock entries. Those entries are related to the employee as well as the pay period. When I'm ready to report, I pull a total of all hourly punches for each employee for the given pay period.

I'm not seeing a structure in your database to store this data. I see where you are doing quite a bit to account for availability and scheduling, but where are you storing actual daily or weekly data?