Filter a database that contain a multivalued field

[LeslyP]

Hello everyone,

I've got a big problem, so I'll try to be crystal clear.

My Database
My database contain informations about soil. The Soil_T have different fields : ID_Soil (Auto#), Type (linked to another table), description (long text)... and Treatment (Multivalued field or many-to-many).
This tabe is linked to Analyse_T wich contain :ID_Analyse (Auto#), ID_Soil (number), Masse (number)...

1drv.ms/x/s!AqNow7kzXdyKhEM-p5ptOdSDrIC2

What I want to do

In antoher form, I have many field that allows me to filter the table Soil_T and Analyse_T. Basicly, this code -> allenbrowne.com/ser-62code.html ; allenbrowne.com/ser-62.html
So I am filtering the form with a filter with fields in the header.

It works number one when Treatement is a multivalued field except that I can't use the field Treatement to filter.

It seem to works when I am doing a many-to-many Relationship. When I am filteringwith the field treament, it works. But when I am filtering with another field, if a record has two Treaments, it's going to appear twice ! Not what I want.

Waht I want

I want to be able to filter with all the fields and I want every record to show only once.

The filter field (in the header) can be a combobox or a multivalued field. Multivalued field would be nice, but it may be to complicated.

Thanks a lot. I hope I am clear enough

PS: I looked a loooooooot on the internet and on the forum. I didn't find an answer :/

LeslyP

 

[jdraw]

Most will tell you to avoid multivalued fields.
Tell us about the application and database. Plain English.
What stage of development are you at?

 

[LeslyP]

My data base is done (well, draft version) except for the Treatment field in the Search form.
In the header of the seach form, I can search with "Description", "Type",... it's going to work. In the result, the record is going to appear once ONLY IF the Treatment values are in ONE FIELD. Ontherwise, my record is going to appear twice.
PS: the result zone is a continous form.

I know multivalued field are not great, but I need my many-to-many relationship to show result in one field (one row in a query for exemple).

Need other informations ?

 

[jdraw]

Can you post a copy of your database? You can post an attachment to the forum if you have less than 10 posts if you use ZIP format.

A picture of your table/relationships would be helpful.

Do you have a clear description of the requirements of this database? That would be helpful to anyone offering assistance.

Good luck.

 

[LeslyP]

Ok !
Pat Hartman:
Thank you for your exemple. However, I know how to do many-to-many Relationships. The problem is filtering. Records with many Treatment appear multiple times.

Jdraw:
This data base is use to store and find analyses of soil. I did a clean and English version of my data base. Ok !

 

[LeslyP]

I got the version with the Treatment in the header as a filter too. But it doesn't work.

 

[LeslyP]

Pat Harman
Like I said in my original post, it's a MV OR a many-to-many. You said MV were to much trouble, so I change it for a many-to-many Relationship.
For the 2 table level lookups, it seems fine for the moment.

 

[jdraw]

But it doesn't work.

What exactly does this mean? Error message?
Can you give instructions on how to test your form?

I would not use lookups at table field level. Would recommend using lookup table.

 

[LeslyP]

jdraw
Ok I was trying Something. When I do the Search with Treatment in a combobox, Nothing happen. But when I do the search with a textbox, there are results. (But I don't want to use the text box...)
However, there a still duplicates of the same record if it have more than one treatment.

Search with combobox

 If Not IsNull(Me.cbo_treatment) Then
    strWhere = strWhere & "([Treatment] like ""*" & Me.cbo_treatment & "*"") AND "
End If

Search with textbox

If Not IsNull(Me.txt_treatment) Then
    strWhere = strWhere & "([Treatment] like ""*" & Me.txt_treatment & "*"") AND "
End If

 

[isladogs]

Is Treatment the bound field in your combobox?
If not, you need to use the column number.
This is zero based so the second column is Me.cbo_treatment.Column(1) etc

 

[LeslyP]

Is Treatment the bound field in your combobox?
If not, you need to use the column number.
This is zero based so the second column is Me.cbo_treatment.Column(1) etc

Hummmm, I see. There is the Soil_T wich contain ID_Soil, the Treatment_T wich contain ID_traitement and Treament, and Treament_link_T wich contain ID_Soil and ID_Treatment.
I am going to try this tomorrow morning ! This might solve at least one problem. Thank you!

Still, I need to figure out how to fit the Treatment value in one field.

 

[LeslyP]

I would not use lookups at table field level. Would recommend using lookup table.

Excuse me, what do you mean ?

 

[LeslyP]

I just find a code that would allow me to stock all the Treatment in one row. I'll to try this tomorrow and see if it's works.

Option Compare Database
Option Explicit
Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = "," ) As String
'Downloaded from theDBguy's site
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With
'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)
Set rs = Nothing
Set db = Nothing
End Function

Query

SELECT DISTINCT tblMyData.Order, SimpleCSV("SELECT [Stage] & ' ' & [Person] & ' ' & [StageDate] FROM tblMyData WHERE [Stage] is not null And [Order] = " & [Order]," " ) AS StagePersonDateSeries
FROM tblMyData;

https://forum.hardware.fr/hfr/Progr...rouper-plusieurs-resultats-sujet_121325_1.htm

 

[LeslyP]

Is Treatment the bound field in your combobox?
If not, you need to use the column number.
This is zero based so the second column is Me.cbo_treatment.Column(1) etc

Ridders Thank you ! It works One problem solved

 

[isladogs]

You're welcome. Can you explain your other problem again

 

[LeslyP]

Ridders

I am actualy trying something, but here's the other problem:

When I am filtering with other field (example: Description), the records with two Treatments to appear twice (like in a Query). I would like them to appear only once with the Treatment(s) in one textbox.

I am trying to Concatenate the field Treatment with this code :
http://www.accessmvp.com/thedbguy/codes.asp?title=simplecsv

I don't understand where to put the SQL statement, so I contacted the autor.

 

[isladogs]

Ridders

I am actualy trying something, but here's the other problem:

When I am filtering with other field (example: Description), the records with two Treatments to appear twice (like in a Query). I would like them to appear only once with the Treatment(s) in one textbox.

I am trying to Concatenate the field Treatment with this code :
http://www.accessmvp.com/thedbguy/codes.asp?title=simplecsv

I don't understand where to put the SQL statement, so I contacted the autor.

The DBGuy is usually very prompt at responding so I'll leave him to answer.
However, there are plenty of similar examples of concatenation both here and on other sites. For example http://allenbrowne.com/func-concat.html

 

[LeslyP]

Whou ! Now everything works just fine ! It was not very complicated, I don't understand why there is not a clear article about that somewhere.

I am going to post my database later with full instructions.

Thanks again everyone

 

[The_Doc_Man]

jdraw suggested not using a table with a lookup field, but rather doing a more "traditional" lookup. (My words, not his.) You asked what it means.

I don't know how familiar you are with database normalization, but the idea of normalizing is to reduce extra copies of stuff in tables. So to do a lookup from a list of known, approved values, you do not have a lookup field.

Instead, on forms you build a combo box do to a lookup of the key value from the table that contains the translation of that key value into text. So on a bound form, the key gets stored but the combo box can be "persuaded" to show you the translation thereof. There is a combo-box wizard that would build one of these for you on the form.

The other way this might work is to build a formal relationship between the field in the main table that would hold the lookup key and the key field (which must be the prime key) in the table that contains the translations. Then you can EASILY build a query that JOINS the main table with the translation table so that you can see both the key and its meaning in the query. This is what I commonly do for reports where the translation might be long but the key is just an INTEGER or LONG INTEGER.

The difference between a lookup field and a lookup table linked via a relationship is that with the lookup table, if you don't need the extra baggage of the lookup in a particular process, you don't need to carry along the translations. Then you have no extra implied records. That is where you can pick up extra records you weren't expecting.

For instance, let's say you have a lookup field called StatusCode. The code itself is really just a number from 1 to 8, let's say. If you tried to recover records with a specific value for some other field and tried to do a SELECT DISTINCT that recovered the status but didn't have a WHERE sub-clause for a specific status code, you might see as many as 8 records, one for each code, because the right code wasn't specified. This is why many of us avoid lookup fields.

 

[LeslyP]

Oh thank you The Doc Man !! I didn't know Access was doing this in my back
I though it was the same as creating myself the relationship. I will change all those lookup things.

So, Access is creating functionalities to help us.... but in the end, it's just crap. Like Multivalued fields xD