Access query.. grrr!!!! 1

[daiv07]

I am going to try to explain this as best I can, it is a little confusing.

I have four tables. Transactions, Budget, Accounts and programs

Transactions contains a list of.. well, transactions and an accountID and program ID

Budget contains accountID, programID, and budget

Accounts contains account ID and account name.

Programs contains program id and program name

The budget table identifies the budget allotted to each account/program relationship, where an account can fund multiple programs and each program can be funded by multiple accounts.

What i need is a query that will return the sum of all transactions per account and the total budget for that account, taken out of the budget table.

I keep getting half there, but as soon as i try to get the sum of the budget in the same query it adds the budget amount as for each transaction listing. (i.e. if there are 100 transactions for the program/account, it will add that budget amount 100 times insted of just adding the budget for the unique account/program relationships)

I also need a similar query that will also get the transactions and budget but this time by program, not account...


any ideas?????

 

[dhookom]

You may need to "pre-summarize" your transaction table first. Then use that query rather than your entire transaction table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daiv07]

thanks!!!

That helped.

I ended up making two queries for transactions and budget then connected them.

I dunno why i didn't think of it before but it did the trick. I'm not to experienced with access SQL so i find myself getting stuck a lot.

Cheers!