Need help updating a report.

[fiel]

I have a report where on the far left column a list of tasks are displayed going down. On the top row going across displays the names of every worker available. In this way, I'm trying to keep a record of hours worked per each person, but am having various problems with it:

1. If a new task is assigned to various workers, the
report will generate a now row in the far left column
to add the new task and all workers assigned at this
time will be added, but workers added later on are not
going onto this row, but a new row is generate at the
time another worker is added. Is there a way to fix
this so all data per task stays on just the 1 row?

2. If a worker is removed from my data table so that no
tasks are currently assigned, my report won't even
open, an error says "The Microsoft Database does not
recognize '[Staff1]' as a field name or expression".
Is there a way to use the iserror() function for this?

 

[dhookom]

There is a crosstab report sample at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

which handles any number of dynamic columns.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fiel]

Is the crosstab also supposed to allow more than one category to show if more people are assigned to a task later? I also can't seem to fix this in only having one category of each type be dislpayed at a time after information is later added.

 

[dhookom]

I don't know what a "category" is. Your first message didn't mention anything about categories.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fiel]

I'm sorry, by categories I meant for the tasks listed on each row on the far left column. Is the cross tab supposed to update for each task as well or will I have to do something else to prevent duplicate listings of the same task if more workers are assigned?

 

[dhookom]

The categories and workers should all get automatically included in the report. This however depends on your table structure which we no little or nothing about.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fiel]

As far as the table is concerned, the fields in it are as follows: Task(text), TaskNum(number), WorkerID(number), WorkerName(text), Hours(number), Postion(text), Hourly(currency). I used 1 table that stores every worker and have my forms set up to view each record based on the TaskNum selected. With this table I'm going to setup my crosstab query; I found a tutorial on Microsoft's website. I'll post later on how that goes.

 

[fiel]

I've tried using various examples online, but they're all for dates. In terms of what I'm trying to do, there isn't an order in names or anything, or is there something I must do to keep track of the amount of workers identified?

 

[dhookom]

Did you look at the crosstab example that I suggested?

OWIJWMT

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fiel]

dhookom,

Are you referring to your crosstab example for Access 97? I'm looking through and trying to relate it to mine, but am getting some confusion over the VBA code.

 

[dhookom]

The Access 97 file is the one I was referring to. There is a table in the mdb that explains the theory behind the solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]