stanmcman@yahoo.com

Complex Report Help Needed

Discussion created by stanmcman@yahoo.com on Jul 14, 2014
Latest reply on Jul 15, 2014 by gdurniak

WIN 8 Pro, FMPA13, FMS12

 

This is a little complicated; I will try to be clear, thorough, and concise.

 

Description:

Trucking companies are required to file quarterly fuel taxes based on the miles they travel in each state and the fuel they purchase in each state.

 

Companies have trucks. Trucks take trips. Trips go through states. The flow is as follows:

 

Customer > Truck > Trip > State; Trip and State are joined by 2 line items tables: MilesLineItem & FuelLineItem

 

(Please Note: This is the same principle as: Customer > Branch Location > Invoice > Product, with Invoice and Product joined by LineItem, except I have 2 join tables)

 

I can successfully create new customers, create new trucks for each customer, create new trips for each truck, with every trip having different quantities of miles and fuel for various states via the line items.

 

I can also successfully run a sub-summary report in MilesLineItems & FuelLineItems which gives me the total miles or fuel for each state, for each truck, for each customer. I can successfully manipulate that data depending on how I sort.

 

The Problem:

I have a two-fold problem:

 

  1. I need to combine the related data from the MilesLineItems and the FuelLineItems, so I can get the total miles and fuel together for each state, for each trip, for each truck, for each customer. Do I need to create another table that connects these two join tables? Or should I be able to do this from the Trips table? If so, I don’t know how. What relationships would I need to do this? And more importantly, what is the best way to do this?
  2. Once I can get #1 accomplished, I need to run the equivalent of a Cross-Tabs report where I can have all 48 states listed as rows, and have the columns be calculations based on sub-summary totals for the miles and fuel for each trip, or truck, or customer, depending on the sort. I also need this report to show all 48 states, not just the ones that have miles or fuel.

 

Example: ABC Transport Inc has truck# 1 which goes on a round-trip from Grand Rapids MI to Chicago IL back to Grand Rapids, and buys fuel in IL. Here’s what the trip would look like:

 

State Miles State Fuel (gallons)

MI 200 IL 85

IN 45

IL 50

IN 45

MI 200

 

My sub-summary report from MilesLineItem would look like this:

State Miles

IL 50

IN 90

MI 400

 

My sub-summary report from FuelLineItem would look like this:

State Fuel (gallons)

IL 85

 

Here is the ultimate report I need to create:

State Miles Fuel MPG (other columns of calculated data based on the total miles and fuel for each state, based on the found set)

AL 0 0

AR 0 0

… … …

IL 50 85

IN 90 0

… … …

MI 400 0

… … …

Etc… all the way through all 48 states.

 

How can I effectively do this?

 

Please ask questions if I didn’t explain it clearly.

 

Thank you in advance for any help.

Outcomes