SQL Multiple Queries

[mbake085]

Hi Everyone,

I am attempting to automate our budgeting document for purchasing. Here is a summary of our purchasing process:

The budget is allocated at the VP level. Engineers from various VP groups request money to spend, that money is approved and taken out of the budget as an estimated amount. As invoices are approved and reported, the "actual" amounts are taken out of the appropriate budget pool replacing the estimated amount.

I have the relationships made and two queries to group and create estimated and actual totals for each budget pool, which is half the battle. My problem is the final product.



I want each VP to pull a sheet summarizing their budget data. Which includes: all budget pools, estimated amounts deducted from appropriate pools and actual amounts deducted to replace the estimate.

My only problem is as I take the original budget data and add one of the sum queries, only the data related to that sum query is visible. I want it to include all budgets even if no estimated or actual amounts have been deducted. Any way of doing this? I haven't worked on it much, maybe a query isn't the correct way to do this...

Thanks,

Matt

[NTC]

Well its very do-able; but difficult to answer in a forum setting because the correct answer depends on how you decide to set up your data structure.

Off the cuff it seems like classic 1:Many tables Budget:Requests Requests:Invoices

I trust you have an Access textbook and are reading thru it. It is tough to design a database otherwise.

Hope this helps a little.

[mbake085]

Could I run an update query that sets the values using a separate query?

Thanks,

Matt

[mbake085]

Update budget
set
requested_other_budget=fr_tracking.requested_other _budget, requested_labor_budget=fr.tracking_requested_labor _budget, spent_other_budget=invoices.spent_other_budget, spent_labor_budget=invoices.spent_labor_budget
from
(select id, -sum (other_budget) as requested_other_budget, -sum (labor_budget) as requested_labor_budget from fr_tracking group by id) as fr_tracking,
((select id, sum (spent_other_budget) as actual_other_spent, sum (spent_labor_budget) as actual_labor_spent from (select pr, -sum (other_budget) as spent_other_budget, -sum (labor_budget) as spent_labor_budget from invoices group by pr) as invoices inner join fr_tracking on invoices.pr = fr_tracking.pr) group by id)) as invoices
where budget.id=fr_tracking.id and invoices.id;

[NTC]

If I understand your post - I don't think you are on the right track. It isn't that complicated and really comes down to the table structure of the data itself.

Your core goal stated is: "I want it to include all budgets even if no estimated or actual amounts have been deducted."

Assuming you have a budget table; then you can show all values. If there is no join data in the request table for a specific budget item ...then simply use an outer join in the query so that all budgets display in the query results.

[mbake085]

Wow, thanks. Don't have much experience with this stuff, but I was starting to think it was hopeless.

Sincerely,

Matt