7 Replies Latest reply on Dec 11, 2013 3:25 PM by bartprins

    Help with loops and relations ?

    bartprins

      Hi again,

       

      I've got another problem with loops and probably relations, still not getting it as newbie

      I made an example db to make it more visual.

       

      I've got a couple of tables Client..Products...ProductDescription

      The tables Client and Product will be imported from a textfile which is get from a third party. The Products however only have a number in the table, i have a table made which is called Productdescription, where i want to lookup the value of Product for example the value is 3. In the descriptiontable the value of 3 should be referenced ( i hope this a correct word ) as "Apples" if the productvalue is 2 then it should say "Grapes"

       

      I have to this for a couple of thousand products and descriptions so would like make a loop where i checks the value of Product and matches this with the Productdescription and do this for all Clientrecords.

       

      I would be much oblidged if someone could point me in the right direction!!

       

      Thanks !

       

      Bart Prins

        • 1. Re: Help with loops and relations ?
          gcatnine

          I do not understand what you what to achieve.

          I had a look at your file and I do not understand why the ClientID and to be related to the ProductID

          Normally, a client has more products so the relation is ClientID with fkClientID in the product table (foreign key). So, in the product table you store in all records that are related to that particular client the value of the ClientID.

          The ProductID identifies each products. To classify the products you need another field i.e. fkProductDescriptionLookupId in which store the value of the product category (1 for banana, 2 for grapes, etc.).

          look at the attached file

          • 2. Re: Help with loops and relations ?
            bartprins

            Hi Gianandrea,

             

            Thank you very much for looking into to this, my problem is that when i change the productfield in your example to a value of 2 i should say "Grapes" as value for Productdescription, but it doesn't do this when i change the value. So the Productfield and the ProductDescriptionfield should match each time a change value in productfield.

             

            I hope you understand what i mean, as for the relations thanks !

             

            Much oblidged

             

             

            Bart Prins

            • 3. Re: Help with loops and relations ?
              jlamprecht

              Hi Bart

               

              Maybe I am misunderstanding here, but why would the Product ID and the Product Description be in two tables? Can a product have multiple Descriptions?

               

              -JohnAustin

              • 4. Re: Help with loops and relations ?
                bartprins

                Hi John Austin,

                 

                No sorry your right product en productdescription can be in the same table!

                • 5. Re: Help with loops and relations ?
                  jlamprecht

                  Hi Bart,

                   

                  Ok I see now. What I would do concerning your relationships right now is set up a Join table in between Client and your Product table with Description.

                   

                  The Join table would act as a new entity, possibly called ClientProduct. This represents a product that belongs to a client. You require this as a single client can have multiple products and a single product can be held by multiple clients.

                   

                  Relate the ClientID of the Client table as a foreign key in the ClientProduct table. Relate the ProductID of the Product table as a foreign key in the ClientProduct table.

                   

                  This will give you a table of clients and their products.

                   

                  -JohnAustin

                   

                  -Added modified version of your file here for reference

                  1 of 1 people found this helpful
                  • 6. Re: Help with loops and relations ?
                    gcatnine

                    in my wxample you have to go to ProducTable layout and change the fkProductDescriptionLookupId field to the value you wnat (i.e. 2) and the description change

                    • 7. Re: Help with loops and relations ?
                      bartprins

                      Hi Gianandrea and John,

                       

                      thank you for the help !!

                       

                      It makes much more sense i'm such a newbie hahaha