Select Product Order data with multiple criteria

[my_sunshine]

Hi all,

I hope you can help me to extract a new data table with all the fields from the attached product order data, using Access SQL or Access Design View. Please note each ID has one (ID 16) or more quotes (ID 13) and the quotes within an ID can be the same (A1) or different (A2-A4) but each row of data is an unique combination of the columns' values. The data is selected based on the Primary (0 or 1), Last change date, and no Product B nor Product C.

[TABLE="width: 600"]
<colgroup><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Judgment[/TD]
[TD]Primary[/TD]
[TD]Quote[/TD]
[TD]Products[/TD]
[TD]Quantity[/TD]
[TD]Last change date[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1 [/TD]
[TD]A1[/TD]
[TD]Product A[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8/8/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1 [/TD]
[TD]A1[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/8/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0 [/TD]
[TD]A2[/TD]
[TD]Product D Level 3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8/17/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD] A3[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/17/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD] A4[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0 [/TD]
[TD]A5[/TD]
[TD]Product D Level 3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A6[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/20/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A15[/TD]
[TD]Product D Level 3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8/17/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A7[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A7[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A7[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A7[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A7[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A7[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A7[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A8[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A8[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A9[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A9[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A10[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1 [/TD]
[TD]A10[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A11[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A11[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A11[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A11[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A11[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A11[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A12[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A12[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A12[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A12[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A12[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A12[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A12[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1 [/TD]
[TD]A13[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] A13[/TD]
[TD]Product D Level 3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A14[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A14[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A16[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A16[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A16[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A16[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0 [/TD]
[TD]A16[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A16[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] a17[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] a17[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1 [/TD]
[TD]a17[/TD]
[TD]… Product B ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] a18[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] a18[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] a18[/TD]
[TD]… product C ….[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
</tbody>[/TABLE]


The criteria are:

1) If an ID has only 1 quote with Primary = 0, and no Product B nor Product C, e.g. ID 16, we'll keep ID 16 quote A5 Product D level 3.
2) If an ID has multiple quotes with all the Primary = 0, e.g. ID 17, choose the quote with the latest Last Change date (8/27/16) and no Product B nor Product C. In this case, we'll keep ID 17 quote A7 Product A.
3) If an ID has all the Products = Product B or Product C, we'll NOT keep the ID e.g. ID 18.
4) If an ID has multiple quotes with more than 1 quote of Primary = 1 e.g. ID 13, we'll keep the ID that has no Product B nor Product C. In this case ID 13 quote A1 Product A, ID 15 quote A13 Product A and Product D Level 3 will be keep.
5) If an ID has multiple quotes and the quote with Primary = 1 has Product B or C, we will NOT keep the ID e.g. ID 14.

After the new data table is created, I'd like to add a new field (named Level 3) to the table to count the quantity of Level 3 for each ID, and subtract the same quantity from the Quantity field.

The new table will look like this:
[TABLE="width: 599"]
<colgroup><col span="4"><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Judgment [/TD]
[TD]Primary[/TD]
[TD]Quote[/TD]
[TD]Products[/TD]
[TD]Quantity[/TD]
[TD]Level 3[/TD]
[TD]Last change date[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD] A1[/TD]
[TD]Product A[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]8/8/2016[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD] A5[/TD]
[TD]Product D Level 3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] A7[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] A13[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD] A13[/TD]
[TD]Product D Level 3[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8/27/2016[/TD]
[/TR]
</tbody>[/TABLE]
Please help! Thank you so so so much!

 

[my_sunshine]

Has anyone got the solution?

I have the diagram of selecting the quotes, but don't know how to attached the pdf/jpeg to this message.

 

[my_sunshine]

Can an expert give me a conclusion if this task is doable in Access? If not, what would be your work-around suggestion? Which software would you use? Thank you so so much!

 

[Micron]

Looks to me like a succession of Union queries would be the way I'd attempt this. Simply put, if you include all the necessary fields and create a sql statement for each condition you outlined, you can assemble a set of records that satisfy your criteria. With the resulting query output, you can either populate an existing table, create a new one, or delete the records from the primary table that don't match the output. Research Union query if you don't know how they work. They're not too difficult to understand, but do have their design limitations. To create the individual sql portions of a Union query, I'd suggest creating separate select queries and copying the portions to your Union query. Keeping the select portion of each as a select query makes it easier to tweak a portion in design view since you cannot have a design view for a Union query.

 

[my_sunshine]

Thank you, Micron, for the advice. I've tried to create 4 tables for each of the conditions:
1) Table 1: IDs with the latest "last change date"
2) Table 2: IDs with NO product B nor product C
3) Table 3: IDs with Primary =1
4) Table 4: IDs with Primary =0

I used the Union query:
* Left Union Table 2 & Table 3 to a new table 5 (i.e. all rows of Table 2 and unique rows of Table 3).

Now I'm stuck with the next sql statement on table 5: if a data row of an ID with Primary =1 and have Product B or Product C, delete all rows of that ID. In this case, the new table 6 won't have ID 14 nor ID 18.

Please help.

Thank you so so much!

 

[Micron]

I think this is going somewhere I hadn't intended. I'll review your 5 sets of criteria and data and post back.

 

[Micron]

Well, I gave it a shot but I'm afraid there are too many parameters for me to figure out. I got as far as this, which seems to bring back the first two requirements but that might be a fluke. Sorry, but I have to admit defeat. I split up the products and level since they should not be mixed into the same field. Hopefully you have not done that.

SELECT tblSunshine.ID, Max(tblSunshine.Primary) AS MaxOfPrimary1, 
Max(tblSunshine.Quote) AS MaxOfQuote, Max(tblSunshine.Products) AS 
MaxOfProducts1, Max(tblSunshine.[Last change date]) AS [MaxOfLast change date]
FROM tblSunshine GROUP BY tblSunshine.ID
HAVING (((Max(tblSunshine.Primary))=0) AND ((Max(tblSunshine.Products)) Not In ("B","C")));

 

[my_sunshine]

Thank you, Micron. I'm wondering if SQL has a query that delete all rows of the same ID. I hope the query would help me to create the table 6.

 

[Micron]

If it is as simple as you just put it, that would be
DELETE tblName.FieldName
FROM tblName
WHERE (((tblName.FieldName)=[ID value]));

A delete query cannot also make a table.