Payroll Dates

[johnnya]

I'm trying to set up a cross tab query with the column headings as
payroll dates
(ex. 1/15/2005, 1/31/2005...etc). Is it possible to do this
programmatically? If not then what would be the best way to get the
column headings to be set to the payroll days?

 

[Chaim]

Aren't the payroll dates a field in your table or query?

 

[johnnya]

No. the database is used to track when commissions are paid to the
insurance agents. The information is used for a report that shows the
amount of commissions paid to the agents broken up by pay period its
entered. But the data is based on a DatePaid field.

ex:
1/15/2005 | $200.00
1/31/2005 | $150.00

I've tried doing it with a crosstab query that gives me the months as
column headings with the amounts for each agent for that month.

I'm sure I'm not giving enough info here but I'll keep posting if you
need more info.

 

[Chaim]

And the payroll dates are always the 15th and last day of the month? Which
would mean the agents are getting possibly many commissions in a pay period
that have to be rolled up to a total per pay period and then this total is
used as the value for the crosstab? So your crosstab would look like:

Payroll Date ---> 1/15/2005 1/31/2005 2/15/2005 2/28/2005 .....
Agent ID
| Agent 1 $0.00 $100.00 $250.00
$25.00
| Agent 2 $150.00 $225.00 ................
etc
v

But the raw data might look like:
Agent Id CommAmt PaidDate
1 55.00 1/21/2005
2 100.00 1/10/2005
2 50.00 1/12/2005
1 45.00 1/29/2005
etc.

I'm just trying to make sure I understand clearly.

 

[johnnya]

Yep, thats pretty much it. Except the payroll days might not be
excataly on the 15th and last days because of weekends. But I can
settle for the 15th and last day for now if i can get these days to be
column heads without having to hard coding them in.