Difficulty developing table for export/transfer

[zags]

I've tried a couple suggestions on this, but so far I still can't figure out an efficient way to do this. Hopefully someone can help me out.

I have an access db that uses tables from an ERP db in Oracle connected via ODBC.

I need to give a user a .csv file that contains a summary of payroll data over an extended period of time, i.e. 2 yrs.

The difficulty I'm having is that the user wants all the data in one .csv file, this can equate to a few hundred columns plus. The columns include a variety of demographic data. Getting this is simple. My issue is trying to display, in summary by pay period, by pay code, the total number of hours an employee worked during the period of time entered. For example, Bob worked all 26 pay periods last year. What the user wants is a .csv file which includes each pay period, as a column header, and the total number of hours Bob worked for each of 6 specific earnings types/codes (REG, OVT, etc.).

Any thoughts on how to approach summarizing the hours by pay period by earnings type/code and listing each period and earnings type in a single table that can be exported as a .csv file?

As far as I can tell I need to be able to run a series of queries using the pay period end date, update the export "table", and then run the same series of queries using the next pay period end date and updating the next series of columns that correspond to that pay period.

Is there anyway to dynamically create/add fields to a table based on how many times I loop through a series of queries, i.e. the number of fields in the table will vary depending upon the date range the user enters before executing the report.

Hopefully this makes sense

Windows XP2 Access 2003

[Scott Price]

Hi there Zags,

You are looking for a Make Table query. Refine your SELECT query to achieve the results you want, right-click on the upper boundary of the query design view and choose Query Type...

However, you likely should sit down with the client and try to explain to them that having the data as they are requesting is going to result in some largely meaningless information! That many columns are not going to be easily viewed, and will probably result in the end user ignoring/skipping most of the information.

If they are immovable, then give them what they want... but be prepared when they come back in a bit asking for some reorganized data that is more meaningful to them.

Regards,
Scott