Everything is going soooo slowly!!! PLEASE HELP! (1 Viewer)

[choward]

Hi,

i'll explain my setup. I'm using Access 2007 to build a database of courses run by a collection of schools. It's a favor for my parent's friend because i thought i could do it... i think i've made a mistake!

Anyway.

There are 3 main tables: Courses (which include: Course ID, Header ID, Ideal Size, Start Date, Duration, Location). Course Demands (which include Demand ID, Course ID, Allocation), and Course Header (which include: Header ID, User, Reference)

This means that when someone wants to set up a new course configuration, it's all sorted via Header ID. So each Header has many Courses. Each course has many Demands (which describe where the students come from).

There are at least 1000 courses and at least 10000 demands.

I have a form that has 2 subforms - one for the courses and a subform in that for its demands. HOWEVER< on the Courses form, there are 3 additional calculated values which describe how much spare capacity there is on a course by summing (via a query then a dlookup to that query) thw total allocationfor the demands to that course.

When i hit that number of record however, it all grinds to a halt.

Any ideas where to try and speed it up?

Thanks

Chris

 

[Guus2005]

You need to optimize your query. Don't use IIF, Dlookup, DMax, etc in your query. JOIN tables rather than use the where clause.

Post a sample database for more info.

 

[KenHigg]

I was thinking there needs to be an index somewhere... ?

 

[choward]

Thanks so far guys! i appreciate having your attention!

Basically, i have a query that sums up all the demands data for each course. This allows me to display the summary details of the course in the same line as the course itself.

WHy do i want it this way around?

It's so the user can ADD NEW COURSES on the same screen. If i use a JOIN query, i cannot do that. This is because the demands are on the many side of the relationship - having a query to show all the data and use that query as the form means i get 'Recordset Not Updatable' errors.

Would a sample database help?

 

[choward]

just to clarify, this is the query that calculates all the data from which the form does a 'dlookup' for the calculated values:

SELECT [RAP Courses].[COURSE ID], [RAP Courses].[CAS HEADER ID], [RAP Courses].START, ([START]+([DURATION]*7)) AS [END], [RAP Courses].LOCATION, [RAP Courses].CAPACITY, (select sum (FORECAST) from [RAP Course Demands] where [COURSE ID] = [RAP Courses].[COURSE ID]) AS FORECAST, (select sum (ALLOCATION) from [RAP Course Demands] where [COURSE ID] = [RAP Courses].[COURSE ID]) AS ALLOCATION, [CAPACITY]-[ALLOCATION] AS [SPARE CAPACITY], (select sum (LOADING) from [RAP Course Demands] where [COURSE ID] = [RAP Courses].[COURSE ID]) AS LOADING
FROM [RAP Courses];

 

[neileg]

If you do the calculations in a query, then the query has to run completely before you get any data. Can't you do the calculation on the form?

 

[choward]

you mean do the summation in a form? That would mke sense. How do i do SQL summatiuon queries in the variable control??

Thanks!

 

[choward]

i have tried (for example) putting in the control source for the Allocation field on the form as:

=(select sum ([FORECAST]) from [RAP Course Demands] where [COURSE ID] = [Forms]![RAP Manager]![RAP Courses subform]![COURSE ID])

but it doesn't work. Any ideas?

 

[neileg]

Not SQL, DSum().

 

[choward]

Neileg - YOU ARE A LEGEND!!!!!! THAT YOU SOOOOOO MUCH!!! that is such an enormous performance boost.

Thanks!!!

 

[choward]

Sorry Neilog, one last question:

Your solution wortks great apart fro one problem - the calculated values do not display until i hover over the cell. That make sense? Any ideas?

 

[neileg]

No idea, I'm afraid. I don't use A2007 so I wouldn't be able to look at your application. Anyone else help?

 

[Guus2005]

You could try to refresh or repaint the form

me.refresh
me.repaint

Requerying the form doesn't help i think.

HTH