C
Carl Rapson
I have a query (Query1) that pulls some records from a table, something
like:
SELECT [ID],[REF
ID],[Table1].[field1],[Table1].[field2],[Table2].[field1]
FROM [Table1] RIGHT JOIN [Table2] ON Table1.[REF ID]=Table2.[ID]
WHERE ([Table1].[field1] IS NOT NULL) AND ([Table1].[field2] IS NOT
NULL);
(The Table1.[ID] and Table2.[ID] are primary keys, and Table1.[REF ID] is
indexed.
Query1 is updatable - I can change field values when the query is open. I'm
using this query as the RecordSource of a subform, and I'm building a Filter
string in the main form and setting it in the subform to restrict the
subform records being displayed (by various other fields in Table2). I would
like to restrict the records returned by Query1 by linking to another query
(Query2) that contains a subset of the [REF ID] field. Query2 is an
aggregate query, something like:
SELECT DISTINCT [ID],[field1],[field1],[field1],Max([field4]) AS [Last
Value] FROM [Table2]
GROUP BY [field1],[field2],[field3];
Query2 is not updatable (I assume because of the aggregate function). So,
when I link Query2 into Query1, like this:
SELECT [Table1].[ID],[Table1].[REF
ID],[Table1].[field1],[Table1].[field2],[Table2].[field1]
FROM [Table1] RIGHT JOIN [Query2] ON [Table1].[REF ID]=[Query2].[ID];
Query1 is no longer updatable - again, I assume, because of the aggregate
function in Query2. I can work around this by leaving Query1 unchanged and
adding the reference to Query2 to my subform Filter string instead:
"[Table2].[ID] IN (SELECT [ID] FROM [Query2]) AND ..."
This leaves Query1 updatable like I need, but the subform is now extremely
slow; it can take several minutes to execute the query to populate the
subform, especially when I change some of the other Filter parametersand
requery the subform. I'm assuming that the slowness is caused by the IN
clause that I've added to the Filter string.
Is there any way I can link Query1 (updatable) to Query2 (not updatable) and
still have Query1 be updatable? Alternatively, is there any way to speed up
the working of the IN clause that I've described above? If more details are
needed, I will be happy to supply them.
Thanks for any assistance,
Carl Rapson
like:
SELECT [ID],[REF
ID],[Table1].[field1],[Table1].[field2],[Table2].[field1]
FROM [Table1] RIGHT JOIN [Table2] ON Table1.[REF ID]=Table2.[ID]
WHERE ([Table1].[field1] IS NOT NULL) AND ([Table1].[field2] IS NOT
NULL);
(The Table1.[ID] and Table2.[ID] are primary keys, and Table1.[REF ID] is
indexed.
Query1 is updatable - I can change field values when the query is open. I'm
using this query as the RecordSource of a subform, and I'm building a Filter
string in the main form and setting it in the subform to restrict the
subform records being displayed (by various other fields in Table2). I would
like to restrict the records returned by Query1 by linking to another query
(Query2) that contains a subset of the [REF ID] field. Query2 is an
aggregate query, something like:
SELECT DISTINCT [ID],[field1],[field1],[field1],Max([field4]) AS [Last
Value] FROM [Table2]
GROUP BY [field1],[field2],[field3];
Query2 is not updatable (I assume because of the aggregate function). So,
when I link Query2 into Query1, like this:
SELECT [Table1].[ID],[Table1].[REF
ID],[Table1].[field1],[Table1].[field2],[Table2].[field1]
FROM [Table1] RIGHT JOIN [Query2] ON [Table1].[REF ID]=[Query2].[ID];
Query1 is no longer updatable - again, I assume, because of the aggregate
function in Query2. I can work around this by leaving Query1 unchanged and
adding the reference to Query2 to my subform Filter string instead:
"[Table2].[ID] IN (SELECT [ID] FROM [Query2]) AND ..."
This leaves Query1 updatable like I need, but the subform is now extremely
slow; it can take several minutes to execute the query to populate the
subform, especially when I change some of the other Filter parametersand
requery the subform. I'm assuming that the slowness is caused by the IN
clause that I've added to the Filter string.
Is there any way I can link Query1 (updatable) to Query2 (not updatable) and
still have Query1 be updatable? Alternatively, is there any way to speed up
the working of the IN clause that I've described above? If more details are
needed, I will be happy to supply them.
Thanks for any assistance,
Carl Rapson