How to group sums and such...

[grande]

Okay so I have two tables tblExpenses and tblExpenseDetails (1-to-Many). tblExpenses has ProjectID and ExpenseType. There are 3 different types of Expenses (Purchase, Travel, Contract).

I would like to have a query that totals the Expense Details by Project and Expense Type. ie, each project will have 3 records in the query.

Sample Data: Pre-Query

tblExpenses
ExpenseID ProjectID ExpenseType
1         1         Purchase
2         2         Travel
3         1         Contract
4         1         Purchase

tblExpenseDetails
ID UnitPrice #ofUnits ExpenseID
1  $2        5        1
2  $5        6        1
3  $7        2        2
4  $2        9        2        
5  $3        6        3
6  $9        1        3
7  $1        9        4
8  $5        5        4

Sample Data: Post-Query

ProjectID ExpenseType Total
1         Purchase    $74
1         Contract    $27
2         Travel      $32

I also have to do taxes and such, but I'm pretty sure that if I get this, I'm pretty sure I can figure out the rest.

-------------------------
Just call me Captain Awesome.

 

[traingamer]

Try:

SELECT tblExpenses.ProjectID, tblExpenses.ExpenseType, Sum(tblExpenseDetails.UnitPrice) AS Total
FROM tblExpenses INNER JOIN tblExpenseDetails ON tblExpenses.ExpenseID = tblExpenseDetails.ExpenseID
GROUP BY tblExpenses.ProjectID, blExpenses.ExpenseType;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[grande]

Now I'm getting an error message that says
"You tried to execute a query that does not include the specified expression 'ExpenseType' as part of an aggregate function."

Any ideas?

-------------------------
Just call me Captain Awesome.

 

[lespaul]

what's the SQL you used?

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[traingamer]

I have (at least one) typo in my posting

GROUP BY tblExpenses.ProjectID, [COLOR=red]t[/color]blExpenses.ExpenseType;

 

[grande]

I just copy & pasted exactly what traingamer typed. All the field names are correct.

I have the field ExpenseType set up as a ComboBox (In table design mode, under the "Lookup" tab). I use the SQL

SELECT [tblBudgetType].[BudgetTypeID], [tblBudgetType].[BudgetTypeText] FROM tblBudgetType;

to get the ExpenseType.

-------------------------
Just call me Captain Awesome.

 

[grande]

Crap, it was a spelling mistake that I had missed, thanks guys!

-------------------------
Just call me Captain Awesome.