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.

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.

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: