Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    How to work with condition in many to many

    Friends, as I have attached a table company, a table technology and a junction table to create
    a many to many.

    I'm trying to create a form with combobox cascade. I have two buttons, one to search for another company and to check for technology.

    First I put looking for technology as the source of the line:

    SELECT TblTechnology.NameOfTechnology, TblLINKCompany_Technology.TechnologyID
    FROM TblTechnology
    INNER JOIN TblLINKCompany_Technology ON TblTechnology.TechnologyID = TblLINKCompany_Technology.TechnologyID;

    When I open the combo technology, the values ​​are duplicates, because as it is a many to many, the code technology join table is repeated. What is the condition that I put to show only a single name of technology?

    Thank you.
    Attached Images Attached Images

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    The technology query, to be used in the first combo, to allow you to select a technology, should reference the technology table alone.

    The query to fill in the company combo, if you want a company that supports the chosen technology, can the the one you just posted, with an added WHERE clause on Technology, so that its value is the value for the technology chosen in the technology combo box.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-05-03)

  4. #3
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    ruirib, thanks for answering. I did as you told me and it worked.

    At the origin of the line I put combobox technology:

    SELECT TblTecnologiaInov.CodTecnologia, TblTecnologiaInov.Tecnologia FROM TblTecnologiaInov ORDER BY TblTecnologiaInov.Tecnologia;


    And in the event of the combo update after I put:

    Me.combEmpresa.RowSource = "SELECT TblLINKEmpresa_Tecnologia.CodTecnologia, TblEmpresa.NomeDaEmpresa, TblLINKEmpresa_Tecnologia.CodEmpresa FROM TblTecnologiaInov INNER JOIN (TblEmpresa INNER JOIN TblLINKEmpresa_Tecnologia ON TblEmpresa.CodEmpresa = TblLINKEmpresa_Tecnologia.CodEmpresa)ON TblTecnologiaInov.CodTecnologia = TblLINKEmpresa_Tecnologia.CodTecnologia WHERE TblLINKEmpresa_Tecnologia.CodTecnologia=" & Forms!FFiltros!combTecnologia & ";"

    It worked perfectly.

    Now the second option, searching for company. In the row source for the combobox Company2, I put:


    SELECT TblEmpresa.CodEmpresa, TblEmpresa.NomeDaEmpresa FROM TblEmpresa ORDER BY TblEmpresa.NomeDaEmpresa;

    It worked.

    And in the event of the combo update after I put Company2:

    Me.combTecnologia2.RowSource = "SELECT TblLINKEmpresa_Tecnologia.CodEmpresa, TblTecnologia.Tecnologia, TblLINKEmpresa_Tecnologia.CodTecnologia FROM TblEmpresa INNER JOIN (TblTecnologiaInov INNER JOIN TblLINKEmpresa_Tecnologia ON TblTecnologia.CodTecnologia = TblLINKEmpresa_Tecnologia.CodTecnologia) ON TblEmpresa.CodEmpresa = TblLINKEmpresa_Tecnologia.CodEmpresa WHERE TblLINKEmpresa_Tecnologia.CodEmpresa=" & Forms!FFiltros!combEmpresa2 & ";"

    I just switched the names of the fields of sql first, but it did not work.

    Note that these are names of tables and fields from my bank official. The idea is the same one I sent earlier.
    Just had created a database with the names of fields and tables in English to facilitate understanding.

    Thanks for helping.
    Attached Images Attached Images

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    Fabio, I am not sure I understand what you are trying to do. Can you post a picture of the form you want to obtain?

  6. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-05-03)

  7. #5
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    ok, I'll try to explain through images:

    first picture - I have two buttons:

    Buscar Tecnologia (Search Technology)

    Buscar Empresa (Search Company)

    I'll choose Technology.

    second picture - technologies (tests) and will choose the novoteste.

    third picture - after choosing the option novoteste, is shown in combobox company, companies related to technology
    chosen. (FABIOTESTE, for example)

    fourth picture - now I clicked the button to search for company, companies are shown correctly, I choose the
    FABIOTESTE, but the combo technology is empty, and theoretically should have shown the novoteste technology, as before.

    Thank you.
    Attached Files Attached Files

  8. #6
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    For some reason, after a long time, (after update combobox company, and shows the combobox fourth-tech image) shows an error message in the join expression. I do not understand why, because the first option works perfectly, and the second option I just changed the names of the fields in the event of combobox after updating.

  9. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    To solve the query issue, without having the database here, I suggest that you do as follows:

    Build a query using the query design dialog, joining the appropriate tables and adding a temporary WHERE condition, for example, CodEmpresa=1. You can then Choose SQL View from the View icon at the top left and copy the SQL to the form. You will then just need to replace the value in the WHERE condition: instead of 1, just use Me.ComEmpresa2.

    The SQL view in the Query Design dialog is a nice way to build and learn SQL to use elsewhere .

  10. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-05-04)

  11. #8
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Thanks for the tip, worked exactly as you told me. Only one little problem, after choosing one of the options
    combobox of technology, for example, I choose the option "novoteste" and after upgrading the rest of the form
    works perfectly, as the option chosen, but the combo shows another name "testeinovação."
    What do you think might be?
    Thank you.
    Attached Files Attached Files

  12. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    Where does that value come from? Do you have it in any of your tables?

  13. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-05-04)

  14. #10
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    It's all right, only when the combo is updated, instead of being (show in the combo) the selected value is displayed whenever a single value. For example, if I choose the value "novoteste" a third combo is populated correctly, in accordance with the value "novoteste." But, showing the combo technology is always the value "testeinovação." I compared all the properties of a combo that works properly, but found no difference.
    Thank you.

  15. #11
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    It's hard to say something, Fabio, without knowing your database and code. Maybe if you attach the database, I can have a look.

  16. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-05-05)

  17. #12
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    ruirib, ok, I am sending the db.

    Please try this sequence:

    search technology > technology = test1 > Active Products > product = mainfraim
    technology in the combo correctly shows the name of technology "test1".

    another sequence:

    search technology > technology = test2 > Active Products > product = cloud
    technology in the combo correctly shows the name of technology "test2".

    third sequence:

    search Company > company = ibm > technology = test1 > Active Products > product = mainfraim
    technology in the combo correctly shows the name of technology "test1".

    fourth sequence:

    search Company > company = ibm > technology = test2 > Active Products > product = cloud

    * The filter works correctly, but the combo technology shows the name of the wrong technology "test1".

    Repeated attempts, I could not identify why this happens.

    Thanks for helping.
    Attached Files Attached Files

  18. #13
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    Fabio,

    I am not sure I understand what you are trying to do, but ok. I think the problem was the datasource for Combotechnology2, that you were setting in the CombCompany_afterUpdate event. For a Technology combo, the first column of the datasource was the company name? I changed that. The datasource has just two columns now, TechnologyID and NameOfTechnology.

    If I got it right, it works now.
    Attached Files Attached Files

  19. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-05-05)

  20. #14
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Thank you for the bank. Actually the first column was the code of the company. I thought it was necessary
    to fill the combo technology correctly, but I forgot who first had to put the id of the technology, then the name of technology
    and then the company id. Anyway, the problem was solved.



    How do I use a translator, sometimes the translation is not perfect. The problem was that when the combo tecnologia2 was updated,
    the filter worked perfectly, but it showed the name of any other technology in the combo.

    I see, I'll have problems with the relationship because the product must be in accordance with the company too, and not a relationship of a
    for many with technology.
    Well, I'll draw up the matter well in order to make a new post.

    Again, thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •