Can a report automatically update from a crosstab query?

T

Tina S

I have a crosstab query that gives me quarterly results. Right now we are at
the end of the secord quarter, so we have no results for the third or fourth
quarter. Is it possible to design a report to show the third and fourth
quarter results as they come in throughout the next 6 months? or in other
words, can i design a report now, the at will display the third and fourth
quarter results automatically as they come in?

A second issue I have is a manager needs to sit with each of their employees
once a quarter. In the past, we have said if a manager has 8 employees, they
need to have 8 sit alongs each quarter. The problem with this is, it does
not take into account that a manager may sit with one person two times and
not at all with another employee. So the manager has 8 sit alongs, but
actually only sat with 7 employees. How can I build a query to only count a
person 1 timer per quarter and to also show a person on the same report that
did not get a sit along.

Here is how I would like the report to look:

Manager Jan Feb Mar Qtr1 Apr May Jun Qtr2 Jul Aug Sep
Qtr3.....etc
Employee1 1 0 0 1 0 1 0 1 0
0 0 0
Employee2 0 1 1 1 1 0 0 1 0
0 0 0


See quarter 3 is showing 0 because it has not occurred yet.
I will have a rolling total column after the Qtr4.
 
J

Jerry Whittle

Forms and reports expect a field to be there when you create them. One way to
fix this problem is to open the crosstab query in design view; right click in
the area near the tables; and select Properties. Next go into the Column
Headings and put in something like:

'Qtr1','Qtr2','Qtr3','Qtr4'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as months would normally do APR, AUG, etc.
You can also make data not show up by taking out a column. For example,
remove JQtr1 and the first quarter data won't show. And for your needs, it
will create an empty field for the form or report even if there is no
matching data.

Of course this only works if you always know what your column heading need
to be.
 

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