presenting totals with relationships

[Mirthly]

I'm an MS Access developer that is attempting to switch to FileMaker for cross-platform support. I've been reading and viewing as much material as possible, but have come up to a stumbling block that I hope everyone here will help me with. Specifically, I've been asked to review an existing database in order to update the reports to include additional breakdowns of information.

Originally, there is a table that contains Client information and another table that contains Residency information.

Client Example:

ID: 1, First Name: john, Last Name: smith, Residency: 1

Residency Example:

ID: 1, Type: over 3 to 5 years, Abbrev: 4-5, FixedValue: 1

In the original structure, the designer used a drop-down list in the Client table that was based on a value list using the Residency table. He was then able to relate the two tables together as CLIENT--client_RESIDENCY and was able to present a layout showing a portal displaying the residency entries, with a "count" of the entries in client. This looked like:

Under 1 year: 50 clients

1 to 3 years: 100 clients

over 3 to 10 years: 2030 clients

unknown: 5 clients

This worked fine, until someone decided that it was time to break the categories down a little more. They wanted the data to update based on a date, rather than being fixed in place at time of data entry (this is a good idea).

I've added the ResidencyDate field to the client table. I've added a calculated field to the client table that finds the number of years of residency, as follows:

intResidencyLen = If (IsEmpty(datResidency);-1;Year(Get(CurrentDate)) - Year(new_ResidencyDate) - If(DayOfYear(Get(CurrentDate)) - DayOfYear(datResidency) > 1;0;-1))

This works to identify the number of years they are residents. I then added two fields to the Residency table to show the minimum and maximum years for each line as follows:

Residency Example:

ID: 1, Type: over 3 to 5 years, Abbrev: 4-5, FixedValue: 1, intMinYears: 4, intMaxYears: 5

I then created a second calculated field, ptrResidency to find the correct entry in the Residency table using the following relationship:

client::intResidencyLen --- residency::intMaxYears

client::intResidencyLen --- residency::intMinYears

I store the residency::ID in this new field (ptrResidency).

I was hoping that I'd be able to create the same relationship client::ptrResidency --- residency::ID and then be able to use the "COUNT" function to count the FixedValue field (which is always a 1).

This isn't working. The counter is empty in my new design even though it works with the old structure. I'm fairly certain this is due to the calculation field. More over, I'm betting it is related to the fact that the value isn't stored but calculated each time it is needed.

If anyone has any advice for me, I'd be very much appreciative!

thanks.

[comment]

I'm betting it is related to the fact that the value isn't stored

You would win on that bet. Why does this have to be done through a relationship? You could simply calculate the bracket in the Clients table, then sort and sub-summarize by it.

[Mirthly]

You would win on that bet. Why does this have to be done through a relationship? You could simply calculate the bracket in the Clients table, then sort and sub-summarize by it.

Actually, this doesn't have to be done via relationship and a simple display portal, however, that was how it was done originally. I'll see if I can figure out how to sort and sub-summarize by it. I'll keep you posted with my results-- and most likely follow-up questions.

Thanks!

[Mirthly]

You would win on that bet. Why does this have to be done through a relationship? You could simply calculate the bracket in the Clients table, then sort and sub-summarize by it.

I am understanding why now. The summary page in this database contains many different portals used to display summary totals for various aspects of the data collected. This information is transferred to a legal document that is submitted up through the management.

In order to use the sub-summarize process, a single layout would need to be created for each summary and displayed separately. While this isn't horrible, it wouldn't be optimal for the person using the database to get the summaries.

Can you think of any way to maintain multiple summaries on one layout?

I think another way to look at this that might be more common would be to use Age...

Store a birth date in the client table. Create a table that contains ranges that will be grouped together (0-5 years old, 6 to 10 years old, etc). Associate the birth date with the client's current age as of report run. Total all clients ages and display the totals beside their category. Under this summary, include the residency summary as mentioned before. Another on the page is country of origin, which are listed based on a standardized "code number" so also have a related table displaying the country and the code.

If I have to split these into three layout/reports to get the information, I'll do it for sure. But it looked much nicer to have them all displayed in the one layout.

I'm really hoping that there will be magic in the replies!

[comment]

I see. How many of these clients will you have (including future growth)?

[Mirthly]

I see. How many of these clients will you have (including future growth)?

The numbers apparently change each year. Digging into the database as it stands, currently active clients are at slightly under 900. There is currently under 15000 total clients but only the "active" ones are listed in the reporting process. Some may return, some won't. We keep their information "in case".

[comment]

I see two options here:

1. Place a one-row portal to Clients on your report layout. This should be based on a relationship showing only active clients. Place a summary field counting the clients (defined in the Clients table) inside the portal. Filter the portal to show only records whose age is less than 1.

Repeat the process for the other age brackets.

2. In the Brackets table, calculate the minimum and the maximum residency start dates of each bracket, based on current date and the bracket's minimum and maximum age. Use these to define the relationship to Clients as:

Brackets::cMinDate ≤ Clients::StartDate

AND

Brackets::cMaxDate > Clients::StartDate

[Mirthly]

I see two options here:

1. Place a one-row portal to Clients on your report layout. This should be based on a relationship showing only active clients. Place a summary field counting the clients (defined in the Clients table) inside the portal. Filter the portal to show only records whose age is less than 1.

Repeat the process for the other age brackets.

2. In the Brackets table, calculate the minimum and the maximum residency start dates of each bracket, based on current date and the bracket's minimum and maximum age. Use these to define the relationship to Clients as:

Brackets::cMinDate ≤ Clients::StartDate

AND

Brackets::cMaxDate > Clients::StartDate

You are awesome! Option 2 made a light-bulb go off in my head.

This has worked for me. My issue was that I was trying to create the calculations on the wrong side of the relationship. So by moving the calculation into the child table instead of the parent, I was able to use the relationship to summarize the information and keep the page looking the same as before.

Not sure why my head couldn't see that I should just flip that around...but I couldn't/didn't.

Thank you SO much for your help.

--Mirthly