I am trying to include a summary calculation for costs on a report by fiscal year and building for a construction project. The database stores the information in four tables: Fiscal Year, Buildings, Projects, and Tasks. The Fiscal Year table is linked to the Projects table by FY and includes calculated summaries for each project year. The Building table is linked to the Tasks table by building. The Tasks table is linked to the Projects table by project number.
My problem occurs due to the costs being separated into design costs and construction costs in the Projects table with each having a different fiscal year. Thus to summarize a single fiscal year by building requires first separately summarizing the design and construction costs by fiscal year and then totaling them. I can easily accomplish this in MS Access by creating intermediate queries but I don't know how to do it in Filemaker Pro which I only use infrequently.