Table structure

[Lyncroft]

Some general advice needed here.

I've a list of laboratories who deal with Analytes(eg, ore, zinc, iron). There could be 200 plus of these. The labs also deal with Matrix(eg, fish, herbs, shellfish).

The way I set it up was:

tblLabs
LabID(PK)
LabName

tblAnalytes
AnalyteID (PK LookUp)
LabID(PK)

tblMatrix
MatrixID(PK LookUp)
LabID(PK)

This works fine and I managed to build a search function using 2 listboxes (1 for analyte, 1 for matrix) to produce a list of labs who can do Ore or Zinc and Fish or Herbs for example.

However, the user wants to produce a list of labs depending upon any combination of chosen analyte and matrix. For example, instead of a combination of Ore or Zinc and Fish or Herbs he would want Ore AND Zinc and Fish AND Herbs. To me the various combinations could be enormous and cant find a way to organise the tables.

Any ideas?

 

[namliam]

Your tables are fine... Your form is not however....

I asumme your table structure is not complete. You actually have 5 tables handling this. The 3 you mentioned above and 2 more...
To be able to do the PK Lookup thing...

If so then all you need do is change the form, change the combo box to listbox (multi select) and build the SQL from vba, posibly even allowing the user to choose between AND and OR for each increasing the flexibility....

I hope this give you a good idea of how to proceed...

Regards

The Mailman

 

[Lyncroft]

Cheers for that Mailman(Namliam).

You're right I've got 5 tables including the 2 look ups.

At the moment I've a main form with the Lab details and a separate subform each for Analytes and Matrix in tabular form using a combo box. This works well enough for inputting.

My problem is getting the info out which is why I was wondering if my tables were set up correctly. To get the info out I've a separate list box for Analyte and Matrix (multiselect) and the user clicks on the relevant item in each box to produce a list of labs that can deal with those items. Unfortunately if the user clicks on Ore and Zinc in Analyte list and Fish in Matrix it doesn't pick out labs that can do "Ore AND Zinc AND Fish", it picks out labs that can do "Ore OR Zinc AND Fish"

I've got totally confused looking at this so apolgies if I'm coming across thick (I probably am!).

 

[namliam]

how are you picking up the labs? Getting the info from the listboxes...

Seems to me to be just a simple case of replacing "OR" by "AND"

Regards

 

[Lyncroft]

Hi again!

I've two list boxes:

Analyte
Sulphur
Carbon
Sodium
Ore
Zinc
etc

Matrix
Fruit
Fish
Marrows
Shellfish

Say we want to pinpoint labs that can do a very specific combination of Sulphur, Carbon and Fruit I would select the relevant items from the listboxes and it loops thru and picks out the labs. In the database Ive inputted one lab that can do only Sulphur(Analyte) and one lab that can do Sulphur(Analyte), Carbon(Analyte) and Fruit(Matrix). I only want the latter to appear. As it stands they both appear because I've selected Sulphur in the Analyte list. I don't see how I can make this work unless I create a separate field for every analyte but you're talking alot of fields here!? Also, the various specific combinations of Analyte/Matrix must be enormous.

I could always leave it as it is and the user can query to a certain level and then just go into the lab details to take it further.

Am I missing something?

 

[namliam]

Say we want to pinpoint labs that can do a very specific combination of Sulphur, Carbon and Fruit I would select the relevant items from the listboxes and it loops thru and picks out the labs

Post the code that "loops thru and picks out the labs"

Regards

 

[Lyncroft]

Hello again. Thanks for being patient!

Here is the code. There is another list showing yes/no whether the lab works out of hrs (this isn't essential). I'm using qryAllData to give me a list of LabID, LabName, OutOfHrs, Analyte, Matrix. Off this qry I'm creating qryDataType to produce a list of LabID, LabName, OutofHrs, Analyte, Matrix from the selected items on the lists. I'm only actually showing LabID, LabName to avoid showing duplicate labs. The list of labs appears on frmAnalyteMatrixResults on which there is a button for the user to go in and look at the relevant labs details.

I guess I could show LabID, LabName plus OutOfHrs, Analyte, Matrix on frmAnalyteMatrixResults to give the user more info. I still can't see how I can just specifically pinpoint the labs to the items selected on the list without picking up labs that apply to just one item on a list for example.


Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sAnalyte As String
Dim sMatrix
Dim sAvailOutHrs As String
Dim sCriteria As String
Dim varItem As Variant

' build criteria string for selected analytes

For Each varItem In Me.lstAnalyte.ItemsSelected
sAnalyte = sAnalyte & ",'" & Me.lstAnalyte.ItemData(varItem) & "'"
Next varItem

If sAnalyte <> "" Then
sAnalyte = Mid(sAnalyte, 2) ' remove leading comma.
sAnalyte = " [Analyte] in (" & sAnalyte & ")"
sCriteria = "and " & sAnalyte
End If

' build criteria string for selected Matrix.

For Each varItem In Me.lstMatrix.ItemsSelected
sMatrix = sMatrix & ",'" & Me.lstMatrix.ItemData(varItem) & "'"
Next varItem

If sMatrix <> "" Then
sMatrix = Mid(sMatrix, 2) ' remove leading comma.
sMatrix = " [Matrix] in (" & sMatrix & ")"
sCriteria = sCriteria & " and " & sMatrix
End If


' build criteria string if available out of hrs.

For Each varItem In Me.lstOutHrs.ItemsSelected
sAvailOutHrs = sAvailOutHrs & ",'" & Me.lstOutHrs.ItemData(varItem) & "'"
Next varItem

If sAvailOutHrs <> "" Then
sAvailOutHrs = Mid(sAvailOutHrs, 2) ' remove leading comma.
sAvailOutHrs = " [AvailOutHrs1] in (" & sAvailOutHrs & ")"
sCriteria = sCriteria & " and " & sAvailOutHrs
End If

If sCriteria <> "" Then
sCriteria = Mid(sCriteria, 5)
Else
'no criteria selected
End If
SQL = "Select Distinct LabID, LabName " & _
" FROM qryAllData " & _
" WHERE " & sCriteria

Set db = CurrentDb

'delete query qryDataType if exists.
On Error Resume Next
db.QueryDefs.Delete "qryDataType"
On Error GoTo 0

' create and run query qryDataType.
Set qDef = db.CreateQueryDef("qryDataType", SQL)
'DoCmd.OpenQuery "qryDataType"
frmAnalyteMatrixResults.SourceObject = "frmAnalyteMatrixResults"
Me.frmAnalyteMatrixResults.Visible = True

 

[namliam]

Ok ... Your using the In() thing.... which is more or less the same as OR....

For Each varItem In Me.lstAnalyte.ItemsSelected
sAnalyte = sAnalyte & "OR [Analyte] = '" & Me.lstAnalyte.ItemData(varItem) & "' "
Next varItem

If sAnalyte <> "" Then
sAnalyte = Mid(sAnalyte, 3) ' remove leading OR.
sCriteria = "and " & sAnalyte
End If

Above should do the same as your In() thing... now changing the OR to AND

For Each varItem In Me.lstAnalyte.ItemsSelected
sAnalyte = sAnalyte & "AND [Analyte] = '" & Me.lstAnalyte.ItemData(varItem) & "' "
Next varItem

If sAnalyte <> "" Then
sAnalyte = Mid(sAnalyte, 3) ' remove leading OR.
sCriteria = "and " & sAnalyte
End If

Should act as you want it. You might want to add an option for the user to choose between OR and AND making it more flexible yet....

Regards

The "ever patient" Mailman

 

[Lyncroft]

Thanks for all your help on this one Mailman. Think I’m going to have to admit defeat. Spent hours on it. I made your changes and I can see what’s happening.

My qryAllData shows

LabID LabName OutHrs Analyte Matrix
45 Foxys Yes Carbon Apples
45 Foxys Yes Calcium Apples
50 Murphys Yes Calcium Apples
51 Browns Yes Calcium Pears

Your code to draw out a combination of Carbon and Calcium and Apples shows:

AnalyteField MatrixField
'Carbon’ AND ‘Calcium' ‘Apples’


What I’d like to see is just the lab Foxys to appear. Currently I don’t get anything – perhaps because the different analytes are in different cells??????

Blimey I need a drink!

Cheers again.

 

[namliam]

What we have here is a bad design....

If what you are showing is actually in 1 table .... bad design indeed..

You then have a many to many relationship inside one table...

Search the forum on the "Many to many" design. Read up on that...
If at all possible i will advice you to chuck this design and start all over. If not what you want is still possible, but a lot harder than it should be...

I may have a think about it if/when i hear back from you....

Regards

 

[Lyncroft]

Thanks for all your help Mailman. I've learn quite a bit here. I'll sit down and have a good think about it.

Thanks once again.