Dear Donna:
Whew! Can we cut down your query a bit? My eyes are wearing out!
SELECT DISTINCTROW PEP.[SumOfClaim Amt], PBP.BudgetAmt,
GF.[Expend Code], PC.Program, PER.[SumOfClaim Amt],
GF.[Acct Desc], PC.ProgDesc, OG.SortOrder, GF.ObjectGroup,
OG.Description, GF.[Fund No]
FROM (([Account No-Keys in General Fund] GF
LEFT JOIN [Object Groups] OG ON GF.ObjectGroup = OG.ObjectGroup)
INNER JOIN (([ProgramExpense Period Summary Query] PEP
LEFT JOIN [ProgramExpense Date Range Summary Query] PER
ON (PEP.[Expend Code] = PER.[Expend Code])
AND (PEP.Program = PER.Program))
LEFT JOIN ProgramCodes PC ON PEP.Program = PC.Program)
ON GF.[Expend Code] = PEP.[Expend Code])
LEFT JOIN [ProgramBudgets Period Query] PBP
ON ([PEP.Program = PBP.PROGRAM)
AND (PEP.[Expend Code] = PBP.[EXPEND CODE])
ORDER BY GF.[Expend Code], PC.Program, OG.SortOrder;
Now, what I have done is to introduce aliasing for those long table
names, and added some indentation and line formatting. Hopefully I
didn't screw it up. Anyway, this improves the readability for me.
Could we look at all your LEFT JOINs for a moment. In each case, is
it the case that there are rows in the table to the left that don't
have child / dependent rows in the table on the right?
For example, are there rows in GF (General Fund) with values in the
ObjectGroup column where you don't have a corresponding ObjectGroup
row in [Object Groups]?
The same question applies to the other LEFT JOINS you have.
The answer to this can best be found in your database designs.
What I need to know primarily is where to divide this query work into
two parts, one for the expenses side and one for the budget side.
Perhaps it would be especially useful to create separate queries that
perform the two sides independently, given the complexity of what you
have so far.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Thank you for your comments, Tom. As I was considering my "attitude" I
realized you may save me lots of time. When I looked in the manuals I see
very little on Union queries. My first query that works uses 3 queries to
arrive at the results as follows. When I pasted the second query with it with
Union All in between I got the error message that [Account no-...].[Expend
Code] could refer to more than one table listed in the from clause. I do have
only one table, but the three queries all use it to limit their results. Here
is the query that results in "if no expense, then no budget". I didn't know
if you need to see the one that results in "if no budget, then no expense".
Thank you so much for your help.
SELECT DISTINCTROW [ProgramExpense Period Summary Query].[SumOfClaim Amt],
[ProgramBudgets Period Query].BudgetAmt, [Account No-Keys in General
Fund].[Expend Code], ProgramCodes.Program, [ProgramExpense Date Range Summary
Query].[SumOfClaim Amt], [Account No-Keys in General Fund].[Acct Desc],
ProgramCodes.ProgDesc, [Object Groups].SortOrder, [Account No-Keys in General
Fund].ObjectGroup, [Object Groups].Description, [Account No-Keys in General
Fund].[Fund No]
FROM (([Account No-Keys in General Fund] LEFT JOIN [Object Groups] ON
[Account No-Keys in General Fund].ObjectGroup = [Object Groups].ObjectGroup)
INNER JOIN (([ProgramExpense Period Summary Query] LEFT JOIN [ProgramExpense
Date Range Summary Query] ON ([ProgramExpense Period Summary Query].[Expend
Code] = [ProgramExpense Date Range Summary Query].[Expend Code]) AND
([ProgramExpense Period Summary Query].Program = [ProgramExpense Date Range
Summary Query].Program)) LEFT JOIN ProgramCodes ON [ProgramExpense Period
Summary Query].Program = ProgramCodes.Program) ON [Account No-Keys in General
Fund].[Expend Code] = [ProgramExpense Period Summary Query].[Expend Code])
LEFT JOIN [ProgramBudgets Period Query] ON ([ProgramExpense Period Summary
Query].Program = [ProgramBudgets Period Query].PROGRAM) AND ([ProgramExpense
Period Summary Query].[Expend Code] = [ProgramBudgets Period Query].[EXPEND
CODE])
ORDER BY [Account No-Keys in General Fund].[Expend Code],
ProgramCodes.Program, [Object Groups].SortOrder;
Tom Ellison said:
Dear Donna:
I congratulate you on your attitude! By grabbing a concept and doing
all you can to run with it yourself you will likely learn the greatest
amount possible. Before we know it, you'll be the one in this
newsgroup answering questions, not asking them.
But don't be embarassed now to come back for more help if needed.
That's a valid part of the process, too!
I deliberately frame my answers to initially be strong on concepts and
light on details in the hope that someone like you will gain the
maximum by picking up the concepts and running with them. But it
rarely works out that way. That's why I'm so proud of you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Wed, 24 Nov 2004 07:49:15 -0800, DonnaG
Thank you, Tom. As I read other posts last night I saw Union as a
possibility, but am new to the concept. I am using Jet and I am beginning to
understand SQL. I have many manuals as well. I will attempt to create a Union
ALL by copying the SQL from the two queries that work into a new Union query.
I'll use a manual for tips. I'll reply back for the next details of my
progress. If I don't get the Union ALL to work in the first step, I'll post
it for you in my next response to clean up.
DonnaG
:
Dear Donna:
It seems to me the clue to your problem is in your statement:
"it is possible to have an expense without a budget and a budget
without an expense."
This is virtually a prescription for the need to have a full outer
join. If you are using Access Jet, which does not have full outer
joins, it will take a small amount of additional effort.
In the case of Jet, you can use a UNION of a LEFT JOIN and a RIGHT
JOIN. I find a variation of this to be best, which is to use a UNION
ALL (which is faster) and filter out the otherwise duplicated rows
from the RIGHT JOIN portion, those being the rows that have both and
expense and a budget.
That's the theory anyway. If some of this seems new or foreign to
you, well that's OK. Having covered the ground in theory, we can work
our way up to it in practice. This may take a day or two of
correspondence to accomplish. Assuming for the moment you may need
this help, here's what I'd ask you to do to get it started.
Please post a query with a simple INNER JOIN that shows just what you
want but limited to those rows that have both budget and expense data.
We can work forward from there. OK?
If you are using MSDE or some other database than Jet, please specify.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Nov 2004 17:43:03 -0800, DonnaG
I'm unable to develop the proper queries that result in a report of 100% of
budgets by account number and program that also include any expenses. One
issue is that it is possible to have an expense without a budget and a budget
without an expense. I've created two queries that give results for each
scenario, but I don't know how to set up the final query that gives me 100%
of budgeted amount and 100% of actual expenses by account number and program
for the report. It sounds easy but has not been.
