CODEWITHSUNDEEP
sqlms-access
M. Brandon Wade
M. Brandon Wade

Reputation: 17

JOIN on Access "Lookup" field returns no results

I have a strange problem that I believe is related to the way my lookups are structured.

TABLE Category
   ID
   CategoryName

TABLE Product
   ID
   ProductName
   Category - Number (SELECT [Category].[ID], [Category].[Category Type] FROM Category ORDER BY [Category Type];)

TABLE SalePrices
   Several fields related to sale date, price, &c.
   ProductName - Text (SELECT [Product].[ID], [Product].[Product Name] FROM Product ORDER BY [Product Name];

For some reason I get a blank result set when I run the following query:

SELECT SalePrices.[Product Name], Product.[Product Name]
FROM SalePrices INNER JOIN Product ON SalePrices.[Product Name] = Product.[Product Name];

I have a query that displays the MIN of SalePrices.UnitPrice which I want to display with the ProductName and CategoryName, but I'm not getting results for that so I wanted to simplify things first.

When I join Product and Category I have to match Product.[Category Type] = Category.ID;, but when I try to do SalePrices.[Product Name] = Product.[ID]; I get a TYPE MISMATCH error. I'm not sure where I went wrong.

The eventual goal is to combine the SalesPrices <-> ProductName join with this one:

SELECT Product.[Product Name], Category.[Category Type]
FROM Product INNER JOIN Category ON Product.[Category Type] = Category.ID;

Upvotes: 0

Views: 1025

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

As suggested in the comments by @Scotch and @HansUp, defining "Lookup" fields in an Access table is generally regarded as a Bad Idea for all but the most trivial of cases. They hide what is really going in the database and they can lead to all kinds of confusion (as you have discovered) when you actually try to use them. (Ref: here, via comment by @HansUp.)

Your best course of action would be to replace the Lookup fields in your tables with regular numeric fields containing the ID (key) values in the related tables. Then, if necessary, you can create and save Select Queries to explicitly do the lookups and display the results as they previously appeared in the [Product] and [SalePrices] Datasheet Views.

Upvotes: 1

Related Questions