How to return value from different column, different row, in the same table in Access Query

[1ExcelNovice]

Hello Access Pro's,

I have a query that uses a table linked to an excel file named Shipment Report. The format of the excel file is a little funky. I'm trying to match the Static Route (i.e., 8147_B01) with the Static Route Total(s) row associated with that route and return the Cost, Fuel Cost, Total Cost, Average Cost, and Stop Count (please see last example below). I'm still fairly new to Access and still have a lot to learn. Thank you for your help in advance!

Static Route	Delivery End Date	TC Shipment ID	Cost	Fuel Cost	Total Cost	Average Cost	Stop Count
8147_B01	09/09/2013	CS02117328	273.00	12.29	285.29	285.29	1
	09/10/2013	CS02121198	273.00	12.29	285.29	285.29	1
	09/11/2013	CS02125080	273.00	12.29	285.29	285.29	1
	09/12/2013	CS02128891	273.00	12.29	285.29	285.29	1
	09/13/2013	CS02132817	273.00	12.29	285.29	285.29	1
	Static Route Total		1,365.00	61.45	1,426.45	285.29	5

<COLGROUP><COL style="WIDTH: 71pt" span=8 width=95><TBODY>
</TBODY>

I'd love for my query to return this information:

Static Route

<TBODY>

		Cost</SPAN>	Fuel Cost</SPAN>	Total Cost</SPAN>	Average Cost</SPAN>	Stop Count</SPAN>
8147_B01</SPAN>	Static Route Total</SPAN>	1,365.00</SPAN>	61.45</SPAN>	1,426.45</SPAN>	285.29</SPAN>	5</SPAN>

</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

</SPAN>

 

[sweeneytime]

Hello, would you not just use sum/count in the query?

 

[alansidman]

If you ensure that the Static Route is in every record in your source file, then you can run an aggregate query summing the values. Your table will need to look like this:

Static Route	Delivery End Date	TC Shipment ID	Cost	Fuel Cost	Total Cost	Average Cost	Stop Count
8147_B01	9/9/2013	CS02117328	273	12.29	285.29	285.29	1
8147_B01	9/10/2013	CS02121198	273	12.29	285.29	285.29	1
8147_B01	9/11/2013	CS02125080	273	12.29	285.29	285.29	1
8147_B01	9/12/2013	CS02128891	273	12.29	285.29	285.29	1
8147_B01	9/13/2013	CS02132817	273	12.29	285.29	285.29	1

<colgroup><col span="2"><col span="6"></colgroup><tbody>
</tbody>

 

[1ExcelNovice]

Thanks guys, I failed to mention that it's not just a single instance. It's a series of batch records similar to that above. So I thought about having the query add the static route to each batch of records but there are 163 different static routes and I wasn't sure how to have it seperate them correctly. Also, some static routes do not have a total of 5 stop counts either which makes it a little trickier.

What I'm trying to do is find discrepancies with the Shipment Report by having a query compare it to a Master File. I only want to compare the totals but don't know how to assign the totals to the correct static routes since it is 5 places up on the linked report. Altering the linked report is not an option so I have to be able to manipulate it through Access.

Thanks again for all your help!