Year by Year comparison

[jjesko]

I have a simple database that keeps track of our product movement. At the end of each month I manually add these numbers by hand. My fields are <<Date>>, <<Sales>>, <<Margin>>, <<Inventory_Balance>>. I now have over a years worth of data.

I want to show on a columnar layout this years data and right next to it last years data for a report. How do I get 2001's data to show up next to 2002's data. I of course can print them in one long list but I want to compare current month to last years month on the same line.

[Geeksharka]

Add a calc field called PreviousYear. It should be the Year of the current record minus 1.

Then create a relationship within the file that matches the PreviousYear field to the (current) Year field. You can then ad din the layout, via the relationship, the data from that related (previous year) record.

If you want to see the previous year's month total (as opposed to the entire year), you will need to base the relationship on the MonthName & Year, using a compound key containing both elements.

The relationship would link two compound fields, one showing:

CurrentYear & MonthName

and the other:

PreviousYear & MonthName.

Set the relationship between the two, and pull the related data into your layout.

HTH

[jjesko]

I know that I am much closer to my goal. I am choosing to to view the total by month. I have related CurrentYear&MonthName to PreviousYear&MonthName. (Also both these fields are calculations with a text result) In my new layout my Current Sales are there however none of my PreviousYear Sales are showing. Since PreviousYear&MonthName = 2001July and CurrentYear&MonthName = 2002July how can they relate? Am I missing something simple?

Are there any databases out there I can view?

[Geeksharka]

There are two records: A and B

A is the parent record, and B its child record.

In this case, B is the same month as A, but of the previous year.

There are two fields in each record (A and ???

CurrentYearMonth and PreviousYearMonth

PreviousYearMonth is a calculation based on CurrentYearMonth

The relation you need to set up from A to B is for when

PreviousYearMonth of record A

=

CurrentYearMonth of record B

Then, in A, pull the data from the related record B (such as SALES) and show them side by side.

For example, if CurrentYearMonth in A is July, 2002, then its PreviousYearMonth field will equal [color:"green"] July, 2001 -- which will match it up with record B's CurrentYearMonth value, which is also [color:"green"]July, 2001.

Use record A's PreviousYearMonth field value to hook it up to the CurrentYearMonth of record B (which by virtue of the calc and relationship would be the record with a CurrentMonthYear matching the PreviousMonthYear of record A).

HTH