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 tblGrantExpenses that will automatically combine expenses from these tables perhaps it will allow me to create a report using tblGrantBudget (for revenue) and tblGrantExpenses (for expenses).
Can someone please give me some guidence? Many thanks.
13 3222
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
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?
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
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.Amount) as Expenses,
tblGrantBudget.Amount - (tblAp.ApAmount + tblPayroll.PayAmount + tblAllocation.Amount) as Balance
From tblGrantBudget LEFT JOIN tblAp on(tblGrantBudget.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll. AccountID = tblAp.ProjectID) LEFT JOIN tblAllocation on(tblAllocation. AccountID = tblAp.ProjectID)
Where tblGrantBudget.GrantNo = " MN900242"
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.
Select Sum(tblGrantBudget.Amount) as Revenue,
Sum(tblAp.ApAmount + tblPayroll. PayAmount + tblAllocation.Amount) as Expenses,
Sum(tblGrantBudget.Amount) - Sum(tblAp.ApAmount + tblPayroll.PayAmount + tblAllocation.Amount) as Balance
From tblGrantBudget LEFT JOIN tblAp on(tblGrantBudget.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll. AccountID = tblAp.ProjectID) LEFT JOIN tblAllocation on(tblAllocation. 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....
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)
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.
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(tblGrantBudget.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll. AccountID = tblAp.ProjectID) LEFT JOIN tblAllocation on(tblAllocation. AccountID = tblAp.ProjectID)
Thanks.
Hope This Helps....
Select tblAp.GrantNo, Sum(tblGrantBudget.Amount) as Revenue,
Sum(tblAp.ApAmount + tblPayroll. PayAmount + tblAllocation.Amount) as Expenses,
Sum(tblGrantBudget.Amount) - Sum(tblAp.ApAmount + tblPayroll.PayAmount + tblAllocation.Amount) as Balance
From (tblGrantBudget LEFT JOIN tblAp on tblGrantBudget.GrantNo = tblAp.GrantNo)
LEFT JOIN tblPayroll on(tblPayroll.AccountID = tblAp.ProjectID) LEFT JOIN tblAllocation on(tblAllocation.AccountID = tblAp.ProjectID)
Group By tblAp.GrantNo, Sum(tblGrantBudget.Amount), Sum(tblAp.ApAmount + tblPayroll. PayAmount + tblAllocation.Amount), Sum(tblAp.ApAmount + tblPayroll.PayAmount + tblAllocation.Amount)
I got the same syntax error message on this line:
(tblPayroll.AccountID = tblAp.ProjectID) LEFT JOIN tblAllocation on(tblAllocation.AccountID = tblAp.ProjectID)
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: snOOp |
last post by:
I am trying to combine the data from two similar tables into one
query, but I need for all of the records from both tables to show up
and I want the ones that have matching 'emplid' to be combined...
|
by: Jamie Pittman via AccessMonster.com |
last post by:
I have two tables with 5000 entries on them. One is based for regular time
with several variables example (employee name, date,time in and out, code,
customer, building) I have another table that...
|
by: mojo |
last post by:
We have set up an Access database with tables x,y, & z where we would
like to have multiple people entering data into a table x. Each person
has an identical copy of the database on their PC's. ...
|
by: Reidar Jorgensen |
last post by:
I have several Access databases, identical in structure, but different data. Is there an
easy way to combine them all into one database? There are six tables, I just want the
data from all...
|
by: Red |
last post by:
So I inherited this db that has multiple 'orders' tables.
Each order table is name for the year of the orders...
i.e. 2005 is 'Orders-2005', 2006 is "Orders-2006"
So, I am not allowed to...
|
by: Roy |
last post by:
No errors here, I'm just looking for ways to make my code more
efficient. As the subject line suggests, I have a LOT of repeaters on a
certain page. They are visible or hidden depending on user...
|
by: Lars E. |
last post by:
Hi all.
I am trying to combine data from 2 tables in one datagridview.
I have tables: "Customer" and "CustomerContact".
I want to display Customer information (displaying all fields in...
|
by: nugz |
last post by:
I want to combine 3 tables with like data then append it with a filter.
Tables: NewStarts, Complaints, Memos
Combine: Date, Address, Route, Pub, etc....
Then sort: previous 8 days, pub/freq...
|
by: bluereign |
last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script...
|
by: stateemk |
last post by:
Hi,
I'm probably making this much harder than it needs to be, but I just can't think of how to do this. Each year, a database is created with all employees on it. I need to pull all the employees...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |