5 Replies Latest reply on Dec 3, 2012 4:15 PM by philmodjunk

    Best way to show data from previous month

    j_morris

      Title

      Best way to show data from previous month

      Post

           I'm working on a DB that holds statistical information entered on a monthly basis. One requirement they have is to be able to view the entries from the previous month on the screen where they enter data for the current month.

           Since there are multiple locations it wouldn't be as easy as just going back one record. I'd have to have the script find the correct past record based on location and date.

           Would I need to create new fields for the "previous entries"?

        • 1. Re: Best way to show data from previous month
          philmodjunk

               Sounds like you need a relationship that links to the record or records from the previous month. The exact details of that relationship depend on the data you are recording. Do you record one record for each month or multiple records for each month?

               Is there a date field that records the date for that record?

               If so, a self join relationship using some calculation fields can match to the record or records from the previous month and display them. If there is more than one record for each month, such records can be listed in a portal.

          • 2. Re: Best way to show data from previous month
            j_morris

                 There are multiple locations (12). There is a record for each location per month so in a given month they'd be 12 records.

                 Each record has a month and year associated with it.

            • 3. Re: Best way to show data from previous month
              philmodjunk

                   Is month recorded as the name of the month or the number?

                   Do you need to see all 12 records of the previous month or just the one for the current record's location?

              • 4. Re: Best way to show data from previous month
                j_morris

                     I actually have the time period accessible in a few different ways:

                       
                •           month number
                •      
                •           month name
                •      
                •           year and month in format YYYYMM

                     I just want to be able to see the previous month's record for the current record's location.

                • 5. Re: Best way to show data from previous month
                  philmodjunk

                       Yes, but the best way to do that is with a date field as we can then use the date function to compute a reference to the previous month. I needed to know what data was avialable in your table in order to craft a pair of calcualtion fields to use in a relationship for this purpose.

                       Define a calculation field, cThisMonth and define it as: Date ( MonthNumber ; 1 ; Year ). Since you don't mention a separate year field, you may need to do it like this: Date ( MonthNumber ; 1 ; Left ( YearMonth ; 4 ) )

                       Then define a second calculation field, cPrevMonth as:

                       Date ( MonthNumber - 1 ; 1 ; year )

                       When the month is January, this calculation for previous month will automatically adjust to refer to December of the previous year.

                       Now you can add a new occurrence of your table, MonthlyDataPrevMonth to produce this self join relationship:

                       MonthlyData::Location = MonthlyDataPrevMonth::Location AND
                       MonthlyData::cPrevMonth = MonthlyDataPrevMonth::cThisMonth

                       And now you can use fields from MonthlyDataPrevMonth on a layout that refers to MonthlyData to show data from the previous month from the same location.