Joins in unmatched query

[Ash2305]

Hi All, hoping you can help me with an unmatched query problem.

I have a linked table, which is identical to a local table, but may contain
new data.
To import this new data I'm looking at using an unmatched query and
appending the data locally.

Unfortunately this isn't working as the unmatched query always returns
whatever is in the linked table, even if it exists in the local table. I
believe this may be due to the number of fields I'm joining, around 45, as I
don't have any problem with just one field.

I've searched around but haven't yet found any indication of a limit to the
number of joins. If anyone can confirm the problem and/or suggest a work
around it would be appreciated.


ps. As a last resort I can forego the use of local tables, however I believe
this will cause performance issues...

 

[Jerry Whittle]

Let's step back a little first. Is there any field or combination of fields
in your table that could be a primary key or is there already a primary key
field (besides an autonumber field which isn't in the other table)? If so you
could append records from the other table and Access will not allow the
duplicates to be appended.

If that isn't the case, please show us the SQL statement for the query that
you've already tried. Open the query in design view. Next go to View, SQL
View and copy and past it here.

 

[John Spencer]

From the help file of query specifications

Number of enforced relationships 32 per table minus the number of indexes that
are on the table for fields or combinations of fields that are not involved in
relationships

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County