Finding matching data 2

[georgesOne]

Hi,

I have a normalized table of formulations with the structure:

Formulation Compound
F1 AA
F1 BB
F2 CC
F3 AA
F3 CC
F3 DD
F4 BB
F4 AA

Now I want to find all records which contain at least one of the compounds in - as an example - formulation F1... (i.e. the match can contain AA or BB or (AA and BB) -> Result: F3, F4 containing AA) and those which contain exactly and only the same compounds (i.e. the match must contain AA and BB -> F4 containing AA and BB).

The search criteria are a result of a "Query34"

SELECT DISTINCT tblFDataNorm.Formulation, tblFDataNorm.Compound
FROM tblFDataNorm
WHERE (((tblFDataNorm.Formulation)=bss_fFindVar(1)) which results in
Formulation Compound
F1 AA
F1 BB

where bss_fFindVar(1) gets the current Formulation value from a form textbox.

I have no clear clue, especially how to get the latter. The first can be optained through a join query between "tblfDataNorm" and "Query34" on compound, I believe.

Any guidance is appreciated.

Kind regards, georges

 

[georgesOne]

....
Should be added that the matches of course also include F1...
regards, georges

 

[PHV]

AA and BB:

SELECT Formulation
FROM tblFDataNorm
WHERE Compound IN ('AA','BB')
GROUP BY Formulation
HAVING COUNT(*)=2

AA or BB:

SELECT Formulation
FROM tblFDataNorm
WHERE Compound IN ('AA','BB')
GROUP BY Formulation
HAVING COUNT(*)>0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Hi PH,

I have tried your suggestions with a 50% success: The AA OR BB sql works, the AA AND BB sql works not. I am still struggling to understand the Count(*) effects, so I do not know, what could be wrong... any idea?

In addition, the sql works when 'AA', 'BB' are hard coded, but when I use the following function

Function bss_fText() As String

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Query34", dbOpenDynaset)

bss_fText = "'"
rst.MoveFirst
Do While Not rst.EOF
bss_fText = bss_fText & rst!CompoundID & "', '"
rst.MoveNext
Loop
bss_fText = Left(bss_fText, Len(bss_fText) - 3)

End Function

which results in the string 'AA','BB'
in the following sql code

SELECT Formulation FROM tblFDataNorm WHERE Compound IN (bss_ftext()) GROUP BY Formulation HAVING COUNT(*)>0

no records result. I can use the function that way, right?

Sorry for bothering you. I am 97% done and kind of tired.

Kind regards, georges

 

[dhookom]

This will never work if the bss_ftext() returns more than one value:

SELECT Formulation FROM tblFDataNorm WHERE Compound IN (bss_ftext()) GROUP BY Formulation HAVING COUNT(*)>0

You could change the SQL property of a saved query.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Thanks, Duane, you are right. Could I eventually use EXISTS?
I actually do not understand the meaning of "You could change the SQL property of a saved query."

However, I still do not get the 'Having Count(*)=2' from PH. If I am looking for three compounds should the query then be like

SELECT Formulation FROM tblFDataNorm WHERE Compound IN ('AA','BB','CC') GROUP BY Formulation HAVING COUNT(*)=3 ?

Regards, georges

 

[dhookom]

Regarding the change the SQL property of a saved query, I use code like:

Public Function ChangeSQL(strQueryName as String, _
    strSQL as String) as String
  Dim db as DAO.Database
  Dim qd as DAO.QueryDef
  Set db = Currentdb
  Set qd = db.QueryDefs(strQueryName)
  ChangeSQL = qd.SQL  'return current SQL statement
  qd.SQL = strSQL     'set new SQL
  set qd = Nothing    'clean up
  set db = Nothing    'clean up
End Function

You can then write your code to update the SQL statement of a saved query.

  Dim strSQL as String
  Dim strPrevSQL as String
  Dim strQueryName as String
  strQueryName = "Your Query Name Here"
  strSQL = "SELECT Formulation FROM tblFDataNorm " & _
    "WHERE Compound IN (" & bss_ftext() & ") " & _
    "GROUP BY Formulation " & _
    "HAVING COUNT(*)>0" 'may need to update this number
  strPrevSQL = ChangeSQL(strQueryName, strSQL)
  'now user your query as needed

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

AND

SELECT F.Formulation
FROM tblFDataNorm F INNER JOIN Query34 Q ON F.Compound=Q.CompoundID
GROUP BY F.Formulation
HAVING COUNT(*)=(SELECT COUNT(*) FROM Query34)

OR

SELECT F.Formulation
FROM tblFDataNorm F INNER JOIN Query34 Q ON F.Compound=Q.CompoundID
GROUP BY F.Formulation

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Thanks a lot...
georges