How to combine 3 tables

[MNNovice]

I have three tables, tblAP, tblPayroll and tblAllocation. Each records three separate expenses. All three are related by FK ECHOID, each has common fields such as AccountNo, FundNo, GrantNo, SubClassNo, ProjectNo and Amount

Originally I created these 3 and was able to generate combined reports using queries. But I have been unable to use these queries and tie it to create a report that will show Revenue, Expenses and balance side by side. I am running into outer join ambiguity problem which I don't know how to solve.

I was wondering if I can create a 4th table called tblGrantExpense s that will automatically combine expenses from these tables perhaps it will allow me to create a report using tblGrantBudget (for revenue) and tblGrantExpense s (for expenses).

Can someone please give me some guidence? Many thanks.

[yarbrough40]

do all three tables have Revenue, Expenses and balance fields in them?

any specifics on what you are trying to accomplish would really help like:
which exact field names you are needing for the report
which tables do those fields reside in (are they all of them?)

and can you describe the result your looking for a bit more? I'm not understanding the 'side by side' you mentioned

[yarbrough40]

as long as your FK ECHOID field is present in all three tables you should be able to do as many joins as you like. Perhaps can you post an example of the working code and an example of where it began giving you trouble?

[MNNovice]

Yarborough40:

Thanks for responding to my questions.

Sample data
tblAp
ApID - PK (auto) - 1
VendorID - Number - 1 (Pulling data from tblVendor)
InvoiceDt - Date/Time - 6/7/09
InvoiceNo - Text - 002765
AccountID - Number - 5821 (pulling data from tblAccounts)
GrantFundID - Number - 872 (pulling data from tblGrantFund)
GrantNo - Text - MN900242 (automatically gets filled in once GrantFundID is selected
SubClassID - Number - 0999 (pulling data from tblSubClass
ProjectID - Number - 63740 9 (Pulling data from tblProjects)
ApAmount - Currency - $10,000

tblPayroll
PayrollID - PK (Auto) - 1
PayrollDt - Date/Time - 4/13/09
PayrollNo - Text - 09-12
AccountID through ProjectID same as tblAP
PayAmount - Currency - 25,000


tblAllocation
AllocationID - PK (auto) - 1
AllocationDt - Date/Time
AccountID through ProjectID same as tblAP
Amount - Currency - $5,000

Now tblGrantBudget has the Revenue data. Let's assume the grant number MN900242 has $100,000. Deducting these 3 expenses, this grant will have a balance of $60,000. Finding the remaining balance on a given grant is what I am trying to calculate. As new expenses are incurred in various categories (AP, payroll or Allocation) for a grant - I need to keep somewhere the remaining balance.

I cannot add tblGrant, tblGrantFunds and tblGrantBudget in a query this is where I get the outer join ambiguity problem.

I am willing to redesign the database if needed. I just want it to come out right.

Thanks for your offer of help.

M

[yarbrough40]

if I'm understanding everything correctly this should work. you could utilize a subquery also but first try this:


Select tblGrantBudget. Amount as Revenue,
(tblAp.ApAmount + tblPayroll. PayAmount + tblAllocation.A mount) as Expenses,
tblGrantBudget. Amount - (tblAp.ApAmount + tblPayroll.PayA mount + tblAllocation.A mount) as Balance
From tblGrantBudget LEFT JOIN tblAp on(tblGrantBudg et.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll. AccountID = tblAp.ProjectID ) LEFT JOIN tblAllocation on(tblAllocatio n. AccountID = tblAp.ProjectID )
Where tblGrantBudget. GrantNo = " MN900242"

[MNNovice]

Okay I will try this. But, let me first let you know that I cannot filter a grant no (MN900242), I would like a query to display balance for all grants (I have about 50 grants). How do I change, if any, the SQL for this query?

thanks.

[yarbrough40]

Select Sum(tblGrantBud get.Amount) as Revenue,
Sum(tblAp.ApAmo unt + tblPayroll. PayAmount + tblAllocation.A mount) as Expenses,
Sum(tblGrantBud get.Amount) - Sum(tblAp.ApAmo unt + tblPayroll.PayA mount + tblAllocation.A mount) as Balance
From tblGrantBudget LEFT JOIN tblAp on(tblGrantBudg et.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll. AccountID = tblAp.ProjectID ) LEFT JOIN tblAllocation on(tblAllocatio n. AccountID = tblAp.ProjectID )
Group By Revenue, Expenses, Balance


OR if this isn't what you want - type out an example of the result you are looking for please....

[MNNovice]

Balance (Grant Number: 1) = Revenue - Sum(ApExpenses + PayrollExpense + AllocationCost)

Balance (Grant Number: 2) = Revenue - Sum(ApExpenses + PayrollExpense + AllocationCost)

Balance (Grant Number: 3) = Revenue - (ApExpenses + PayrollExpense + AllocationCost)

[yarbrough40]

what I really need is an example snapshot of your expected results. so let me be clear, are you looking for a result like this?:

GrantNo ------------Revenue ----------Expenses ---------- balance
MN900242----------150,000 ---------- 40,000 -------------110,000
TK421005-----------500,000 ---------- 70,000 ------------ 430,000
GS909922 ----------80,000 ------------20,000 ------------- 60,000


if not show me where you're trying to get.

[MNNovice]

Yes exactly.

I got an error message for the SQL. It reads: Syntax error (missing operator) in query expression From tblGrantBudget LEFT JOIN tblAp on(tblGrantBudg et.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll. AccountID = tblAp.ProjectID ) LEFT JOIN tblAllocation on(tblAllocatio n. AccountID = tblAp.ProjectID )

Thanks.

[yarbrough40]

Hope This Helps....


Select tblAp.GrantNo, Sum(tblGrantBud get.Amount) as Revenue,
Sum(tblAp.ApAmo unt + tblPayroll. PayAmount + tblAllocation.A mount) as Expenses,
Sum(tblGrantBud get.Amount) - Sum(tblAp.ApAmo unt + tblPayroll.PayA mount + tblAllocation.A mount) as Balance
From (tblGrantBudget LEFT JOIN tblAp on tblGrantBudget. GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll.A ccountID = tblAp.ProjectID ) LEFT JOIN tblAllocation on(tblAllocatio n.AccountID = tblAp.ProjectID )
Group By tblAp.GrantNo, Sum(tblGrantBud get.Amount), Sum(tblAp.ApAmo unt + tblPayroll. PayAmount + tblAllocation.A mount), Sum(tblAp.ApAmo unt + tblPayroll.PayA mount + tblAllocation.A mount)

[MNNovice]

I got the same syntax error message on this line:

(tblPayroll.Acc ountID = tblAp.ProjectID ) LEFT JOIN tblAllocation on(tblAllocatio n.AccountID = tblAp.ProjectID )

[yarbrough40]

experiment with changing the join types (INNER JOIN, RIGHT JOIN, etc..)

oh I think I see an issue... use the common ProjectID/AccountID for the initial join instead of the GrantNo.






if this doesn't work then it means your databse structure will need you to create a couple of separate queries in order to force the order of the joins.

so you would create query1, query2
do your sum calculations with the appropriate joins then bring them together

select GrantNo, Revenue, Expenses, Balance
from query1 Inner Join query2

[MNNovice]

Thank you for your tips. I don't know how to do these. I will need to read your instructions thoroughly to understand it. But unfortunately I am short on time right now as I am leaving for vacation. I will play with it upon my return on July 13.

Thanks and have a good day.