[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Dlookup efficiency in MS Access

I am trying to use a select query to analyze annual data records from crosstab query of values grouped at the intersection of column (field number) and row (year).  

The Dlookup syntax follows:  

1-from query design view:
606: DLookUp("[606]","CrosstabPHXAMA","Year = " & [year])

2-in SQL view

DLookUp("[606]","CrosstabPHXAMA","Year = " & [year]) AS 606

The goal is to order it by year to combine with other data fields summed by year.  I am having difficulty getting the operation to run efficiently.

Thanks for any tips on simplifying this to avoid bogging down.  **When I run the query, it freezes when reaching the field generated by this syntax.
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DLookups are great for grabbing a single value ONE time. If you need anything else, you're far better off building a query that returns your data, and then using THAT query when building other queries (like the one for your crosstab).
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
To add to what LSM said, *never* use a domain function in a query.  It's equivalent to using a subselect (which you could write directly in the SQL statement), but it is totally un-optimizable by the query parser.    If you use a Dlookup(), DCount(), etc in a query, your destined for poor performance from the start.
No points here please.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now