Help with showing value of another record in current record

[Matthew R White]

My database is organized by a 4 digit customer number field (ex 4571) and a 2 digit year field (ex 12) the customer gets a new file each year, so 12 would indicate 12 separate files for the customer. 

the 4571 customer number field is associated with the general data for the customer, and the 2 digit year field is associated with data that changes each year.

 

We have a field for Total Premium thats associated to the 2 digit year field (so each year it changes)

 

On the current file say the (12) file, I would like to show the Total Premium field associated with the (11) file.

So I can show what the customer paid last year on my current file.

 

Any way to do this?

 

Thanks

[MartieH]

if you create a self-join relationship using the customer number field, you can then have a portal that shows your field(s) for each year.

 

Martie

[Matthew R White]

so, a self-join would duplicate the customer number field, and then link that to?

I'm not sure I understand 100% what to do with that...

[MartieH]

you create a table occurence - (TO) in the one table using a self-join relationship - the customer number field = the customer number field.

 

once this relationship is created, you can make a portal using that relationship - in the portal, place your year and total premium fields from the self-join relationship.

 

This will give you a portal on each record which will show the year and total premium for that year for each record that meets the relationship criteria - the same customer number.

 

the portal will show a row for each record with the same customer number that the current record has.

 

hth,

Martie

[Matthew R White]

That worked! Thanks! How do I get this to now show multiple records from all accounts within that table?

[comment]

I am having difficulty orienting myself in your description. It seems like you should have a table of Customers, where each customer has a unique record with a unique CustomerID (e.g. 4571), and a related table of Files(?) linked to Customers by CustomerID.

 

With this in place, you can place a portal to Files on a layout of Customers, place the Files::Total Premium field inside the portal, and thus display all the premiums for that customer.

 

 

In order to show the previous year's premium in the Files table, you can define a calculation field cPrevYear =

Year -1 

and then define a self-join of this table as:

 

Files::CustomerID = Files 2::CustomerID

AND

Files::cPrevYear = Files 2::Year

 

 

Alternatively, join only on CustomerID and use a filtered portal to display only records where:

File 2::Year = File::Year - 1