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.

 

[Pat Hartman]

There is nothing the Multi-Value field offers that you can't do in more traditional ways. When you use multi-value fields, you need to use specialized SQL syntax because Multi-Value fields are not supported by standard SQL syntax. I don't use them, ever so I am not familiar with the syntax but you should be able to locate it in help or Google. That also makes them not upsizable although that will probably not be a problem for you unless you expect to convert the BE to SQL Server or some other RDBMS. I've attached a sample m-m database so you can see how to implement m-m correctly. The MV field works only for a very restricted subset of possibilities. As soon as you need intersection data, you will need to abandon the MV field anyway so you might want to consider it now.

 

[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.

 

[Pat Hartman]

Sorry, I can't help you with Multi-Value fields. I do not use them. the price for the "cool" control is too high. I prefer to do things the old fashioned way.

1. I don't see a multi-value field in any table.
2. I do see 2 table level lookups. This is another "feature" with too many problems to overcome so I also do not use it.
3. Behind the scenes, the MV relationship is implemented using a junction table. Consequently, the query will return the same record for each match in the junction table. You might be able to resolve this if you look up the correct SQL syntax. You might be able to get back one row with multiple values separated by commas but I can't say for certain.

To "fix" the problem in the current state, requires too much 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