By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Difficulty developing table for export/transfer

P: 10
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
Aug 15 '07 #1
Share this Question
Share on Google+
1 Reply


Scott Price
Expert 100+
P: 1,384
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
Aug 27 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.