What Relationship Must Be Established to Get Totals from Two Tables?

[userfmp]

I have the following:

 

Table 1 – Expenses

 

Field A: Date

Field B: Concept

Field C: Amount

 

Table 2 – Flight Time

 

Field D: Date

Field E: Flight Time (in hours)

 

I need a report that will give me the expense cost per hour, i.e. the total of Field C divided by the total of Field E.

 

Table 1 and Table 2 are not related. What relationship must I establish between them to obtain my desired result?

 

If establishing a relationship between the tables is not the answer, how else can I achieve the figure I need?

 

Thank you for any help that can be provided. 

[bcooney]

Are the tables related to the same parent table? I would think that each has a foreign key. Otherwise, I'm not sure that I understand what you're doing.

If no relationship, you could always calculate using ExecuteSQL.

Can you describe this use case in a paragraph?

[userfmp]

bcooney,

 

Thank you for your response.

The tables are not related in any way.

In table 1 I have records like:

01/01/17 Fuel $10

03/17/17 Catering $11

06/09/17 Repairs $12

In table 2 I have records like

02/02/17 1.5 hours

08/05/17 2.2 hours

I need a layout in which I can include the following calculation:

10 + 11 + 12 = $33 divided by 1.5 + 2.2 = 3.7 for a total of 33/3.7 = $8.92

[bcooney]

Imagine you have many of the first type and second types of records. You always want a single total referencing all records? Isn't there a Flight ID in common?

Are you really using FM11?

If not, here's a demo showing how I might use sql to do this.

demo.fmp12.zip

[userfmp]

bcooney,

Yes to your question about many records in table 1 and many in table 2.

I need a single total referencing all records but by year, i.e. some of the records in table 1 and table 2 belong to different years. 

No flight ID or any other field in common.

Yes, I am using FMP 11. 

 

[bcooney]

Sorry, I can't think in fm11 anymore. 

[Fitch]

You could create a Year table, with a record for each year. With a start and end date in the Year table, you could make a relationship to the date fields in the other tables. Then your calculation in the Year table would look like:

Round( Sum( Expenses::Amount ) / Sum( Flight Time::Flight Time ) ; 2 )