4 Replies Latest reply on Apr 8, 2013 1:37 PM by fabiuz

    How to find and sort records using two tables

    fabiuz

      Title

      How to find and sort records using two tables

      Post

           Hi,

           I have a little problem with two tables:

           1. costumers

           2. visits

           the tables are related by using the costumer id field, each costumer may have 1 or more visit .

           Visits contains the days of the month when I can and when I usually visit a costumer.

           It is not a date, but a day or more then one day in the week, in a month cicle.

           So for example a costumer could be visited on the turns n.1,2,3 and 7,8,9 each month.

           For each turn, there is a costumer sequence to follow for the visit. So, in the Visits table I have a field called sequence where I have set a number like 10, 20... 120 ecc. for each turn.

           So each costumer for each possible turn have a sequence for such turn.

           So for example, the costumer "Adam" could be visited on the turn n.3 in the sequence n.40, on the turn n.8 in the sequence n.120 .

           What I need now is to get all costumers that can be visited on a specific day turn, for example the turn n.3. Then I want to show these costumers orderd by their sequence in such turn.

           I am able to find costumers of the turn 3, but I still don't understand how I can order them taking care of their sequence number for that day.

           Sort function will always see the first available turn in visits table.

           Is anybody able to give me a litlle suggestion?

            

           Thanks a lot!

           Fabio

        • 1. Re: How to find and sort records using two tables
          philmodjunk
               

                    So for example, the costumer "Adam" could be visited on the turn n.3 in the sequence n.40, on the turn n.8 in the sequence n.120

               I have carefully read you post but have no clue how that works.

               Care to try that explanation again? Keep in mind that we have no idea what business you run nor how you run it other than you make periodic visits to your customers.

          • 2. Re: How to find and sort records using two tables
            fabiuz

                 sorry,

                 I'll try explain better: say I have a list of costumers and I have to visit them a couple of times per month.

                 If I consider a "month" like a sequence of 4 weeks, so a month for me is made of 28 days 7x4 .

                 Further more I don't visit costumers on saturdays and sundays.

                 So I have:

                 day 1 - Mon

                 day 2 - Tue

                 day 3 - Wed

                 day 4 - Thu

                 day 5 - Fri

                  

                 day 6 - Mon

                 day 7 - Tue

                 day 8 - Wed

                 day 9 - Thu

                 day 10 - Fri

                       

                      day 11 - Mon

                      day 12 - Tue

                      day 13 - Wed

                      day 14 - Thu

                      day 15 - Fri

                       

                      day 16 - Mon

                      day 17 - Tue

                      day 18 - Wed

                      day 19 - Thu

                      day 20 - Fri

                       
                      These are the possible values for the field "Turn" on the table "Visits".
                      Each costumer can be visited  on a couple of days per month only, or there could be some days that I could visit him if required to me.
                       
                      So for each costumer I have added a list of possibile days in the "Visits" table.
                      For each visit day, I have set a sequence number also.
                      So if I have ten costumers such day, I could have them ordered by using the sequence field.
                       
                      I need to use data in tables to:
                       
                      1. find costumers that I have to visit in one of my days, for example day 19 - Thu
                      2. order results by taking care of the sequence value of each costumer for that day.
                       
                      I am unable to get the point n. 2 or to find an easier way to solve the problem :)

                  

            • 3. Re: How to find and sort records using two tables
              philmodjunk
                   
                        These are the possible values for the field "Turn" on the table "Visits".
                   So turn stores a number from 1 to 20?
                    
                        
                             Each costumer can be visited  on a couple of days per month only, or there could be some days that I could visit him if required to me.
                   
                        So for each costumer I have added a list of possibile days in the "Visits" table.
                    
                   So exactly how and where are you storing this "list"? In what format are you storing that list information?
                    
                        
                             For each visit day, I have set a sequence number also. So if I have ten costumers such day, I could have them ordered by using the sequence field.
                   
                         
                        This means that if you have more than one customer scheduled for the same day, the customer visit record with Sequence = 1 is visited first and the visit record for that day with sequence = 2 is visted second?
                         
                        If I have that correct, then It sounds like you need to perform a find on the visits layout for the specified day (your number in turn makes it harder to find by a specific date) and then you would sort the visit records by Sequence to list them in the order that you will visit them. Fields from the customers table may be included on this layout to show customer name, address, etc.
                         

                    

              • 4. Re: How to find and sort records using two tables
                fabiuz

                     Thanks! I think I have got it now!

                      

                     yes, "turn" field stores a number, the values are taken from an external table not related: it used to get readable values only by using a popup menu.

                     In a costumers layout,  I have added a portal to display records from the Visits table for each costumer, and I am using it to insert new values too.

                     Correct: costumer with sequence 1 for that day is the firsth I will visit that day. other will follow based on their sequence number.

                     Can you think an easier way to get a similar result?

                     I mean, I need to plan visits to costumers for a month, and it will repeat in the same way each month. I don't want to plan day by day, I need a repating sequence that I can vary when needed.

                      

                     Thanks a lot, I will try now with the Visit layout!