Creating a query to look for data that a field does not have. 1

[timely]

I am working on an application that will allow the users here to look at stocks and see who does not have stocks that are recomended as a buy. I first created a list of what they did have and then a query of what is recommended as buys. The problem now is if someone has several stocks and one may be the recommended as a buy but since the other is a diffrent stock it still compares with that and makes the report say they do not have it. I do not have any VBA books on hand to look up how I can do it that way and the Query methods I've tried have all failed.

I was trying to see if anyone knew of a way to do a query like this.

Thanks.

 

[JimHorton]

Timely,
A very simplified version---if you have, say a table with the currently recommended buys, lets say it's called tblBuys, with one field, Stock, and the customer's stock table, say it's tblCustStocks with fields CustID,CustName and Stock, a query might look like this:
SELECT B.STOCK,A.CUSTID,A.CUSTNAME FROM TBLBUYS B,TBLCUSTSTOCK A WHERE B.STOCK NOT IN (SELECT STOCK FROM TBLCUSTSTOCK C WHERE C.CUSTID = A.CUSTID)
--Jim

 

[timely]

I tried the code that you recommended and it worked but it was extremely slow.

 

[CosmoKramer]

How about:

SELECT DISTINCTROW Table1.Num, Table1.LastName, Table1.FirstName
FROM Table1 LEFT JOIN Table2 ON Table1.Num = Table2.Num
WHERE (((Table2.Num) Is Null))

Or, you can use the Find Unmatched query wizard......

 

[JimHorton]

Timely,
This type of query will be slower, but if CustID and Stock are indexed, it will be faster.

Cosmo,
In your example, he needs values from *each* table, which is why the IN clause is needed, ie you would need to put Table2.Num in the results somehow.
--Jim

 

[timely]

Jim,

Ok The problem is with the indecies is that query uses other queries and not directly using a table. Then for the queries that are created prior are linked in one case to a excel spreadsheet and I can not put a index on a link of a excel spreadsheet. Now I have not tried to put a index on the other tables that are linked from a foxpro system and am not sure yet if I can index those or not. The first run of the quries took almost an hour and when I tried to put an order by I had to stop it after an hour and half.

Maybe I would have all my prior quries going to tables and then place indecies on those?

I do agree that the one from cosmo is not plausable due to the information is created from two diffrent queries.


Tim

 

[JimHorton]

Tim,
When doing more complex stuff on linked non-isam tables (like excel), it's really tough to get any performance out of it. If the data is semi-static (such as a daily list of trades or daily list of buy recommendations, as opposed to the trades being live-updated, which I doubt if it's in excel) then I highly suggest automating a simple import routine so you can work with native-jet data. I've done alot of work with trading houses, and we have automated processes that import data that the exchange will send. Typically I'll be using Oracle for these with Access front-end, but I've done several with pure Access, and queries quite similar to the one in the example run in a matter of seconds, even when the trades list is in 6 digits. If this is feasible, I'd reccomend bringing all the data into access.
--Jim

 

[timely]

Jim

I will start doing that tomarrow. I have alot of table to created due to they are all inported from other programs. I hope that will take care of the speed issue. I am not use to using so much data with access. I was an Oracle Programmer for over a year and love the power it had. Unfortunatly the small firms either can not afford it or the owner just does not want to shell out cash for a real database system.

Tim

 

[timely]

Jim,

Thanks with the help, it is working much better now that I made tables to hold the data and put indecies on. I may have other questions in the future.

Thanks,
Tim