Not In ('list'... 2

[bergis]

Hi
I am trying to write a query that looks in the database for entries that doesn't exist in a list. My problem is that Access don't accept the way I write th list. I'v tried every possible way: ('aaa','bbbb',.../("aaa","bbbb",..../('aaa';'bbbb';... and any other combination you can think of. What am I doing wrong?
Help much appreciated!

Kjell

 

[jes]

Hi,

Are you designing a query or are you writing the query in code?

In the query builder you can use the 'criteria' and 'OR' fields of the expression. So, for example, you can have an expression where you display Id where criteria = 1 or 2 or 3.

If you are building the expression use something like:

Select * From Table Where Id = '1111' or Id = '2222';

Obviously the above will get long winded if your list is more than a few items in which case use another table with your valid list items and use an inner join query.

Hope that helps,

Jes

 

[limpan]

You could use recordsets. Loop through a recordset of the table and add the entries that doesn´t match your list value to a temporary table. Then you repeat this procedure for every entry in the list. This way you´ll get a table with the entries you want.

There is probably an easier way, but not that I know of...

/Linus

 

[bergis]

Hi
Obviously I didn't explain this to well, but I see from the answers that there is an other, probably easier way to write this query (yes, I try to write it, not design it). I have a database with two tables (actually a lot more than two, but I'm only interested in two of them). Consider this: Table Report contains the data and table Replist contains the available report types in current configuration, but this database has existed for a long time and other configs has been in use, and therefore the database contains reports with naming outside of the current config. I want to compare the report.reporttype with replist.reporttype and list reports not in replist.reporttype. I don't know enough about sql/access to figure this out, but I need to find out quite soon, and since the report table contains 10000+ records, I figured that if I could make a query I would save myself some work.
TIA
Kjell

 

[tlbroadbent]

Use the "Find Unmatched Query Wizard" and it will guide you through creation of the query. The query will look like the following.

SELECT Report.*
FROM Report LEFT JOIN Replist ON Report.CostAcct = Replist.CostAcct
WHERE (Replist.CostAcct Is Null);
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost