Joining a 'long integer' field to a 'short text' number

[sneuberg]

Here an example of the WHERE NOT IN I was talking about. This might be close to what you are looking for.

SELECT Sales.InvoiceNo, Sales.OrderDateTime, Sales.ContactName
FROM Sales
WHERE CStr(Sales.InvoiceNo) NOT IN (SELECT SalesReceipt.RefNumber FROM SalesReceipt)

and http://www.fmsinc.com/Microsoftaccess/query/outer-join/index.html might be better than the last link I gave you.

 

[peskywinnets]

This looks quite hopeful - it worked on the test database, but in my real world deployment it just shows 'RUNNING QUERY' (I've a linked ODBC database - the one called SalesReceipt - that holds about 38,000 Reference numbers)

The query is still running after 5 minutes (I even added a bit of date criteria, but that didn't make any difference....it just shows 'RUNNING QUERY' (with access feeling unreceptive - though isn't showing 'NOT RESPONDING')

 

[peskywinnets]

Ok, bingo, that second URL had the SQL syntax I needed so here it is...

SELECT Sales.InvoiceNo, Sales.OrderDateTime
FROM Sales LEFT JOIN SalesReceipt ON CStr(Sales.InvoiceNo) = SalesReceipt.Refnumber
WHERE ((Sales.OrderDateTime)>#01/01/2016# and ((SalesReceipt.Refnumber) Is Null));

.....& the above works a treat - it has revealed some real world mismatches ....i.e. invoices in Access that haven't been imported to Quickbooks (when we thought they had).

What a relief...thanks for sticking with me

 

[sneuberg]

This looks quite hopeful - it worked on the test database, but in my real world deployment it just shows 'RUNNING QUERY' (I've a linked ODBC database - the one called SalesReceipt - that holds about 38,000 Reference numbers)

The query is still running after 5 minutes (I even added a bit of date criteria, but that didn't make any difference....it just shows 'RUNNING QUERY' (with access feeling unreceptive - though isn't showing 'NOT RESPONDING')

Thanks for letting me know that. If in the future I suggest something with a subquery as a solution I'll add the caveat that it's only good for small numbers of records. Glad the other way worked for you.

 

[Frothingslosh]

Sneuberg, I believe I read somewhere that Access basically calculates the subquery once for each record rather than running it once and using it like a filter the way SQL Server does. I know I've personally seen dramatically improved response times after replacing subqueries with 'stacked' queries.

Another failure point I've run into before with split databases is indexes - if there are any parameters on a non-indexed field, it seems like Access pulls in the entire recordset rather and then filters it rather than having the backend do it.

Because of those, if there's no feasible way to speed things up in a split database by tweaking the SQL, I just switch the query to a pass-through instead, which generally solves the speed problem.