Reputation: 17
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
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