Slow Forms

[prgwhiting]

Hi Everyone,
Small problem. Most of my forms use Queries, not saved ones, but the ones that you use with the query builder when you click on recordsource. This appears to slow the opening of the form down incredibly. The main query that causes the problem, is a query on one table that has an extra field written into the query which takes it's data from a subquery. I'll explain why in a minute, the subquery places the relevant users name by the relevant records which then get filtered down by criteria listed, which asks only to see the current users records. I'm wondering if there is anything that I can do to speed this form up.

Now onto the subquery. This might be helpful to some people. I've got a lot of users who are allocated areas of clients to look after. The areas are denoted by codes such as 70, or 80, or 99. What I've done is instead of writing a new line in a user table, one for each area, and adding the users details. I've placed all of the users areas in one field like so 70;80;90 I then use a sub query which allocates each users areas the sub query is below
(SELECT [USERLOGINNAME] FROM [TBLUSERS] WHERE [USERSAREA] like "" * "" & LEFT([TBLACCOUNTS].[PROPERTYREFNO],2)&"" * "") AS RESPONSIBILITIES FROM TBLACCOUNTS WHERE ((((SELECT [USERLOGINNAME] FROM [TBLUSERS] WHERE [USERSAREA] like "" * "" & LEFT([TBLACCOUNTS].[PROPERTYREFNO],2)&"" * ""))=[CurrentUser]))
This enables me to place each users name by each of their areas and then use that field to filter by. Hope someone finds this useful and can also help me
Thanks

 

[Chris RR]

Hmmm.... If anyone else has another opinion, jump in here, but I think that your table design (putting multiple codes in a single field) is a bad idea and going to slow you down, too. It sounds like it should be great, have Access process fewer records, but 'like' is going to be slower than reading a few table rows. I'd split the "user areas" into its own table, linked to your master table, with a row for each user area. You could look up "normalization" in almost any standard database design book for the reasons why...