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.
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.
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?
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.
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.