MS ACCESS SQL Join Subquery

Question

I have two tables: newparts, storedparts

I insert the parts of the newparts, which are not jet in the storedparts into the storedparts:

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM storedparts " & _
             "RIGHT JOIN newparts ON (storedparts.identifier = newparts.identifier) AND (storedparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((storedparts.AutoID) Is Null);"

This is working fine so far. Now the Problem: Table storedparts is getting so big that the programm is taking too Long for the join process. My solution: Just compare the newparts not to all parts of the storedparts, but just to parts that aren't older than 4 days... I tried a subquery like this, but i can't get it to run.

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM storedparts (WHERE storedparts.timestamp > Now() - 4) " & _
             "RIGHT JOIN newparts ON (storedparts.identifier = newparts.identifier) AND (storedparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((storedparts.AutoID) Is Null);"

Any help is appreciated.

Why `RIGHT JOIN`? If outer join really is needed, switch tables and do a `LEFT JOIN`instead. Most people find those hard enough to understand, and right join way more confusing. (It's easier to understand "main table left join optional data" instead of "optional data right join main table".) — jarlh, Jul 28 '16 at 08:54

Ashley John · Answer 1 · 2016-07-28T09:04:40.250

You could add the where clause after the join statements and see if it improves the performance of the query . Else Try this and see if it works

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM ( SELECT * FROM storedparts WHERE 
              storedparts.timestamp > DateAdd ( 'd', -4, Now()) )sparts" & _
             "RIGHT JOIN newparts ON (sparts.identifier = newparts.identifier) AND 
             (sparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((sparts.AutoID) Is Null);"

Eduard · Accepted Answer · 2016-07-28T09:24:14.277

This wouldn't be a problem if your tables have indexes.

CREATE INDEX ndx_sp_identifier ON storedparts (identifier);
CREATE INDEX ndx_np_identifier ON newparts (identifier);

Then I suggest you change your query to something like this as @jarlh pointed out.

INSERT INTO storedparts 
SELECT newparts.* 
FROM newparts
LEFT JOIN storedparts 
ON newparts.identifier = storedparts.identifier
AND newparts.timeStamp = storedparts.timeStamp
WHERE storedparts.AutoID Is Null;

MS ACCESS SQL Join Subquery

2 Answers2