ms-access loading data question

Answer 1

That SQL is bad because do you really want to group by those many fields? It seems like that SQL statement was thrown together and looked like it worked so you choose to keep it that way.

Answer 2

Do you use dlookups anywhere or any other calculated controls = even simpler ones tend to slow the loading/refreshing of a form...

Answer 3

I notice that you have a number of subforms, you may get better performance if you do not load all the subforms until they are needed: http://stackoverflow.com/questions/1360011/does-it-degrade-performance-to-use-subforms-in-ms-access/1362175#1362175

Answer 4

After reformatting the SQL, it looks like you're doing some joins that aren't necessary, those being the join on tbluserstudentteacher, tblUsersSubjects and tblUserAvailability. You can simplify (and I'd strongly suspect improve the performance) considerably. Something like the following should work:

SELECT u.id, u.title, u.first, u.last
  FROM (tblusers u LEFT JOIN chavrusas c ON u.id = c.luser_id
  AND u.gender LIKE 'm*'
  AND u.last LIKE 'd*'
  AND c.luser_type = 'shliach'
  AND c.ruser_type = c.luser_type)
ORDER BY last;

The GROUP BY isn't appropriate or necessary either, as you have no aggregate functions that would result in grouping.

I'd suspect you didn't post your real SQL here, because of the unneeded GROUP BY and JOINed tables.