Creating a FAST updatable query

[Leif]

I'm trying to create a query to supply a recordset for a
form. The fields are all from a single table, however I
only want records where one of the fields (an indexed,
duplicates allowed field) match a unique list of values
from a query. The query is a UNION of two other queries.
Both of the underlining queries have their Unique Values
property set to Yes. There is no Unique Value property
for the Union query.

If I use a join between a table and the query the
resulting query is not updatable. An example is the
following:

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 INNER JOIN Query1 ON Table1.Field1 =
Query1.Field1;

This produces a query that runs quickly, but is not
updatable because it is joined on a field (in table 1)
that is not unique and a query field that is unique (but I
don't seem to have a way to let Access know that).

The other way is to create a subquery in the WHERE clause
to select only the desired records. This recordset set is
updatable, but runs VERY slowly. It appears that the
subquery is run for EVERY SINGLE row of the main table.
The query looks like this:

SELECT Field1, Field2, Fields3
FROM Table1
WHERE (Table1.Field1 In (SELECT Field1 FROM Query1));

Any suggestions? Thanks.

 

[Eric Butts [MSFT]]

Hi Leif,

Have you tried changing the Form property "Recordset Type" to "Dynaset
Inconsistent Updates"?

Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "Leif" <[email protected]>
| Sender: "Leif" <[email protected]>
| Subject: Creating a FAST updatable query
| Date: Mon, 19 Jul 2004 12:55:40 -0700
| Lines: 33
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| thread-index: AcRtyl50ue/GVvzvSPCMIM40WsqE/Q==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:207334
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.queries
|
| I'm trying to create a query to supply a recordset for a
| form. The fields are all from a single table, however I
| only want records where one of the fields (an indexed,
| duplicates allowed field) match a unique list of values
| from a query. The query is a UNION of two other queries.
| Both of the underlining queries have their Unique Values
| property set to Yes. There is no Unique Value property
| for the Union query.
|
| If I use a join between a table and the query the
| resulting query is not updatable. An example is the
| following:
|
| SELECT Table1.Field1, Table1.Field2, Table1.Field3
| FROM Table1 INNER JOIN Query1 ON Table1.Field1 =
| Query1.Field1;
|
| This produces a query that runs quickly, but is not
| updatable because it is joined on a field (in table 1)
| that is not unique and a query field that is unique (but I
| don't seem to have a way to let Access know that).
|
| The other way is to create a subquery in the WHERE clause
| to select only the desired records. This recordset set is
| updatable, but runs VERY slowly. It appears that the
| subquery is run for EVERY SINGLE row of the main table.
| The query looks like this:
|
| SELECT Field1, Field2, Fields3
| FROM Table1
| WHERE (Table1.Field1 In (SELECT Field1 FROM Query1));
|
| Any suggestions? Thanks.
|

 

[John Vinson]

I'm trying to create a query to supply a recordset for a
form. The fields are all from a single table, however I
only want records where one of the fields (an indexed,
duplicates allowed field) match a unique list of values
from a query. The query is a UNION of two other queries.
Both of the underlining queries have their Unique Values
property set to Yes. There is no Unique Value property
for the Union query.

A UNION query (unless you specify UNION ALL) automatically makes its
values unique... and also makes the query, and any query *including*
it, non updateable.

You can *try* the following - not certain it will work:

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 WHERE Table1.Field1 IN(SELECT Query1.Field1 FROM Query1);

May not be lightning-fast but it should be updateable.

If the UNION still messes you up, you might need to use as many IN
clauses as there are components of the UNION - e.g.

Field1 IN (SELECT Field1 FROM componentquery1)
OR Field1 IN(SELECT Field1 FROM componentquery2)

 

[Leif]

Thanks for your reply. See below.

-----Original Message-----


A UNION query (unless you specify UNION ALL) automatically makes its
values unique... and also makes the query, and any query *including*
it, non updateable.

You can *try* the following - not certain it will work:

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 WHERE Table1.Field1 IN(SELECT Query1.Field1

FROM Query1);

Pehaps you did not see it, but in the second half of my
note I try this same query.

May not be lightning-fast but it should be updateable.

Your right on both counts, it is updateable and it is not
lighting-fast. In fact with a table of 32,000 records and
a Union query of around 500 it takes about 10 minutes to
come up completely. For my application that is
unacceptable.

I could, through a lot of programming, create a table of
the fly from the union, and join to that. I was hoping
for an easier solution, however.

 

[Guest]

Hi Eric,

I tried it, both in the query and the form, no joy.

 

[John Vinson]

Your right on both counts, it is updateable and it is not
lighting-fast. In fact with a table of 32,000 records and
a Union query of around 500 it takes about 10 minutes to
come up completely.

Eeeuwwww...

Right you are. Not acceptable.

Other than the join to a temp table, though, I don't see any good
solutions!