Results 1 to 13 of 13
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    DLookup in a query REALLY SLOW

    I tried several different configurations of a query where I want ALL records from table "Families" but want to access the landline and cell phone numbers from a related table for the family "Head of Household" (FamilyHoHID).

    The query depicted below runs okay but it seems to run slow when used as the RecordSource in one of my forms leading me to believe there's a better way to design the query?



    Click image for larger version. 

Name:	000.jpg 
Views:	29 
Size:	105.8 KB 
ID:	38683

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Can't you just JOIN the two tables?

    Domain aggregates are known to cause slow performance in query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    SELECT fam.FamilyD, ..., phon.HomePhone, phon.CellPhone  FROM Families fam LEFT OUTER JOIN QRegistry phon ON phon.FamilyHohID = fam.FamilyID

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Can't you just JOIN the two tables?
    That's what I thought from the outset. If I add the Registry table to the design view and link one-to-one Families.FamilyHohID to Registry.RegistryID I get a gazillion records. I just want all the records in Families.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Provide sample data from both tables.

    Isn't RegistryID unique in Registry?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by GraeagleBill View Post
    That's what I thought from the outset. If I add the Registry table to the design view and link one-to-one Families.FamilyHohID to Registry.RegistryID I get a gazillion records.
    It sounds like full join of 2 tables - i.e. every record in one table is joined with all records in another table. This join is used on very limited occasions!

    Usually either:
    Inner join is used, where results are returned from records with matching key values in both tables;
    or
    Outer join is used, where from one table all records are returned, and from another table values are returned from records which have matching key values. When 1st table record doesn't have matching records in another, instead of another table values nulls are returned.

    My example in my previous post was using left outer join. Did you try it out?

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Isn't RegistryID unique in Registry?
    Most definitely. RegistryID and FamilyID are Auto.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Just discovered that after many years of use, the thread notifications were going into a Spam folder. I have no idea as to why that is. I'll try a bit later to configure the Outer Join, as your description leaves me to believe that's all that is needed to resolve the issue.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Based on your example, I would have expected the following query to run producing just the three fields, "FamilyID", "HomePhone" and "CellPhone". I'm no wizard when it comes to SQL so you lost me with your use of the terms "fam" and "LEFT". Maybe a quick explanation?

    Click image for larger version. 

Name:	001.jpg 
Views:	17 
Size:	43.8 KB 
ID:	38714

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    fam and phon are alias table names and not necessary for query. LEFT is the join type. OUTER word is not needed.

    Use the query builder DesignView instead of SQLView.

    Pull in your tables, set the link between key fields. If you have relationships built this will happen automatically. Click the link line to open dialog and set the JOIN type.

    Drag fields to the grid.

    Then switch to SQLView to see the generated SQL statement.

    This is basic Access functionality. Haven't you built queries before?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by GraeagleBill View Post
    Just discovered that after many years of use, the thread notifications were going into a Spam folder. I have no idea as to why that is.
    https://www.accessforums.net/showthread.php?t=77138
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    As you get "Parameter not found" error, this indicates that either you set the alias for QRegistry something else (not 'phon'), or key field in QRegistry is named differently (not 'FamilyHohID') when you set join condition. I.e. Access didn't find one of them, and assumes you have a parameter in your query.

    Sorry about 'OUTER' part in example join - As I use mostly with SQL Server databases, I often forget, that Access doesn't use this word in LEFT JOIN syntax.

    Aliases are useful, especially when you use data from other databases/applications in your query (and which sometimes have long paths added). Btw, you can create them in query wizard too, but I have no clue how to do this there!

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ArviLaanemets View Post
    Btw, you can create them in query wizard too, but I have no clue how to do this there!
    If you right-click on a table in the query design grid and select properties, Alias is one of the properties.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Slow Query
    By Romio in forum Queries
    Replies: 10
    Last Post: 09-26-2016, 04:33 PM
  2. Very Slow Query
    By 83dons in forum Access
    Replies: 14
    Last Post: 04-17-2015, 09:07 AM
  3. Query with Function very slow
    By dtours in forum Queries
    Replies: 8
    Last Post: 04-09-2015, 03:51 AM
  4. Replies: 3
    Last Post: 07-11-2014, 08:13 AM
  5. Query running Slow
    By mike02 in forum Queries
    Replies: 2
    Last Post: 07-26-2013, 12:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums