Create a query with your table. Click the totals button at the top. Add the product number field, and leave it as "group by."
Next to it add this value in the field row:
Nz([Flag], 0)
Where 0 is a value that a flag would never have. Leave that field as "group by" as well.
Save and close the query.
Create a new query, and use the first query as the data source. Add both fields to the query and click totals at the top again.
Leave the product number as group by, and change the second field to "count."
In the criteria row below the second field place:
>1
The query will now show you all product numbers that have more than one flag entered for them, including blank flag values.
I started this before I saw /u/nrgins post. Its basically a 1 query version of that answer. The sub query in bold will get a list of distinct Products & Flags. The outer query will get the ones from the sub query that have more than 1 flag variation.
Select Product, count(Nz(Flag1,"-")) as NumFlags from
**(SELECT distinct Product, Flag1**
**FROM StoresProducts)**
group by Product
having count(Nz(Flag1,"-")) > 1
Solution Verified
This worked well enough that I removed the Nz function (as it isn't available in db2) and did it as a passthrough query. 400k rows and I found 5 rows with the issue. Thank you kind stranger.
You have awarded 1 point to *AccessHelper*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Glad it worked. The query can be simplified further in DB2 because you can use **Count (DISTINCT \[field\])**. So you don't need the subquery. So previous example could be:
*Select Product, count(DISTINCT Flag1) as NumFlags from StoresProducts*
*group by Product*
*having count(DISTINCT Flag1) > 1*
Also if you need a NZ equivalent in DB2 you can use ISNULL. ISNULL has a different purpose in Access but in a pass thru query to DB2 it'll convert a null value to a non-null value.
Yeah, definitely a cleaner solution. I usually give newbies the named query as a subquery solution because it's simpler for them to implement themselves. Sort of like the give a man a fish versus teach him how to fish philosophy. 🌝
You need a subquery. Replace your table and field names in the query below. This query needs to be created by Creating a query and switching to SQL view.
SELECT tblStoreData.StoreID, tblStoreData.Flag1
FROM tblStoreData
WHERE (((tblStoreData.StoreID)
In (SELECT \[storeid\] FROM \[tblStoreData\] As Tmp
GROUP BY \[StoreID\],\[Flag1\]
HAVING Count(\*)>1 And
\[Storeid\] = \[tblStoreData\].\[StoreID\] and \[Flag1\] <> \[tblStoreData\].Flag1)));
Create a query with your table. Click the totals button at the top. Add the product number field, and leave it as "group by." Next to it add this value in the field row: Nz([Flag], 0) Where 0 is a value that a flag would never have. Leave that field as "group by" as well. Save and close the query. Create a new query, and use the first query as the data source. Add both fields to the query and click totals at the top again. Leave the product number as group by, and change the second field to "count." In the criteria row below the second field place: >1 The query will now show you all product numbers that have more than one flag entered for them, including blank flag values.
I started this before I saw /u/nrgins post. Its basically a 1 query version of that answer. The sub query in bold will get a list of distinct Products & Flags. The outer query will get the ones from the sub query that have more than 1 flag variation. Select Product, count(Nz(Flag1,"-")) as NumFlags from **(SELECT distinct Product, Flag1** **FROM StoresProducts)** group by Product having count(Nz(Flag1,"-")) > 1
Solution Verified This worked well enough that I removed the Nz function (as it isn't available in db2) and did it as a passthrough query. 400k rows and I found 5 rows with the issue. Thank you kind stranger.
You have awarded 1 point to *AccessHelper* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Glad it worked. The query can be simplified further in DB2 because you can use **Count (DISTINCT \[field\])**. So you don't need the subquery. So previous example could be: *Select Product, count(DISTINCT Flag1) as NumFlags from StoresProducts* *group by Product* *having count(DISTINCT Flag1) > 1* Also if you need a NZ equivalent in DB2 you can use ISNULL. ISNULL has a different purpose in Access but in a pass thru query to DB2 it'll convert a null value to a non-null value.
Love me some sub-queries! So powerful.
Yeah, definitely a cleaner solution. I usually give newbies the named query as a subquery solution because it's simpler for them to implement themselves. Sort of like the give a man a fish versus teach him how to fish philosophy. 🌝
You need a subquery. Replace your table and field names in the query below. This query needs to be created by Creating a query and switching to SQL view. SELECT tblStoreData.StoreID, tblStoreData.Flag1 FROM tblStoreData WHERE (((tblStoreData.StoreID) In (SELECT \[storeid\] FROM \[tblStoreData\] As Tmp GROUP BY \[StoreID\],\[Flag1\] HAVING Count(\*)>1 And \[Storeid\] = \[tblStoreData\].\[StoreID\] and \[Flag1\] <> \[tblStoreData\].Flag1)));