Query Based on Passing Criteria

[MatthewH]

Hey everyone,
I'm attempting to make a table based on a weird criteria, was hoping I can explain it well here and someone can help me.

I get a weekly table of products that come into a warehouse. Each product has a unique ID and this table also contains the company it comes from (lets say Amazon for example). I also have their history, how many have been damaged, opened, or returned in another table (This table contains only these columns: Company ID|# of damaged|# of opened|# of returned). In my THIRD table I have the requirements for my filter that I'm going to be using (# of damaged|# of opened|# of returned). I want to put items that pass the requirements into a table (and remove them from the current table)

For example: Requirement is 4 damaged, 3 opened, or 5 returned in their history. Table 1 would go through each product ID and read the Company ID. If the company ID has a history of greater than or equal to 4 damaged OR 3 opened OR 5 returned, it will put it into a separate table that will be called "WorryProducts".

I started making the query and couldn't get the expression to be right. Here's what I got:

Expr1: IIf([Historical Data]![DamagedCount]>=[Requirement Count]![NumDamaged],"Yes",IIf([Historical Data]![Historical Data]>=[Requirement Count]![NumOpened],"Yes",IIf([Historical Data]![ReturnedCount]>=[Requirement Count]![NumReturned],"Yes","No")))

I made this a field as I thought it would allow them to filter properly. I set the Criteria in the query to "Yes".
In the query I linked [Product ID] from table [WeeklyTable] to [Product ID] from [Historical Data]. Please let me know what's going wrong or how I can fix this process and get it working.

I also recognize this isn't getting rid of the data from the other table, but that's the next step after this (which I assume would just be another query.) For now I'm focused more on getting this table to include the pertinent data.

-Matt

 

[CJ_London]

would be easier to understand if you provided the full sql rather than just part of the criteria. Also, what does 'couldn't get the expression to be right' mean? - it returns the wrong results? if so what does it return? you get an error? - is so what is the error?

At the moment all I can suggest is [Historical Data]![DamagedCount] should be [Historical Data].[DamagedCount]

 

[MatthewH]

SELECT [WeeklyTable].[Column1], [WeeklyTable].[Column2], [WeeklyTable].[Column3], [WeeklyTable].[Column4], [WeeklyTable].[Column5], [WeeklyTable].[Column6], [WeeklyTable].[Column7], [WeeklyTable].[Company ID], [WeeklyTable].[Column9], [WeeklyTable].[Column10], [WeeklyTable].[Column11]
FROM [Requirement Count], [Historical Data] INNER JOIN [WeeklyTable] ON [Historical Data].[Company ID] = [WeeklyTable].[Company ID]
WHERE (IIf([Historical Data]![DamagedCount]>=[Requirement Count]![NumDamaged],"Yes",IIf([Historical Data]![Historical Data]>=[Requirement Count]![NumOpened],"Yes",IIf([Historical Data]![ReturnedCount]>=[Requirement Count]![NumReturned],"Yes","No"))))="Yes"));

This was the code, edited a bit (just names) to keep things anonymous that were needed haha. Hope you can help me from here!!

 

[MatthewH]

I think I should add that I get the error: "Data type mismatch in criteria expression" which I feel is telling me that my WHERE statement is incorrect but I've checked and double and triple checked them and can't seem to find anything.

This is my WHERE statement, I've just changed it:

WHERE (((IIf([Historical Data].[DamagedCount]>=[Requirement Data]![NumDamaged],"Yes",IIf([Historical Data].[Approved Closes Per ECI]>=[Requirement Data]![NumOpened],"Yes",IIf([Historical Data].[Approved Links Per ECI]>=[Requirement Data]![NumReturned],"Yes","No"))))="Yes"));

It may be important to note that there is only one row of data in that Requirement Data and I guess it's not being read correctly?
It would just have the 3 numbers, 4 5 6 for example.