Building a query to filter records

[frankt68]

Hi!

I have a table with the following fields:
Group_ID
Offer_ID
Notes
Total _ sum
Selection

I would like to build a query that would show only those records (Offer_ID) for each Group_ID that are not selected and for which the sum is less than the sum of the selected Offer_ID.



Please advise how can I do that.
I'm using access 2010 and don't have a lot of experience with using it.

 

[Minty]

Can you post up some sample table data, and the expected query result - make sure you have enough sample data to cover all your possible outcomes.
A small spreadsheet will be adequate, or a simple table, with the desired results obvious

 

[frankt68]

Hi!

Thank you for your reply. I had to prepare sample tables, which took me some time ...
I uploaded a sample file with two tables. Table1 contains "original" records. Table2 contains records that I would like to get with the help of a query.

Additional explanation of the wanted results:
Case 1
As you can see, in Table1 there are two records with Group_ID number 2 (Offer_ID 394 and Offer_ID 1). The second one (Offer_ID 1) is selected, however, it has a higher total value as the first one. So, in Table2, there is only one record with Group_ID 2 (Offer_ID 394) because it is not selected and has a lower total value as the record with Offer_ID 1.

Case2
In Table1, there are three records with Group_ID 14 (Offer_ID 1075, Offer_ID 395, Offer_ID 856). Offer_ID 1075 is selected and has the lowest total. Since there is no record with Group_ID 14 that would have a lower total than the selected Offer_ID 1075, there is no record with Group_ID 14 in Table2.

Case3
Table1 contains seven records with Group_ID 25 (Offer_ID 402
401, 116, 115, 400, 859, 288). None of the records is selected, so all seven records are listed in Table2. It would be great if the Query would also filter out all Group_ID records without selection, so none of these records would show up in query results.

Hope this helps.

 

[Minty]

You'll need a couple of sub queries. I'll post back up when I have a chance to look at it.

 

[arnelgp]

SELECT Table1.Group_ID AS T1_GROUP_ID, Table1.Offer_ID AS T1_OFFER_ID, Table1.Total_Sum AS T1_TOTAL_SUM, TM.GROUP_ID AS T2_GROUP_ID, TM.OFFER_ID AS T2_OFFER_ID, TM.TOTAL_SUM AS T2_TOTAL_SUM
FROM Table1 LEFT JOIN (SELECT Table2.Group_ID, Table2.Total_Sum, Table2.Offer_ID
FROM Table2
WHERE (((Table2.Group_ID) In (SELECT Table1.Group_ID
FROM Table1
WHERE (((Table1.Selection)=True))
ORDER BY Table1.Group_ID)) AND ((Table2.Total_Sum)<(SELECT TOP 1 T1.TOTAL_SUM FROM TABLE1 AS T1 WHERE T1.GROUP_ID=TABLE2.GROUP_ID AND T1.SELECTION=TRUE)))) AS TM ON TABLE1.GROUP_ID=TM.GROUP_ID
WHERE (((Table1.Selection)=True)) ORDER BY TABLE1.GROUP_ID ASC;

 

[frankt68]

Hi arnelgp and thank you for your reply.

Unfortunately, your proposed solution does not give the wanted results.
I think I was not clear enough in explaining what I want.
So I'll try to explain it again.
I have a table with records like those in Table1. I would like to filter the records like in Table1 in such a way that I only get records with the same Group_ID, which are not selected and have a sum smaller than the record that is selected.
Examples of the wanted results are summarized in Table2, that I have prepared manually (Table2 does not exist in the original database). So Table2 represents the filtered records from Table1 after using the query (or more of them).

I hope this makes sense.

 

[arnelgp]

SELECT TABLE1.GROUP_ID, TABLE1.TOTAL_SUM, TABLE1.OFFER_ID, TABLE1.NOTES, TABLE1.SELECTION
FROM TABLE1
WHERE (((TABLE1.GROUP_ID) In (SELECT Table1.Group_ID FROM TABLE1 WHERE Table1.Selection=True)) AND ((Nz([TABLE1].[TOTAL_SUM]))<(SELECT TOP 1 T1.TOTAL_SUM FROM TABLE1 AS T1 WHERE T1.GROUP_ID=TABLE1.GROUP_ID AND T1.SELECTION=TRUE))) OR (DCOUNT("*","TABLE1","GROUP_ID=" & TABLE1.GROUP_ID & " AND SELECTION=FALSE")=DCOUNT("*","TABLE1","GROUP_ID=" & TABLE1.GROUP_ID))
ORDER BY TABLE1.GROUP_ID;

 

[frankt68]

Thanx arnelgp.
That's exactly what I wanted.