MS Access DLookUp issue

Question

Dear All,

I have the below table located into an MSAccess database

I don't understand why when I run this Sector: DLookUp("[Sector]","Table_Source","[ProductLine] =129") function

I get 6 records instead of one

Thanks

Answer 1

Thanks for your reply but, according to Microsoft site, I should have only one field:

Like I said earlier, if you add more columns to your query, it would become more obvious. For example,

SELECT DLookup..., Category FROM [Table...]

Answer 2

You are calling the DLookup function in an expression for the 'field' row in the design grid.  This returns a computed column. It will evaluate independently for every row returned by the query. It will not restrict the results.  To do that you don't need to call the function at all.  Simply enter 129 in the 'criteria' row of the Sector column in the query design grid.

This will translate to SQL as follows:

SELECT Sector

FROM Table_Source

WHERE ProductLine = 129;

This will return all rows where the ProductLine value is 129. If the Sector value will be the same for all those rows then you can return a single row in the result table by including the DISTINCT predicate:

SELECT DISTINCT Sector

FROM Table_Source

WHERE ProductLine = 129;

However, if this is the case it would suggest that the table is not normalized to Third Normal Form. Regardless of this, from your sample data this is also indicated by Sector and Category apparently being functionally determined by Sector Handling, in which case the table should be decomposed, with the Sector and Category columns being non-key columns in a referenced table with one row per Sector Handling value, and this column designated as the primary key, referenced by the Sector Handling foreign key column in Table_Source. Better still, Sector and Category can be made the composite primary key of the referenced table, and retained as a composite foreign key in Table_Source, or the referenced table can be given an autonumber primary key, referenced by a long integer foreign key column in Table_Source, in which case both the Sector and Category columns would be removed from Table_Source. The Sector_Handling values can easily be returned in a computed column in a query, or a computed control in a form or report, where necessary by concatenating the Sector and Category values. This last approach would be that used by most experienced Access developers.

Answer 3

Your SQL expression is asking for the DLookup for each record in the current recordset, which apparently is 6 records. It's only returning a single value for each record, though, as the documentation indicates.

If you want one record across the entire recordset, ask for it differently. You can apply a Group By Clause to the query, for example. Or ask for Top 1 record.

But I suspect that you need to step back and think through the goal here and then come back to the selection with an appropriate method to satisfy it.

Answer 4

Thanks for your reply but, according to Microsoft site, I should have only one field:

Answer 5

That's because the table for your query has six records in it, and the DLookup is being applied to each record. Try adding the other columns in your query to see more of what's going on.