Access 2000 reporting [user] issue 1

[boone35223]

I have created a crosstab query from a payroll file which has approximately 50 columns and around 300 rows of data. Some of the columns are calculated controls summing other columns. The query takes about 30 to 45 seconds to run.

I am attempting to write a report. The report takes about 30 to 45 seconds to load. The problem I am having is that when I attempt to add fields or make any other alterations to the report (or form too), the the report freezes like it is accessing the crosstab query. After 2 or 3 touches of the report, Access locks up.

Is there a way to turn off or prevent the report from attempting to link to the query while I am writing the report?

 

[dhookom]

I expect the report is attempting to determine the available fields from the crosstab. It needs to run the query to do this. Did you specify the column headings in the crosstab properties?

If you can't figure this out, consider providing the SQL view of the crosstab.

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

Below is the SQL view of the crosstab query:

TRANSFORM Sum(employee_checks_dist.pr_check_amt) AS SumOfpr_check_amt
SELECT employee_checks.pr_check_to, Sum(employee_checks_dist.pr_check_amt) AS [Total Of pr_check_amt]
FROM (employee_checks_dist INNER JOIN employee_checks ON employee_checks_dist.pr_check_no = employee_checks.pr_check_no) INNER JOIN employee ON employee_checks.pr_check_to = employee.pr_ssn
WHERE (((employee_checks.pr_check_date)>#9/1/2009#) AND ((employee_checks_dist.pr_check_kind)="+" Or (employee_checks_dist.pr_check_kind)="-"))
GROUP BY employee_checks.pr_check_to, employee_checks.pr_check_date, employee.pr_emp_name, employee.pr_emp_firstname, employee_checks_dist.pr_check_kind
PIVOT employee_checks_dist.pr_check_categ;

In order to pull employee addresses and other information, I created another query from which I am attempting to write the report. The second query is the one referred to in my original thread with the 50 or so columns and 300 or so rows of data.

Thanks in advance for any assistance.

 

[dhookom]

Did you specify the column headings in the crosstab properties?

Can you try specify the column headings in the crosstab properties?

I think the WHERE CLAUSE can be rewritten

WHERE employee_checks.pr_check_date >#9/1/2009# AND employee_checks_dist.pr_check_kind  IN ("+" , "-")

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

Help me understand the significance of specifying the column headings. The CHECK DISTRIBUTION TABLE has 15 or so incomes ("+") and 30 or so deductions ("-"). Employees can have any combination of the various incomes and deductions. For example: Incomes include SAL, HRS, OT, etc. which can only be "+". Other values may be either "+" or "-". In instances where a value has both an income and a deduction attribute, I found the correct value by dividing the amount pulled by the query by two (as the "+" and "-" combined returned twice the actual income or deduction.

In the second query linked to the crosstab query, I did specify all columns ("+" and "-") and used IIF statements to set any null values to zero so I could perform sum calculations on the various columns.

 

[dhookom]

If you specify the column headings then your report design might not have to run the crosstab in order to understand the columns. For instance you could enter the incomes and deduction pr_check_categ values into the column headings property.

You haven't really stated if you have tried this or if it is even possible. I can't tell you for sure that this will speed up your design but I expect it might help.

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

Sorry, you are over my head. If I understand you, I tried to add columns to my crosstab query, then in the column properties in the description and column headings, I entered specific values for the various "+" and "-" incomes and deductions from the table. The error message when I re-run the query is that in a crosstab query you can only specify one row, one column, and one value.

 

[dhookom]

I don't think you have found the crosstab properties. There is a property in the property dialog named Column Headings. Find it and try entering all of your possible column headings.

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

I opened the crosstab query in design view. The pr_check-categ has been designated as my column heading in the crosstab query. When I click properties for this column, the third row down is "Column Headings". I tried entering my possible column headings in the properties. I am not sure how to explain the results but the payroll categories are blank and the pr_check_to (socials) are repeated in the rows.

If I enter only one possible column description in the properties, I get the pr_check_categ (repeated multiple times for each social). When I enter multiple pr_check categ's, I get nothing in the column.

 

[dhookom]

The column headings property of the crosstab query is a property of the query and not of an individual column. For instance is your Column Heading expression is:

Field: Expr1: "Qtr " & DatePart("q",[OrderDate],1,0)

You might set the column headings property:

Column Headings: "Qtr 1","Qtr 2","Qtr 3","Qtr 4"

When you view the sql, the last line might look like:

PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");

Duane
Hook'D on Access
MS Access MVP

 

[boone35223]

Defining the columns worked once I got to the right place. The report runs fine now. Thank you for your help.