AnsweredAssumed Answered

How to find and sort records using two tables

Question asked by fabiuz on Apr 8, 2013
Latest reply on Apr 8, 2013 by fabiuz


How to find and sort records using two tables



     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!