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!


[TABLE="width: 568"]
<COLGROUP><COL style="WIDTH: 71pt" span=8 width=95><TBODY>[TR]
[TD="class: xl64, width: 95, bgcolor: silver"]Static Route[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]Delivery End Date[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]TC Shipment ID[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]Cost[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]Fuel Cost[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]Total Cost[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]Average Cost[/TD]
[TD="class: xl64, width: 95, bgcolor: silver"]Stop Count[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 95, bgcolor: transparent"]8147_B01[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]09/09/2013[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]CS02117328[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]273.00[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]12.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 95, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]09/10/2013[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]CS02121198[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]273.00[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]12.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 95, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]09/11/2013[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]CS02125080[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]273.00[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]12.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 95, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]09/12/2013[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]CS02128891[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]273.00[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]12.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 95, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]09/13/2013[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]CS02132817[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]273.00[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]12.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 95, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"]Static Route Total[/TD]
[TD="class: xl65, width: 95, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]1,365.00[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]61.45[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]1,426.45[/TD]
[TD="class: xl66, width: 95, bgcolor: transparent"]285.29[/TD]
[TD="class: xl67, width: 95, bgcolor: transparent"]5[/TD]
[/TR]
</TBODY>[/TABLE]

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

[TABLE="width: 557"]
<TBODY>[TR]
[TD="align: center"]Static Route[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cost</SPAN>[/TD]
[TD="align: center"]Fuel Cost</SPAN>[/TD]
[TD="align: center"]Total Cost</SPAN>[/TD]
[TD="align: center"]Average Cost</SPAN>[/TD]
[TD="align: center"]Stop Count</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]8147_B01</SPAN>[/TD]
[TD="align: center"]Static Route Total</SPAN>[/TD]
[TD="align: center"]1,365.00</SPAN>[/TD]
[TD="align: center"]61.45</SPAN>[/TD]
[TD="align: center"]1,426.45</SPAN>[/TD]
[TD="align: center"]285.29</SPAN>[/TD]
[TD="align: center"]5</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
</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:

[TABLE="width: 534"]
<colgroup><col span="2"><col span="6"></colgroup><tbody>[TR]
[TD]Static Route[/TD]
[TD]Delivery End Date[/TD]
[TD]TC Shipment ID[/TD]
[TD]Cost[/TD]
[TD]Fuel Cost[/TD]
[TD]Total Cost[/TD]
[TD]Average Cost[/TD]
[TD]Stop Count[/TD]
[/TR]
[TR]
[TD]8147_B01[/TD]
[TD="align: right"]9/9/2013[/TD]
[TD]CS02117328[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]12.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8147_B01[/TD]
[TD="align: right"]9/10/2013[/TD]
[TD]CS02121198[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]12.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8147_B01[/TD]
[TD="align: right"]9/11/2013[/TD]
[TD]CS02125080[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]12.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8147_B01[/TD]
[TD="align: right"]9/12/2013[/TD]
[TD]CS02128891[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]12.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8147_B01[/TD]
[TD="align: right"]9/13/2013[/TD]
[TD]CS02132817[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]12.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]285.29[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!