Select common product in one table (1 Viewer)

amir0914 · Oct 7, 2019

Hi all, I have a table with two columns that the first column is box name and the second column name is product name and I want to select products that is common in all boxes, for example :

box.................product

box4.................. E
box1.................. A
box2.................. B
box3.................. C
box1.................. D
box4.................. A
box3.................. A
box2.................. D
box2.................. A
box2.................. E

result of query :

Product
A

Can I create the query?

ypma · Oct 7, 2019

Amir: Not sure from you example exactly what your result should be. If you are looking for duplicates IE Where Box and Product are the same ? Then use the query wizard to find duplicates. Hope this is of help to you?

Regards Ypma

Reread your post, and realise you want the product that is in all 4 boxes

arnelgp · Oct 7, 2019

first create a query (Query1) that will count how many box you have:

Code:

select box, product, count(box) as countObox from yourTable group by box, product

next create the final query (qryFinal)

Code:

select product from Query1 
where Query1.countOfBox = Dmax("countOfBox", "Query1")

theDBguy · Oct 7, 2019

Hi. Sounds like you're trying to figure out the MODE. Is that right?

amir0914 · Oct 7, 2019

Hi all and thanks for replying, I want to find product that is in all 4 boxes, it may be one product is in all box or tow products is in all box (i don't want to figure out maximum box)

plog · Oct 7, 2019

Without making any assumptions about your data and just using the table you provided this is going to take a few sub-queries and take a bit of processing.

First you will need a list of all the unique permutations of boxes/products:

Code:

SELECT box, product FROM YourTableNameHere GROUP BY box, product

Name that 'sub1'. Now, you need to know how many total boxes there are so you must create a query to get all unique boxes:

Code:

SELECT box FROM YourTableNameHere GROUP BY box

Name that 'sub2'. Now you can build the final query:

Code:

SELECT product FROM sub1 GROUP BY product
HAVING COUNT(product)=DCOUNT("box", "sub2")

Again, that's just using the data you provided in your first post. If we knew more about your data this might be easier. For example--a table that lists all boxes and/or table that lists all products, knowing that you will never "skip" a box (e.g. box 4 has product but box 3 is not used), etc.

theDBguy · Oct 7, 2019

amir0914 said:
Hi all and thanks for replying, I want to find product that is in all 4 boxes, it may be one product is in all box or tow products is in all box (i don't want to figure out maximum box)

Hi. As plog said, this might take a few queries to get to the end result. So, if you would like us to build it for you, you might consider posting a sample db with your data. Just a thought...

amir0914 · Oct 7, 2019

Hi plog, your are right theDBguy, I couldn't do it with plog guidance , I attach part of my database in this post, can you do it on my database?
(as I said I want to figure out products that are in all boxes)

View attachment SAMPLE.accdb

theDBguy · Oct 7, 2019

amir0914 said:
Hi plog, your are right theDBguy, I couldn't do it with plog guidance , I attach part of my database in this post, can you do it on my database?
(as I said I want to figure out products that are in all boxes)

View attachment 76833

Hi. This is one just one way to get what you want. There's probably other and better ways to achieve the same thing, but it was just something I could do really quickly. Please open the "final" query. Hope it helps...

arnelgp · Oct 7, 2019

using vba to get records.

amir0914 · Oct 7, 2019

Thank you theDBguy for replying but your way is hard to do because I have many boxes and i should create query for each boxes, it means if i have 20 boxes, I should create 20 query . thanks anyway

Thank you arnelgp, I used your query and module, that was great and my issue resolved.
but I have another question from you, I can figure out products in all boxes with your query, is there a way if I want to figure out products between only selected boxes ??
it means not all boxes and do it with only multiple criteria in "qryDistinctBox". is it possible?

Thanks in advanced.

theDBguy · Oct 7, 2019

amir0914 said:
Thank you theDBguy for replying but your way is hard to do because I have many boxes and i should create query for each boxes, it means if i have 20 boxes, I should create 20 query . thanks anyway

Thank you arnelgp, I used your query and module, that was great and my issue resolved.
but I have another question from you, I can figure out products in all boxes with your query, is there a way if I want to figure out products between only selected boxes ??
it means not all boxes and do it with only multiple criteria in "qryDistinctBox". is it possible?

Thanks in advanced.

Hi. You're welcome. As I said, there's a better way of doing the same thing than what I have shown you. Luckily, Arnel came to our rescue. Good luck with your project.

arnelgp · Oct 7, 2019

change the function.

to check all boxes: fncInAllBox([product], "")
to check selected boxes: fncInAllBox([product], "box1", "box2", "box4", etc..)

Code:

Public Function fncInAllBox(v As Variant, ParamArray boxes() As Variant) As Boolean
'
' pass null string ("") to include all box
'
' example fncInAllBox([product], "")
'
' for selected boxes, pass the box name separated by comma
'
' example fncInAllBox([product], "box1", "box2" [, boxN])
'    
    Dim intCount As Integer
    Dim db As DAO.Database
    Dim bolReturn As Boolean
    Set db = CurrentDb
    Dim var As Variant
    If Trim(v & "") = "" Then Exit Function
    If boxes(0) = "" Then
        With db.QueryDefs("qryDistinctBox").OpenRecordset(dbOpenSnapshot, dbReadOnly)
            If Not (.BOF And .EOF) Then .MoveFirst
            While Not .EOF
                intCount = intCount + Abs(Nz(DCount("1", "mytbl", "product='" & v & "' and box = '" & !box & "'"), 0) > 0)
                .MoveNext
            Wend
            bolReturn = (intCount >= .RecordCount)
        End With
    Else
        For Each var In boxes
            intCount = intCount + Abs(Nz(DCount("1", "mytbl", "product='" & v & "' and box = '" & var & "'"), 0) > 0)
        Next
        bolReturn = (intCount >= UBound(boxes))
    End If
    fncInAllBox = bolReturn
End Function

or if you don't want to change the function, just Add criteria to qryDistinctBox query.

Criteria: In ("box1", "box2", box3")

amir0914 · Oct 7, 2019

Thank you again arnelgp, can you do it by list box? it means I select multiple boxes from listbox in form and query work with selected boxes in listbox.

arnelgp · Oct 7, 2019

here I made a form.

amir0914 · Oct 8, 2019

Hi, Thank you arnelgp, sorry for delay in replying, I checked it but list box query doesn't work right, as shown in the images isn't product "c" in box 2 or there is not product h , k in box1. can you correct it?

Thanks in advanced.

arnelgp · Oct 8, 2019

only slight modification.

theDBguy · Oct 8, 2019

arnelgp said:
only slight modification.

Hi Arnel. Kudos for sticking with it. Cheers!

amir0914 · Oct 9, 2019

arnelgp said:
only slight modification.

Hi arnelgp, thank you so much for spend your time for me.

Select common product in one table (1 Viewer)

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

I’m here to help

Registered User.

Banishment Pending

I’m here to help

Registered User.

I’m here to help

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

I’m here to help

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Attachments

I’m here to help

Registered User.

Similar threads

Users who are viewing this thread