Dynamic access to columns in crosstab query

R

Randy Hartwick

I'm not sure if I am using the best method to accomplish this task or not,
however.

I have a table with:

EmpID Number
Weekending Date
CurrentOT Number

Data in the table represents overtime worked for employees for various week
ending dates, (i.e. 4/15/05; 4/29/05; 5/13/05) and new data is added after
each pay period.

What I need to be able to do is create a report that shows EmpID, Current OT
(for the last week ending date entered - 5/13/05), Prior OT (sum of all dates
excluding the current week ending date - 4/15/05 & 4/29/05), and Total OT
(sum of all dates).

I can create a cross tab query to give me totals by weekending date and then
manipulate the data from there, however, the column heading name will change
when I add a new week's OT. That would mean that I would have to edit the
report design each week. Is there a way that I can either rename the column
headings to a generic name or place the crosstab information into another
table? Or is there a different way that I should be trying to accomplish
this task?

Thanks for any help.

Randy
 
D

David Lloyd

Randy:

I used the following KB article to solve a similar issue.

http://support.microsoft.com/default.aspx?scid=kb;en-us;328320

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm not sure if I am using the best method to accomplish this task or not,
however.

I have a table with:

EmpID Number
Weekending Date
CurrentOT Number

Data in the table represents overtime worked for employees for various week
ending dates, (i.e. 4/15/05; 4/29/05; 5/13/05) and new data is added after
each pay period.

What I need to be able to do is create a report that shows EmpID, Current OT
(for the last week ending date entered - 5/13/05), Prior OT (sum of all
dates
excluding the current week ending date - 4/15/05 & 4/29/05), and Total OT
(sum of all dates).

I can create a cross tab query to give me totals by weekending date and then
manipulate the data from there, however, the column heading name will change
when I add a new week's OT. That would mean that I would have to edit the
report design each week. Is there a way that I can either rename the column
headings to a generic name or place the crosstab information into another
table? Or is there a different way that I should be trying to accomplish
this task?

Thanks for any help.

Randy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top