"Distinct" Query that is NOT read-only

[evanscamman]

I have 2 tables with a one to many relationship - tblItem and tblItemStock, respectively.

I need to have an editable query based mostly on fields from tblItem.
I would also like to have a yes/no field* that shows whether or not there are related records in tblItemStock (whether the item is a stock item or not).

My problem is that because of the one-to-many relationship, when I add tblItemStock to the query, my Item is listed multiple times. Using DISTINCT causes the query to be read-only, and so does using a subquery to create the Yes/No field.

Is there any other way to determine if there are related records in tblItemStock without making the query read-only or adding duplicate records?

Thank you,
Evan

* iif(isnull([tblItemStock].[ItemStockID]),False,True)

 

[namliam]

Use a DLookup or self created funtion to fill the field.

DLookup is probably easiest...

Both solutions WILL NOT BE FAST, even slow... but it works.

 

[evanscamman]

Yes, dlookup/elookup or dcount could work - but very slow. Hoping for a solution that stays within the SQL framework.

Evan