Pivot Tables vs. Report in Access

[Guest]

I am working with someone who is inputting the same information 3 times.
Once in an expense report (Excel), once in the budget (Excel) and then once
into Access so they can print a report. I am having them link the two Excel
workbooks, so that should help a little, but I was wondering if a Pivot Table
would do the trick for them in regards to reports. It seems silly to just
use Access just for a report. But I have not worked with Pivot Tables much,
so I do not know.

Here are their column headings:

Field Officer, Trip ID, Nights/Days, Location, Date, Misc, Taxi, Gas, Meals,
Mileage, Airfare, Auto, Hotel, Entertainment, Total Cost

Their report in Access are by Travel Expenses by Field Officer and by
Location.

With all the expenses being column headings I am not sure how to go about
this.

Any ideas?

Thank you

 

[Guest]

It sounds like a simple sort and subtotals (under Data menu) will give you
what you want.

 

[Guest]

In XL Select Data -> Pivot Table or Report. A wizard will come up. When asked
for the data source select External Data and browse for the Access Database.
Complete the Wizard. Place the Expense Type in the Row and the value in the
Middle. You can also add the Location and the field oficer to the row or
column fields or to the Pivot Filter at the top.

 

[Guest]

Thank you.

I would prefer to keep Access out of it. Excel already contains all the
data. My problem is when it comes to Expense Type. They are all broken out
into their own column headings. So I have to indiviually add each expense,
then the Pivot Table becomes not so easy to read. But if I make and Expense
Type column and put the expenses (misc, taxi, gs, meals, etc) under that
column heading then I create a lot more data to be entered. Each person then
could have upto nine different records (for each expense) just for one trip.
Does this make sense?

Thank you for your help.

 

[Debra Dalgleish]

Ideally, you'd have the expense type in one column, and amounts in one
column. However, with the layout you have now, you could create a pivot
table with Field Officer and Location in the Row area, and all the
expense fields in the data area.
After you create the pivot table, drag the Data field button onto the
cell that contains the word Total, to arrange the data fields horizontally.