1 Reply Latest reply on Sep 14, 2012 3:10 PM by philmodjunk

    Can I use the same Match Field to create relationships between more than two tables

    JonathanHouston

      Title

      Can I use the same Match Field to create relationships between more than two tables

      Post

           Dear all

            

           Does anyone know if I can use the same match field in more than two tables to relate the information between them.

            

           I have a match field in each table called 'Month ID' which is a calculation field that displaces it's contents as Month Year (e.g. September 2012).

            

           I have multiple tables such as Invoices, Expences, Credit Notes etc and within these tables each record contains a Month ID Field in order to tag it to a particular Month. The idea is that I have central table which has a record per month and that the monthly figures from the other tables (i.e. Invoices, Expences etc) feed into this main table by using the Month ID tag field.

            

           I can get this to work perfectly by creating a relationship between Month ID in the Invoices Table to Month ID in the Main Table but once this first relationship is created none of the further relationships seem to work. This seems to be the case when I try to use the same match field but this doesn't seem to make sense.

            

           Can anyone help me with this?

            

           Many thanks

            

           Jonathan

            

            

            

        • 1. Re: Can I use the same Match Field to create relationships between more than two tables
          philmodjunk

               Yes, you can use the same match field to link to other tables so this would not be why it's not working for you.

               You may want to use the controls below post A Answer to upload a screen shot of Manage | Database | relationships and then also describe exactly how the relationship is not working for you.

               You might also want to use an actual date field set to the first day of a given month. Such a field will naturally sort in the correct chronological order so it can be a better option. Data formatting can be specified for such a field so that it displays only the month name and year so that it can look exactly like your current MonthID field.

               And this calculation can take any date in "datefield" and compute the date for the first day of the month if you select "date" as the return type.

               DateField - day ( DateField ) + 1