Include/Exclude records

[austenr]

I have a large table that I need to write a query for. I also have two other tables, one that holds the codes for the records I want to INCLUDE and report on and one that have the codes I want to EXCLUDE. The INCLUDE table has over 100 items and the EXCLUDE table has over 600 items. Obviously you cannot define them in the code and I am a little lost on how to approach this. I figure a dynamic array would work but have never written one before successfully.

I have three criteria in each table that makes them either INCLUDEABLE or EXCLUDEABLE records so I guess it is a multidimensional array I am looking for.


Can anyone provide some direction?

[geekgirlau]

In your query for the large table, try something like this (obviously you'll need to change field and table names):

SELECT *
FROM Claims
WHERE (ItemNo In (SELECT ItemNo FROM Include)) AND (ItemNo Not In (SELECT ItemNo FROM Exclude));

[stanl]

another approach you might try



SELECT * FROM Claims
WHERE EXISTS (SELECT ItemNo FROM Include WHERE Include.ItemNo=Claims.ItemNo);


Stan

[austenr]

Thanks for the replies Geek Girl and stanl. I got past my original problem Geek Girl with your code however I am having another matter as well.

Here is what I am trying to accomplish.

I have two tables:

Retired Price List and Retirement Service Code Increases.

What I am trying to accomplish with the code below is match the Inst Number and the Price List Number from the Retired Price List table to the Bank Number and Price List in the Retirement Service Code Increases table to return the Service Code, Bank Number and Price List which is in the Retirement Service Code Increases table. So I want a new table called Matching Service Codes to be populated after the query runs.

I have tried running the code below and I keep getting a pop up box asking for a parameter. I do not understand why this box is coming up and also I do not know how to create the new table and populate it. I have attached the sample database as well. I would appreciate any help. :banghead:

SELECT *
FROM [Retired Price List] INNER JOIN [Retirement SVC Codes Increases] ON [Retired Price List].[Inst Number] = [Retirement SVC Codes Increases].[Bank Number]
WHERE
[Retired Price List].[Inst Number] = [Retirement SVC Codes Increases].[Bank Number]
AND
[Retired Price List].[Price List Number] = [Retirement Service Code Increases].[Price list]