Help with form which shows data from a crosstab

[Sheep]

Hi, completely new to Access so I don't know a lot of the jargon on here. I'm trying to modify an existing form that was created years ago, and now that the database is way bigger it opens very slowly due to 15 list boxes all loading the same query and then finding a million different rows.

So the basics of the form:
It's control source is the 'Invoice' table. This table contains various fields which are all referred to in a few different textboxes on the form. All of these are editable, and are made to input things like invoice descriptions etc.
There is a textbox where people insert the 'Job No' which updates all the other text boxes with whatever data might already be in the invoices table fields.
All good so far.
Then there are the 15 list boxes which slow the loading time of the form to about 5 minutes. (Once it has loaded it is all instant though)
These list boxes have their control source set to 'Job No' and their row source set to 'sum of employee X' from a crosstab query.
The crosstab query is coded to sum all the hours each employee worked on each 'Job No' during the last month. So there is a 'Job No' field and then an 'sum of employee X' field for each of the 15 employees.
So in the form, these list boxes are returning a line for every single job (which works out to be hundreds of lines and take a long time to).

Once the form finally loads, the user can input their desired job number into the 'Job No' textbox, and all 15 of the list boxes will instantly update themselves with how many hours each employee worked on that job for the last month.
With this information the user can manually copy the the hours they wish in another textbox in the form which gets saved back to the final invoice table. The reason for this is that sometimes we do not want to bill a customer for 100% of the hours worked by each employee. So the list boxes serve only as a data display tool, not for editing anything.

So what I want to do is speed up the loading of this form.

I have tried turning all the list boxes into Dlookup textboxes,
Using this code:
=DLookUp("[SumOfemployeeX]","crosstab query","[Job No] = " & [Job No])
These allow the form to load instantly, but then when the 'Job No' textbox is changed they all take a minute to change their output. Not so good.

My next thinking was to find a way to only load the crosstab query once. But I already have the record source of the form set to 'Invoice'.

So I tried creating a subform which uses the crosstab query as a record source, and links to the 'Job No' control textbox from the main form. After overcoming an error regarding using a crosstab for a subform record source, I couldn't get the subform to display anything other than a white box in form view mode.

My next effort was to create a completely separate form. I set the crosstab query as the record source, added a textbox with the 'Job No' control source like the other form has. And then tried to simply display the 15 'employee X' text boxes so that they could be viewed and then manually copied out to the other form. But for some reason the 'Job No' text box cannot be edited now, because apparently if your record source is a crosstab it doesn't let you.

Any suggestions for the best method of doing all this? Basically I want each of the 15 text boxes to perform a function like: select "Sum of Employee X" from "crosstab table" where "Job No" = what ever has been entered in the Job No text box. But the problem seems to be that the data is split over 2 tables and one of them is a crosstab query.

[burrina]

It sounds to me like poor design is the issue. Normally you would have a Main form where you could enter a Customer and you could use DLookup to fill in any other pertinent data. Next, you would have a subfom(s) to enter Employee time into and the subforms record source would have the calculated expressions in them and this would be displayed on the subforms footer.

HTH

[June7]

Yes, domain aggregates can be slow and with a million (this is accurate, not exaggeration?) records, very slow.

Did you try a single multi-column listbox to display all 15 employees?

Instead of CROSSTAB as source for 15 listboxes maybe a GROUP BY query using expressions to emulate CROSSTAB - review http://www.datapigtechnologies.com/f.../crosstab.html. Use the query as source for a single multi-column listbox or subform or subreport.