multiple queries problem

[Adam]

Using Access 2003 on Windows XP

I have three tables
tblCategories with just category names and ID numbers
tblExpense with fields for date of expense, amount of expense and which
category the expense is in (related to tblCategory)
tblBudget with fields for year, month, category, and amount budgeted

I have the following query, qryExpenseSum, to sum the expenses for each
month for each category:

SELECT DatePart("yyyy",[dteDate]) AS numYear, DatePart("m",[dteDate]) AS
numMonth, tblExpense.numCategory, Sum(tblExpense.curAmount) AS SumOfcurAmount
FROM tblExpense
GROUP BY DatePart("yyyy",[dteDate]), DatePart("m",[dteDate]),
tblExpense.numCategory
HAVING (((DatePart("yyyy",[dteDate]))=[forms]![frmHome].[numYear]));

What I would like is to create another query that puts the total amount
spent next to the amount budgeted for each category in each month so that I
can find the difference and put it in a report. I was almost able to acheive
that with the following query

SELECT qryExpenseSum.numYear, qryExpenseSum.numMonth,
qryExpenseSum.numCategory, tblCategory.strCategory, tblBudget.curBudget,
qryExpenseSum.SumOfcurAmount, [curBudget]-[SumOfcurAmount] AS curDifference
FROM (qryExpenseSum INNER JOIN tblCategory ON qryExpenseSum.numCategory =
tblCategory.anuCategoryID) INNER JOIN tblBudget ON (qryExpenseSum.numCategory
= tblBudget.numCategory) AND (qryExpenseSum.numMonth = tblBudget.numMonth);

....but if there was nothing spent in a particular category in a particular
month, it does not show up in the query for that month. I have tried setting
the relationship in the first query to include all records from tblExpense
(with an nz to make it show up as $0 for unused categories), but over the
course of a year all of the categories are used, just not in every month.

Sorry for the long winded explanation, I just wanted to answer as many
questions as I could before they were asked. Thank you.

Adam

 

[Tom Ellison]

Dear Adam:

In your query, please change the first INNER JOIN to read RIGHT JOIN. If
I'm reading this correctly, that's the fix you need.

If you are not accustomed to writing queries in SQL View (and the
unformatted appearance of your examples seems to indicate that they have
been formatted by Access and not by a programmer, then likely you are
working in the Design View most of the time. In that case, there is a line
between the two tables for this JOIN in the Design View. Right click on
that line and look at the properites of that JOIN and you'll see 3 choices
for how to choose the JOIN you need. The alteration I have proposed here is
all laid out for you there, so you can learn from that how to select the
correct type of JOIN needed in the future.

I hope this helps. Please come back if you need any other assistance.

Tom Ellison

Using Access 2003 on Windows XP

I have three tables
tblCategories with just category names and ID numbers
tblExpense with fields for date of expense, amount of expense and which
category the expense is in (related to tblCategory)
tblBudget with fields for year, month, category, and amount budgeted

I have the following query, qryExpenseSum, to sum the expenses for each
month for each category:

SELECT DatePart("yyyy",[dteDate]) AS numYear, DatePart("m",[dteDate]) AS
numMonth, tblExpense.numCategory, Sum(tblExpense.curAmount) AS
SumOfcurAmount
FROM tblExpense
GROUP BY DatePart("yyyy",[dteDate]), DatePart("m",[dteDate]),
tblExpense.numCategory
HAVING (((DatePart("yyyy",[dteDate]))=[forms]![frmHome].[numYear]));

What I would like is to create another query that puts the total amount
spent next to the amount budgeted for each category in each month so that
I
can find the difference and put it in a report. I was almost able to
acheive
that with the following query

SELECT qryExpenseSum.numYear, qryExpenseSum.numMonth,
qryExpenseSum.numCategory, tblCategory.strCategory, tblBudget.curBudget,
qryExpenseSum.SumOfcurAmount, [curBudget]-[SumOfcurAmount] AS
curDifference
FROM (qryExpenseSum INNER JOIN tblCategory ON qryExpenseSum.numCategory =
tblCategory.anuCategoryID) INNER JOIN tblBudget ON
(qryExpenseSum.numCategory
= tblBudget.numCategory) AND (qryExpenseSum.numMonth =
tblBudget.numMonth);

...but if there was nothing spent in a particular category in a particular
month, it does not show up in the query for that month. I have tried
setting
the relationship in the first query to include all records from tblExpense
(with an nz to make it show up as $0 for unused categories), but over the
course of a year all of the categories are used, just not in every month.

Sorry for the long winded explanation, I just wanted to answer as many
questions as I could before they were asked. Thank you.

Adam

 

[Adam]

Tom-
Thanks for the response. I tried you suggestion but got the dreaded
"ambiguous outer join" message. What next?

Adam

P.S. You are spot on with my experience writing in SQL. Thanks in advance
for any help.

Dear Adam:

In your query, please change the first INNER JOIN to read RIGHT JOIN. If
I'm reading this correctly, that's the fix you need.

If you are not accustomed to writing queries in SQL View (and the
unformatted appearance of your examples seems to indicate that they have
been formatted by Access and not by a programmer, then likely you are
working in the Design View most of the time. In that case, there is a line
between the two tables for this JOIN in the Design View. Right click on
that line and look at the properites of that JOIN and you'll see 3 choices
for how to choose the JOIN you need. The alteration I have proposed here is
all laid out for you there, so you can learn from that how to select the
correct type of JOIN needed in the future.

I hope this helps. Please come back if you need any other assistance.

Tom Ellison

Using Access 2003 on Windows XP

I have three tables
tblCategories with just category names and ID numbers
tblExpense with fields for date of expense, amount of expense and which
category the expense is in (related to tblCategory)
tblBudget with fields for year, month, category, and amount budgeted

I have the following query, qryExpenseSum, to sum the expenses for each
month for each category:

SELECT DatePart("yyyy",[dteDate]) AS numYear, DatePart("m",[dteDate]) AS
numMonth, tblExpense.numCategory, Sum(tblExpense.curAmount) AS
SumOfcurAmount
FROM tblExpense
GROUP BY DatePart("yyyy",[dteDate]), DatePart("m",[dteDate]),
tblExpense.numCategory
HAVING (((DatePart("yyyy",[dteDate]))=[forms]![frmHome].[numYear]));

What I would like is to create another query that puts the total amount
spent next to the amount budgeted for each category in each month so that
I
can find the difference and put it in a report. I was almost able to
acheive
that with the following query

SELECT qryExpenseSum.numYear, qryExpenseSum.numMonth,
qryExpenseSum.numCategory, tblCategory.strCategory, tblBudget.curBudget,
qryExpenseSum.SumOfcurAmount, [curBudget]-[SumOfcurAmount] AS
curDifference
FROM (qryExpenseSum INNER JOIN tblCategory ON qryExpenseSum.numCategory =
tblCategory.anuCategoryID) INNER JOIN tblBudget ON
(qryExpenseSum.numCategory
= tblBudget.numCategory) AND (qryExpenseSum.numMonth =
tblBudget.numMonth);

...but if there was nothing spent in a particular category in a particular
month, it does not show up in the query for that month. I have tried
setting
the relationship in the first query to include all records from tblExpense
(with an nz to make it show up as $0 for unused categories), but over the
course of a year all of the categories are used, just not in every month.

Sorry for the long winded explanation, I just wanted to answer as many
questions as I could before they were asked. Thank you.

Adam

 

[Tom Ellison]

Dear Adam:

I don't know what went wrong. I recommend returning to your query in the
INNER JOIN fashion.

Make sure it is running as before.

Follow my previous instructions to change the JOIN type in the Design View.
Read the options and understand them. Choose the one that makes sense. Or
just try one. If it's the wrong one, it won't fix the problem, and you can
try again with the other option. There are only 3. This should suffice,
and get you through this pretty quickly and easily. Try to learn as much as
you can as you do this by studying what the descriptive terms are in that
dialog for selecting your type of JOIN. It will serve you well in the
future, and will instruct you just why there are different types of JOINs,
and when you will need them in the future.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

Tom-
Thanks for the response. I tried you suggestion but got the dreaded
"ambiguous outer join" message. What next?

Adam

P.S. You are spot on with my experience writing in SQL. Thanks in
advance
for any help.

Dear Adam:

In your query, please change the first INNER JOIN to read RIGHT JOIN. If
I'm reading this correctly, that's the fix you need.

If you are not accustomed to writing queries in SQL View (and the
unformatted appearance of your examples seems to indicate that they have
been formatted by Access and not by a programmer, then likely you are
working in the Design View most of the time. In that case, there is a
line
between the two tables for this JOIN in the Design View. Right click on
that line and look at the properites of that JOIN and you'll see 3
choices
for how to choose the JOIN you need. The alteration I have proposed here
is
all laid out for you there, so you can learn from that how to select the
correct type of JOIN needed in the future.

I hope this helps. Please come back if you need any other assistance.

Tom Ellison

Using Access 2003 on Windows XP

I have three tables
tblCategories with just category names and ID numbers
tblExpense with fields for date of expense, amount of expense and which
category the expense is in (related to tblCategory)
tblBudget with fields for year, month, category, and amount budgeted

I have the following query, qryExpenseSum, to sum the expenses for each
month for each category:

SELECT DatePart("yyyy",[dteDate]) AS numYear, DatePart("m",[dteDate])
AS
numMonth, tblExpense.numCategory, Sum(tblExpense.curAmount) AS
SumOfcurAmount
FROM tblExpense
GROUP BY DatePart("yyyy",[dteDate]), DatePart("m",[dteDate]),
tblExpense.numCategory
HAVING (((DatePart("yyyy",[dteDate]))=[forms]![frmHome].[numYear]));

What I would like is to create another query that puts the total amount
spent next to the amount budgeted for each category in each month so
that
I
can find the difference and put it in a report. I was almost able to
acheive
that with the following query

SELECT qryExpenseSum.numYear, qryExpenseSum.numMonth,
qryExpenseSum.numCategory, tblCategory.strCategory,
tblBudget.curBudget,
qryExpenseSum.SumOfcurAmount, [curBudget]-[SumOfcurAmount] AS
curDifference
FROM (qryExpenseSum INNER JOIN tblCategory ON qryExpenseSum.numCategory
=
tblCategory.anuCategoryID) INNER JOIN tblBudget ON
(qryExpenseSum.numCategory
= tblBudget.numCategory) AND (qryExpenseSum.numMonth =
tblBudget.numMonth);

...but if there was nothing spent in a particular category in a
particular
month, it does not show up in the query for that month. I have tried
setting
the relationship in the first query to include all records from
tblExpense
(with an nz to make it show up as $0 for unused categories), but over
the
course of a year all of the categories are used, just not in every
month.

Sorry for the long winded explanation, I just wanted to answer as many
questions as I could before they were asked. Thank you.

Adam