Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow query (1997)

    I am working on a query which has 18 smaller queries that are joined together. Out of the 18 queries, I am adding 1 field of each query to design the large query. When I run the query using 15 fields of the 18 queries then I get results. However if I add more fields to the large query then it continues to run forever. Is there a limit to the fields one can add in the design view? And if so how can I alleviate this problem?

    Thanks,

    Ben

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow query (1997)

    You will have to tell us in more detail how the queries are designed and how they are joined in the large query.

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow query (1997)

    The queries are linked together by physician and each of the individual queries gives me a count of a certain measure which has a parameter of either yes,no, or n/a. Here is the SQL.
    SELECT [Qry_Attending_Summary Rules].[MD#], [Qry_Attending_Summary Rules].[MD Name], [Qry_Attending_Summary Rules].Cases, Qry_dc14_attending_na.[dc14n/a], Qry_dc14_attending_no.dc14no, Qry_DC14_attending_yes.dc14yes, [Qry_handP24_attending_n/a].[HandP24_n/a], Qry_handP24_attending_no.HandP24_no, Qry_handp24_attending_yes.HandP24_yes, [Qry_HandP_attending_n/a].[HandP_n/a], Qry_HandP_Attending_no.HandP_no, Qry_HandP_Attending_Yes.HandP_yes, [Qry_IC_attending_n/a].[IC_n/a], Qry_IC_attending_no.IC_no, Qry_IC_attending_yes.IC_yes
    FROM ((((((((((((((((([Qry_Attending_Summary Rules] LEFT JOIN Qry_dc14_attending_na ON [Qry_Attending_Summary Rules].[MD#] = Qry_dc14_attending_na.[MD#]) LEFT JOIN Qry_dc14_attending_no ON [Qry_Attending_Summary Rules].[MD#] = Qry_dc14_attending_no.[MD#]) LEFT JOIN Qry_DC14_attending_yes ON [Qry_Attending_Summary Rules].[MD#] = Qry_DC14_attending_yes.[MD#]) LEFT JOIN [Qry_handP24_attending_n/a] ON [Qry_Attending_Summary Rules].[MD Name] = [Qry_handP24_attending_n/a].[MD Name]) LEFT JOIN Qry_handP24_attending_no ON [Qry_Attending_Summary Rules].[MD#] = Qry_handP24_attending_no.[MD#]) LEFT JOIN Qry_handp24_attending_yes ON [Qry_Attending_Summary Rules].[MD#] = Qry_handp24_attending_yes.[MD#]) LEFT JOIN [Qry_HandP_attending_n/a] ON [Qry_Attending_Summary Rules].[MD#] = [Qry_HandP_attending_n/a].[MD#]) LEFT JOIN Qry_HandP_Attending_no ON [Qry_Attending_Summary Rules].[MD#] = Qry_HandP_Attending_no.[MD#]) LEFT JOIN Qry_HandP_Attending_Yes ON [Qry_Attending_Summary Rules].[MD#] = Qry_HandP_Attending_Yes.[MD#]) LEFT JOIN [Qry_IC_attending_n/a] ON [Qry_Attending_Summary Rules].[MD#] = [Qry_IC_attending_n/a].[MD#]) LEFT JOIN Qry_IC_attending_no ON [Qry_Attending_Summary Rules].[MD#] = Qry_IC_attending_no.[MD#]) LEFT JOIN Qry_IC_attending_yes ON [Qry_Attending_Summary Rules].[MD#] = Qry_IC_attending_yes.[MD#]) LEFT JOIN Qry_postop_attending_na ON [Qry_Attending_Summary Rules].[MD#] = Qry_postop_attending_na.[MD#]) LEFT JOIN Qry_postop_attending_no ON [Qry_Attending_Summary Rules].[MD#] = Qry_postop_attending_no.[MD#]) LEFT JOIN Qry_postop_attending_yes ON [Qry_Attending_Summary Rules].[MD#] = Qry_postop_attending_yes.[MD#]) LEFT JOIN [Qry_progress_attending_n/a] ON [Qry_Attending_Summary Rules].[MD#] = [Qry_progress_attending_n/a].[MD#]) LEFT JOIN Qry_progress_attending_no ON [Qry_Attending_Summary Rules].[MD#] = Qry_progress_attending_no.[MD#]) LEFT JOIN Qry_progress_attending_yes ON [Qry_Attending_Summary Rules].[MD#] = Qry_progress_attending_yes.[MD#]
    GROUP BY [Qry_Attending_Summary Rules].[MD#], [Qry_Attending_Summary Rules].[MD Name], [Qry_Attending_Summary Rules].Cases, Qry_dc14_attending_na.[dc14n/a], Qry_dc14_attending_no.dc14no, Qry_DC14_attending_yes.dc14yes, [Qry_handP24_attending_n/a].[HandP24_n/a], Qry_handP24_attending_no.HandP24_no, Qry_handp24_attending_yes.HandP24_yes, [Qry_HandP_attending_n/a].[HandP_n/a], Qry_HandP_Attending_no.HandP_no, Qry_HandP_Attending_Yes.HandP_yes, [Qry_IC_attending_n/a].[IC_n/a], Qry_IC_attending_no.IC_no, Qry_IC_attending_yes.IC_yes;
    I hope this is enough information.

    Ben

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow query (1997)

    With all those left joins, the number of record combinations that have to be checked increases very fast with the number of joins. I fear that Access just bogs down with 18 joins.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •