how do it dlookup (1 Viewer)
- Thread starter hatmak
- Start date
You don't DLookup in queries. You use JOIN keyword:
https://www.w3schools.com/sql/sql_join.asp
Simply bring sum into data Query, link approriately, add appropriate criteria and then you can just bring down sumofCount into the query.
https://www.w3schools.com/sql/sql_join.asp
Simply bring sum into data Query, link approriately, add appropriate criteria and then you can just bring down sumofCount into the query.
Looking at your database further, I suggest you use correct data types. data.code is Short Text but contains all numerical data. data.fin_years is Short Text but might be more approriately be numeric or an actual Date/Time which holds the first date of the year. If you do that you can use simple math or common functions to determine prior and next years. With Short Text you will have a hard time comparing 2 consecutive years data.
isladogs
MVP / VIP
- Local time
- Today, 14:32
- Joined
- Jan 14, 2017
- Messages
- 18,209
As originally stated by plog, you shouldn't use DLookup in queries as it will cause the query to run slowly on a large dataset.
A much more efficient approach is to join the data table and sum query as shown below
BTW you shouldn't use code, count, sum as field or object names as they are reserved words in Access
EDIT Crossposted at https://www.accessforums.net/showthread.php?t=79183 where june7 provided the Dlookup expression for the OP.
A much more efficient approach is to join the data table and sum query as shown below
Code:
SELECT data.code, data.count, data.fin_year, sum.SumOfcount
FROM data INNER JOIN [sum] ON (data.fin_year = sum.fin_years) AND (data.code = sum.code1);BTW you shouldn't use code, count, sum as field or object names as they are reserved words in Access
EDIT Crossposted at https://www.accessforums.net/showthread.php?t=79183 where june7 provided the Dlookup expression for the OP.
Last edited:
