Solved Creating a query with unique records using complicated filtering fields? (1 Viewer)

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
I have Table1 that I want to combine with Table2 to create a query.

Table1 has the following column fields:

GroupID | MyName| Age | Animal

The table has the following fields:

AnimalID | Animal | Rating | Mood

I have a relationship set up linking `AnimalID`, `Animal`, and `Gender` from Table1 to Table2, which looks like this:

Code:
| Table1|         |Table2|
GroupI
MyName
Age
Rating             AnimalID
Animal   ->     Animal
                        Rating
                        Mode


If I made a query out of the above, it would look something like this:

Code:
GroupID   MyName    Age      Rating    Animal      Mood
  17      Kevin     1        1         Lion        Good
  17      Kevin     1        1         Leopard     Bad
  17      Kevin     1        2        Leopard      Bad
  17      Kevin     1        3        Tiger      Good
  17      Kevin     1        3        Lion        Good
  17      Kevin     1        3        Leopard      Bad
  17      Kevin     1        4        Giselle      Good
  17      Kevin     1        4        Tiger      Bad
  18      Kevin     2        2        Hippo      Good
  18      Kevin     2        2        Cheetah      Good
  18      Kevin     2        3        Cheetah      Good
  19      Kevin     3        1        Leopard      Bad
  19      Kevin     3        2        Leopard      Bad
  19      Kevin     3        3        Leopard      Bad
  20      David     1        1        Leopard      Bad
  20      David     1        2        Cheetah      Good
  20      David     1        2        Leopard      Bad
  20      David     1        3        Cheetah      Good
  20      David     1        3        Leopard      Bad
  21      David     3        3        Zebra      Bad
  23      John     2        1        Lion      Good
  23      John     2        3        Lion      Good
  24      Henry     1        3        Buffalo      Good
  24      Henry     1        5        Baboon      Bad

GroupID links the Name and Age together. Each Animal belongs in a Rating, which has a Mood, which belongs in each GroupID. Table2 does not have any records with the same Animal and Rating appearing twice.

However, what I would like to display in a query is a filtering where, for each GroupID, display the MyName, Age, Rating, Animal, and Mood where the Rating is unique for each Age for each GroupID, i.e. It's a One (Age) to Many (Rating) relationship. If all the records in that GroupID has Mood as Good, filter the first record. Otherwise, if any one record for that GroupID has Mood as Bad, display the first Bad record.

For example, this is how I would expect the query to look:

Code:
GroupID   MyName    Age      Rating    Animal      Mood
  17      Kevin      1            1       Leopard      Bad
  17      Kevin      1            2       Leopard      Bad
  17      Kevin      1            3       Leopard      Bad
  17      Kevin      1            4       Tiger           Bad
  18      Kevin      2            2       Hippo         Good
  18      Kevin      2            3       Cheetah      Good
  19      Kevin      3            1       Leopard      Bad
  19      Kevin      3            2       Leopard      Bad
  19      Kevin      3            3       Leopard      Bad
  20      David     1            1       Leopard      Bad
  20      David     1            2       Leopard      Bad
  20      David     1            3       Leopard      Bad
  21      David     3            3       Zebra           Bad
  23      John       2            1       Lion              Good
  23      John       2            3       Lion             Good
  24      Henry     1            3       Buffalo       Good
  24      Henry     1            5       Baboon      Bad

For the records where Cheetah and Hippo and `Good`, displaying the first record above as shown above is sufficient:

Code:
     18     Kevin      2      2      Cheetah     Good
     18     Kevin      2      2      Hippo       Good

I am completely lost as to where I can even began to start handling this difficult filtering criteria. How can I achieve the above results in SQL?

Please see attached demo for reference and I apologize for the weird spacing.

EDIT: I posted in the wrong thread. MOD please move to Queries.
 

Attachments

  • Database11.accdb
    428 KB · Views: 129

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
If all the records in that GroupID has Mood as Good, filter the first record. Otherwise, if any one record for that GroupID has Mood as Bad, display the first Bad record.
it is difficult to create a query that you want.
maybe instead of query you can put the result you want in a Temporary table (in my case Output table).
open Output Form.
see the Code on the Open / Load event of the form.
 

Attachments

  • Database11.accdb
    504 KB · Views: 39

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
it is difficult to create a query that you want.
maybe instead of query you can put the result you want in a Temporary table (in my case Output table).
open Output Form.
see the Code on the Open / Load event of the form.
Hey @arnelgp, appreciate your response with this but the Output table doesnt look quite right. There's only one unique GroupID. Maybe I confused you with my requirements.

The same GroupID value can be repeated which is why it's not a Primary Key. Only the "same" Rating cannot belong to the same Age for that "specific" GroupID record. You can have have multiple of the same GroupID record with the same Age, but you just can't have the same Rating belonging to that Age.

For example, take a look at these records for GroupID = 17:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LionGood
17​
Kevin
1​
1​
LeopardBad
17​
Kevin
1​
2​
LeopardBad
17​
Kevin
1​
3​
LionGood
17​
Kevin
1​
3​
LeopardBad
17​
Kevin
1​
3​
TigerGood
17​
Kevin
1​
4​
TigerBad
17​
Kevin
1​
4​
GiselleGood

I would inspect the output table to display this filter:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LeopardBad
17​
Kevin
1​
2​
LeopardBad
17​
Kevin
1​
3​
LeopardBad
17​
Kevin
1​
4​
TigerBad

As you can see, there is a different Rating for the same Age of the same GroupID, i.e. Kevin. That's where the complicated part of determining how to filter 2 records with the same Rating linking to the same Age of the same GroupID, and only filtering out the first record that is Bad, or if both records are Good, then filter out that first Good record.

In the first case above we have 2 records like so:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LionGood
17​
Kevin
1​
1​
LeopardBad
Since one of the record is Bad, the filtered record that should be displayed is the one containing the Leopard record only.

For GroupID = 18, we have 2 records with the same Rating linking to the same Age:

GroupIDMyNameAgeRatingAnimalMood
18​
Kevin
2​
2​
HippoGood
18​
Kevin
2​
2​
CheetahGood


Since both have a Mood of Good, filter and display the first record in the Output table.

@arnelgp is this something that can be achieved, or is it too logically difficult with too many variables to play with?
 

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
I changed your Query2 to also Group by Age and Rating, which produces the output I want:



GroupIDAgeRatingCountGoodCountBadCountGroup
17​
1​
1​
1​
1​
2​
17​
1​
2​
0​
1​
1​
17​
1​
3​
2​
1​
3​
17​
1​
4​
1​
1​
2​
18​
2​
2​
2​
0​
2​
18​
2​
3​
1​
0​
1​
19​
3​
1​
0​
1​
1​
19​
3​
2​
0​
1​
1​
19​
3​
3​
0​
1​
1​
20​
1​
1​
0​
1​
1​
20​
1​
2​
1​
1​
2​
20​
1​
3​
1​
1​
2​
21​
3​
3​
0​
1​
1​
23​
2​
1​
1​
0​
1​
23​
2​
3​
1​
0​
1​
24​
1​
3​
1​
0​
1​
24​
1​
5​
0​
1​
1​
@arnelgp As you can see, there is a different Rating for each Age. The only difficult part is to display the Output table with the Good record if CountBad is 0, otherwise if CountBad is not 0, display the Bad record.

I'm looking into your Form_Open code to see what I can come up with.

Here's the attached updated Demo if you want to inspect.
 

Attachments

  • Database11.accdb
    672 KB · Views: 116

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
I re-instate the Query i made (query2), that is the correct query.
see if this change in the code will do it for you.
 

Attachments

  • Database11 (1).accdb
    984 KB · Views: 89

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
This is the Ouput form from your latest:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1Bad
17​
Kevin
1​
2Bad
17​
Kevin
1​
3Bad
17​
Kevin
1​
4Bad
18​
Kevin
2​
2​
CheetahGood
18​
Kevin
2​
2​
HippoGood
19​
Kevin
3​
1Bad
19​
Kevin
3​
2Bad
19​
Kevin
3​
3Bad
20​
David
1​
1Bad
20​
David
1​
2Bad
20​
David
1​
3Bad
21​
David
3​
3Bad
23​
John
2​
3​
LionGood
24​
Henry
1​
5Bad

The only issue is in regards to GroupID 23 and 24.

In Query 1, there's the following:

GroupIDMyNameAgeRatingAnimalMood
23​
John
2​
1​
LionGood
23​
John
2​
3​
LionGood
24​
Henry
1​
3​
BuffaloGood
24​
Henry
1​
5​
BaboonBad
Since there's a One-To-Many relationship with GroupID 23, where Age = 2 -> Rating = 1, and Age = 2 -> Rating = 3, I should expect 2 records. There's also 2 records for GroupID 24, where Age = 1 -> Rating = 3, and Age = 1 -> Rating = 5.

The Output should look something like this:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1Bad
17​
Kevin
1​
2Bad
17​
Kevin
1​
3Bad
17​
Kevin
1​
4Bad
18​
Kevin
2​
2​
CheetahGood
18​
Kevin
2​
2​
HippoGood
19​
Kevin
3​
1Bad
19​
Kevin
3​
2Bad
19​
Kevin
3​
3Bad
20​
David
1​
1Bad
20​
David
1​
2Bad
20​
David
1​
3Bad
21​
David
3​
3Bad
23​
John
2​
1​
LionGood
23​
John
2​
3​
LionGood
24​
Henry
1​
3​
BuffaloGood
24​
Henry
1​
5​
BaboonBad

Also, the Rating and Animal is not displaying correctly, but I think that's an easy fix. I'll look into this some more.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
why does 24 have a Good record, when you're Own rule state to output just the Bad if there is At Least a bad record?
 

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
why does 24 have a Good record, when you're Own rule state to output just the Bad if there is At Least a bad record?
If there are 2 records with the same GroupID where one is Bad and one is Good, and the Age points to the same Rating for both records, then choose the record that is Bad to Output.

However, if there are 2 records with the same GroupID where one is Bad and one is Good, but the Age "does not" points to the same Rating for both records, then both records should be Output.

In this example, one record of Henry has Age = 1 -> Rating = 3, while the other record of Henry has Age = 1 -> Rating = 5. Therefore, both records should be output separately since the Age does not point to the same Rating for both records.

I hope that is clear, and I apologize for the confusion in the requirements.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
ok, check this again.
 

Attachments

  • Database11 (1).accdb
    672 KB · Views: 87

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
ok, check this again.
Looks almost good except one repeated record. Here is the Output:

IDGroupIDMyNameAgeRatingAnimalMood
210​
17​
Kevin
1​
1Bad
211​
17​
Kevin
1​
2Bad
212​
17​
Kevin
1​
3Bad
213​
17​
Kevin
1​
4Bad
214​
18​
Kevin
2​
2Good
215​
18​
Kevin
2​
3Good
216​
18​
Kevin
2​
2Good
217​
19​
Kevin
3​
1Bad
218​
19​
Kevin
3​
2Bad
219​
19​
Kevin
3​
3Bad
220​
20​
David
1​
1Bad
221​
20​
David
1​
2Bad
222​
20​
David
1​
3Bad
223​
21​
David
3​
3Bad
224​
23​
John
2​
1Good
225​
23​
John
2​
3Good
226​
24​
Henry
1​
5Bad
227​
24​
Henry
1​
3Good


And here is the issue:

IDGroupIDMyNameAgeRatingAnimalMood
214​
18​
Kevin
2​
2Good
215​
18​
Kevin
2​
3Good
216​
18​
Kevin
2​
2Good
The first and last record should be combined as one Good record because they both have Age = 2 -> Rating = 2.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
here again.
 

Attachments

  • Database11 (1).accdb
    564 KB · Views: 116

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
here again.
Hey @arnelgp, sorry to keep bugging you about this but the same issue still appears on the latest Output:



GroupIDMyNameAgeRatingAnimalMood
18​
Kevin
2​
2​
CheetahGood
18​
Kevin
2​
3​
CheetahGood
18​
Kevin
2​
2​
HippoGood
There should only be 2 records there. One is Age = 2 -> Rating = 3, Cheetah, Good, and the other record should be Age = 2 -> Rating = 2, Cheetah, Good.

So it should look like this in Output:


GroupIDMyNameAgeRatingAnimalMood
18​
Kevin
2​
2​
CheetahGood
18​
Kevin
2​
3​
CheetahGood
Sorry I'm playing around with your latest code to try and fix it myself, but I'm not experienced enough in VBA.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
again please.
 

Attachments

  • Database11 (1).accdb
    564 KB · Views: 112

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
i think we got it correct?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
no problem, it became clear from your example results.
 

6thDAY

Member
Local time
Today, 07:17
Joined
Oct 10, 2021
Messages
36
no problem, it became clear from your example results.
Apologies for bring this solved thread back from the dead, but I modified some records, and it doesn't meet the requirements as we discussed previously.

Let me show an example. In the original fixed you made, Query1 looks like so:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LionGood
17​
Kevin
1​
1​
LeopardBad
17​
Kevin
1​
2​
LeopardBad
17​
Kevin
1​
3​
LionGood
17​
Kevin
1​
3​
LeopardBad

For GroupID=17, we have Kevin in 3 groups:
  1. Age = 1 ---> Rating = 1, Good (Lion) and Bad (Leopard)
  2. Age = 1 ---> Rating = 2, Bad (Leopard)
  3. Age = 1 ---> Rating = 3, Good (Lion) and Bad (Leopard)
The final Output:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LeopardBad
17​
Kevin
1​
2​
LeopardBad
17​
Kevin
1​
3​
LeopardBad
However, because I never showed a group (i.e. same Age and Rating) where there were 2 or more records that all have Mood = Good, the code works based on the data I provided you.



But now I've updated Query1 to show the following:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LionGood
17​
Kevin
1​
1​
LeopardGood
17​
Kevin
1​
2​
LeopardBad
17​
Kevin
1​
3​
LionGood
17​
Kevin
1​
3​
LeopardGood
17​
Kevin
1​
3​
TigerGood


However, the only output I get for GroupID=17 is the following:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
2​
LeopardBad

But the EXPECTED Output should be:

GroupIDMyNameAgeRatingAnimalMood
17​
Kevin
1​
1​
LionGood
17​
Kevin
1​
2​
LeopardBad
17​
Kevin
1​
3​
LionGood
I've attached the database with the issue. Can you further look into this? I'm currently doing the same. Thanks @arnelgp
 

Attachments

  • Complicated Filter.accdb
    832 KB · Views: 88

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:17
Joined
May 7, 2009
Messages
15,037
there is an age: 1, rating: 4, animal: Tiger, mood: good on groupid = 17?
 

Attachments

  • Complicated Filter.accdb
    560 KB · Views: 109

Users who are viewing this thread

Top Bottom