R
Robert T
Hi:
I just created a Travel Expense database so there are very few entries in
the child table called tblExpenses. I want to create a subreport so I created
a query that groups on TravelID [each trip has a unique TravelID], the
TypeExpense [Air, Hotel, etc.] and then I want to sum up the total expense in
each category.
Here's what the table data looks like, I hope the columns line up:
TravelID DateExpenseTypeExpense AmountExpense
----------------------------------------------------------------------
001 5/12/2008 Hotel $201.64
001 5/12/2008 Taxi $10.00
001 5/12/2008 Airfare $399.00
001 5/13/2008 Hotel $201.64
001 5/14/2008 Hotel $201.64
001 5/12/2008 Per Diem $40.50
001 5/13/2008 Per Diem $54.00
001 5/15/2008 Hotel $201.64
001 5/14/2008 Per Diem $54.00
001 5/15/2008 Per Diem $54.00
001 5/16/2008 Taxi $30.00
001 5/16/2008 Per Diem $40.50
001 5/16/2008 Airfare $399.00
001 5/12/2008 Mileage $21.72
001 5/16/2008 Mileage $21.72
001 5/16/2008 Tip $10.00
002 6/9/2008 Airfare $350.50
002 6/9/2008 Car Rental $40.72
002 6/9/2008 Hotel $131.10
003 6/16/2008 Airfare $150.50
003 6/20/2008 Airfare $369.00
I created what I thought would be a simple totals query and the numbers are
way off. The totals are about ten times higher than they should be so I know
I'm doing something STUPID!
Here's the query:
SELECT tblExpenses.TravelID, tblExpenses.TypeExpense,
Sum(tblExpenses.AmountExpense) AS SumOfAmountExpense
FROM tblExpenses, LtblRegionalOffices
GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense;
I just created a Travel Expense database so there are very few entries in
the child table called tblExpenses. I want to create a subreport so I created
a query that groups on TravelID [each trip has a unique TravelID], the
TypeExpense [Air, Hotel, etc.] and then I want to sum up the total expense in
each category.
Here's what the table data looks like, I hope the columns line up:
TravelID DateExpenseTypeExpense AmountExpense
----------------------------------------------------------------------
001 5/12/2008 Hotel $201.64
001 5/12/2008 Taxi $10.00
001 5/12/2008 Airfare $399.00
001 5/13/2008 Hotel $201.64
001 5/14/2008 Hotel $201.64
001 5/12/2008 Per Diem $40.50
001 5/13/2008 Per Diem $54.00
001 5/15/2008 Hotel $201.64
001 5/14/2008 Per Diem $54.00
001 5/15/2008 Per Diem $54.00
001 5/16/2008 Taxi $30.00
001 5/16/2008 Per Diem $40.50
001 5/16/2008 Airfare $399.00
001 5/12/2008 Mileage $21.72
001 5/16/2008 Mileage $21.72
001 5/16/2008 Tip $10.00
002 6/9/2008 Airfare $350.50
002 6/9/2008 Car Rental $40.72
002 6/9/2008 Hotel $131.10
003 6/16/2008 Airfare $150.50
003 6/20/2008 Airfare $369.00
I created what I thought would be a simple totals query and the numbers are
way off. The totals are about ten times higher than they should be so I know
I'm doing something STUPID!
Here's the query:
SELECT tblExpenses.TravelID, tblExpenses.TypeExpense,
Sum(tblExpenses.AmountExpense) AS SumOfAmountExpense
FROM tblExpenses, LtblRegionalOffices
GROUP BY tblExpenses.TravelID, tblExpenses.TypeExpense;