Help with sum(field) plz

[savok]

I have 3 tables, Table1 has OrgID, Table2 has BudID, Table3 has PayID. All are Primary Autonumbers.

I need to select some fields from table 2 and a sum of one field from Table3. Where OrgID in table2 = "selected id"

here is the code i use in vb. Total is a currency field, date is a date field others are ids.

Select b.BudID, b.Date, sum(p.Total) from Budgets b, Payments p where b.OrgID = " & strOrgID & " and b.BudID = p.BudID"

the error that i get is "You tried to execute a query that does not include the specified expression 'BudID' as part of an aggregate function"

Does anyone know what I can be doing wrong?
thanks in advance

omg i hope this made sense to someone

 

[gwinn7]

Yes, you need to include the GROUP BY clause for all non-calculated fields in your query.

Gary
gwinn7

 

[Cagliostro]

I do not know how is it in Access but in SQL Server it work.
select sum(p.Total) from Payments p
where p.BudID
in
(
select BudID from Bugets b where b.OrgID = " & strOrgID & "
) John Fill

 

[savok]

Do you mean

Select b.BudID, b.Date, sum(p.Total) from Budgets b, Payments p where b.OrgID = " & strOrgID & " and b.BudID = p.BudID group by b.Date

if so it didnt work

 

[Cagliostro]

There are many variants. The following one also run well in SQL Server
select sum(t.Total), t.Date from
(Select b.BudID budID, b.Date Date, p.Total Total from Budgets b, Payments p where b.OrgID = " & strOrgID & " and b.BudID = p.BudID) t
group by t.Date John Fill

 

[savok]

thanks I'll try em and let you know

 

[savok]

Nope doesnt work Guess cause its Access so its diff syntax.

DBComm.CommandText = "Select b.Bud_ID, b.Bud_Start_Date, b.Bud_End_Date, b.Bud_Total_Funds, p.Pay_Total_Used as Total_Used from Budgets b, Payments p where b.Org_ID = " & strOrgID & " and b.Bud_ID = p.Bud_ID order by b.Bud_Start_Date"

the above works completely fine

as soon as I change it to sum(p.Pay_Total_Used)

It gives an error. So maybe you cant use sum in this way?

 

[Cagliostro]

Try following:
select sum(t.Total), t.Date from
(Select b.BudID as budID, b.Date as Date, p.Total as Total from Budgets as b, Payments as p where b.OrgID = " & strOrgID & " and b.BudID = p.BudID) as t
group by t.Date John Fill