Query to return specific text if any record in a field matches specific text (1 Viewer)

hubelea

Registered User.
Local time
Today, 12:47
Joined
Nov 9, 2011
Messages
33
My database tracks products - the product table includes descriptors like UPC, Category, Brand, Description, Size, and also tracks whether a specific product is approved at State level. The approval status is maintained within the JnctnStateUPC table at the SUUPC level in the RecentStatus field.

I'm trying to use that data to determine if a BRAND is approved. For example, if any of the RecentStatus records at SUUPC level indicate the UPC is approved, then I want to populate a field that says "Approved". Here are my tables
1631722620271.png


Here are the query results I'd like to see:

State-- Brand-- State Brand Status (assume this is a calculated field)
I would use criteria within the brand to select the brand, and the results I would then like to see would be one record for each state. Right now I've got the query written using calculated/expression fields to determine the State Brand Status, but that is giving me the results at SUUPC level. Again, for a specified brand, if any SUUPC is approved, I'd like to show that the brand is approved within that state.

Help?
 

plog

Banishment Pending
Local time
Today, 12:47
Joined
May 11, 2011
Messages
10,651
Query help is best achieved by data. Please provide 2 sets to demonstrate your issue:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. After you feed the data from A into this yet to be built query, what should the data be that you get out?

Again, 2 sets of data--starting and expected results based on the starting.
 

hubelea

Registered User.
Local time
Today, 12:47
Joined
Nov 9, 2011
Messages
33
Sorry, thought giving you the tables with the fields would work.

Here's the first query to bring together info from the product table and the JnctnStateUPC table

1631727097435.png

Here are the results of that query:

1631727242410.png


Note that in AK there are some items approved and some not allowed. Here are the results I'd like to see: One record for each state. No UPC field. If any records for that state in the query results above are "Approved", then the BrandStatus would be Approved. Conversely, if NO records within that state are approved, then the BrandStatus would be "Not Approved"

1631727488164.png
 

Attachments

  • 1631727349258.png
    1631727349258.png
    11 KB · Views: 94

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:47
Joined
May 21, 2018
Messages
5,686
Do a group by query where recentstatus = approved. Group on State, SCPCat, BrandLookup.
 

plog

Banishment Pending
Local time
Today, 12:47
Joined
May 11, 2011
Messages
10,651
I see two images with data so I will use those as the A and B I requested. You didn't provide a name for the A data, so I will call it A.

This query will get you from A to B:

Code:
SELECT SCPState, SCPCat, BrandLookup, MIN(RecentStatus) AS BrandStatus
FROM A
GROUP BY SCPState, SCPCat, BrandLookup
HAVING MIN(RecentStatus)="Approved"

If that doesn't work, reread my post and provide me exactly what I requested.
 

hubelea

Registered User.
Local time
Today, 12:47
Joined
Nov 9, 2011
Messages
33
OK, that seems to move me in the right direction - brings me the list of states where at least one UPC is approved. Now how do I get it bring back "Not Approved" so that I wind up with the entire list of states?
Do a group by query where recentstatus = approved. Group on State, SCPCat, BrandLooku
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:47
Joined
Feb 19, 2002
Messages
33,681
You would NOT store the approved status. You would determine the status by joining to the JunctinStateUPC table. Use a left join. and add an IIf() to return the status.

Select ...., IIf(RecentStatus & "" = "", "Not Approved", RecentStatus) As Status
From ...
 

hubelea

Registered User.
Local time
Today, 12:47
Joined
Nov 9, 2011
Messages
33
I've got it everyone. Took a few separate queries and then joined them. Thanks for your help!
 

Users who are viewing this thread

Top Bottom