SUBQUERY BLUES

[Glint]

Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form.

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

 

[louisjohnphillips]

Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
Sum( ItemAmount ) AS Balance,
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.

 

[Glint]

Coooooooooool, Louis.
Real simple. This part did the trick:
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
Thanks a lot. I hope you are still around while I test other parts of the
query.
--
Glint

Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
Sum( ItemAmount ) AS Balance,
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.

 

[Glint]

Sorry to bother you again, Louisjohnphillips, but the query DOES APPEAR to
work, and now I am confused: TDate was not selected as a field, yet the query
seems to be comparing it with the form CashBook Starting date. Why is that?
--
Glint

Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
Sum( ItemAmount ) AS Balance,
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.

 

[louisjohnphillips]

Sorry to bother you again, Louisjohnphillips, but the query DOES APPEAR to
work, and now I am confused: TDate was not selected as a field, yet the query
seems to be comparing it with the form CashBook Starting date. Why is that?
--
Glint

Hi All,
I have a query that goes like this:
SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;
This returns the totals for all records for each item in the Expenditure
column.
Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:
SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;
This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..
The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
             Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
             Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
             Sum( ItemAmount ) AS Balance,
             Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.- Hide quoted text -

- Show quoted text -

This must mean our definitions of "SELECTED" differ.

In the SELECT statement, I listed Tdate as part of an iif() function
formula. It works and there is no trick here. Zero multiplied by any
number equals Zero. One times any multiplier equals the multiplier.

 

[Glint]

While you are still there, can you please show me how I can use a WHERE
clasue in the field?
--
Glint

Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
Sum( ItemAmount ) AS Balance,
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.

 

[Glint]

Thanks again. I had assumed that I must drag a field into the grid before I
could make reference to it.
Please show me how to use a WHERE clause, like if I want to select between 2
dates or use other criteria as well.
--
Glint

Sorry to bother you again, Louisjohnphillips, but the query DOES APPEAR to
work, and now I am confused: TDate was not selected as a field, yet the query
seems to be comparing it with the form CashBook Starting date. Why is that?
--
Glint

Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
Sum( ItemAmount ) AS Balance,
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.- Hide quoted text -

- Show quoted text -

This must mean our definitions of "SELECTED" differ.

In the SELECT statement, I listed Tdate as part of an iif() function
formula. It works and there is no trick here. Zero multiplied by any
number equals Zero. One times any multiplier equals the multiplier.

 

[louisjohnphillips]

Thanks again. I had assumed that I must drag a field into the grid beforeI
could make reference to it.
Please show me how to use a WHERE clause, like if I want to select between 2
dates or use other criteria as well.
--
Glint

Sorry to bother you again, Louisjohnphillips, but the query DOES APPEAR to
work, and now I am confused: TDate was not selected as a field, yet the query
seems to be comparing it with the form CashBook Starting date. Why isthat?
--
Glint
:
Hi All,
I have a query that goes like this:
SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;
This returns the totals for all records for each item in the Expenditure
column.
Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column couldbe like
this:
SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;
This returns totals for each item in the Summary column (which isequal to
the Expenditure column above) depending on the dates of the CashBook form..
The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.
--
Glint
My version of Access Help states:
"The Sum function ignores records that contain Null fields."
If that is true for your version, the query could be simplified to
read.
SELECT Expenditure,
             Sum( IIf( ItemAmount <0,0, ItemAmount ))AS Income,
             Sum( IIf( ItemAmount >0,0, ItemAmount ))AS Expense,
             Sum( ItemAmount ) AS Balance,
             Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;
I'm hoping the [Forms]![CashBook]![Starting] value will be comparedto
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.- Hide quoted text -
- Show quoted text -

This must mean our definitions of "SELECTED" differ.

In the SELECT statement, I listed Tdate as part of an iif() function
formula.  It works and there is no trick here.  Zero multiplied by any
number equals Zero.  One times any multiplier equals the multiplier.-Hide quoted text -

- Show quoted text -

Yikes! That's where my logic breaks down.

If you cannot use:

Sum( IIF( Tdate >=[Forms]![CashBook]![Starting] and
Tdate <=[Forms]![CashBook]![Ending] , 1, 0)
*
ItemAmount ) as Income1

Then you must create a secondary query based on vBalances.

SELECT A.Expenditure,
Sum( IIf( A.[ItemAmount] < 0, 0, A.[ItemAmount] )) AS Income,
Sum( IIf( A.[ItemAmount] > 0, 0, A.[ItemAmount] )) AS
Expense,
Sum( A.[ItemAmount] ) AS Balance,
B.Income1
FROM ExpenseItems as A
LEFT JOIN
( SELECT Summary, Sum( ItemAmount ) AS Income1
FROM vBalances
WHERE TDate >=[Forms]![CashBook]![Starting]
AND TDate <=[Forms]![CashBook]![Ending] )
GROUP BY Summary ) as B
ON A.ExpenditureID = B.Summary
GROUP BY A.Expenditure;

 

[Glint]

You are my hero! Thanks.
--
Glint

Thanks again. I had assumed that I must drag a field into the grid before I
could make reference to it.
Please show me how to use a WHERE clause, like if I want to select between 2
dates or use other criteria as well.
--
Glint

Sorry to bother you again, Louisjohnphillips, but the query DOES APPEAR to
work, and now I am confused: TDate was not selected as a field, yet the query
seems to be comparing it with the form CashBook Starting date. Why is that?

:
Hi All,
I have a query that goes like this:

SELECT ExpenseItems.Expenditure, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]<0,0,[ItemAmount])) AS Income, Sum(IIf(IsNull([ItemAmount]) Or
[ItemAmount]>0,0,[ItemAmount])) AS Expense, Sum(Nz([ItemAmount],0)) AS Balance
FROM ExpenseItems LEFT JOIN vBalances ON ExpenseItems.ExpenditureID =
vBalances.Summary
GROUP BY ExpenseItems.Expenditure;

This returns the totals for all records for each item in the Expenditure
column.

Now I want to add another income column that returs only select records
based on the dates of an opened form. The new Income Column could be like
this:

SELECT vBalances.Summary, Sum(vBalances.ItemAmount) AS Income1
FROM vBalances
WHERE (((vBalances.TDate)<=[Forms]![CashBook]![Starting]))
GROUP BY vBalances.Summary;

This returns totals for each item in the Summary column (which is equal to
the Expenditure column above) depending on the dates of the CashBook form..

The problems is how to incorporate the Income1 column into the first query.
I have not been able to get the syntax of the subquery right.

My version of Access Help states:

"The Sum function ignores records that contain Null fields."

If that is true for your version, the query could be simplified to
read.

SELECT Expenditure,
Sum( IIf( ItemAmount <0,0, ItemAmount )) AS Income,
Sum( IIf( ItemAmount >0,0, ItemAmount )) AS Expense,
Sum( ItemAmount ) AS Balance,
Sum( IIF( Tdate <=[Forms]![CashBook]![Starting], 1, 0) *
ItemAmount ) as Income1
FROM ExpenseItems LEFT JOIN vBalances ON ExpenditureID = Summary
GROUP BY Expenditure;

I'm hoping the [Forms]![CashBook]![Starting] value will be compared to
Tdate.
If it is less than Tdate, the ItemAmount will be included in the
Income1 sum.- Hide quoted text -

- Show quoted text -

This must mean our definitions of "SELECTED" differ.

In the SELECT statement, I listed Tdate as part of an iif() function
formula. It works and there is no trick here. Zero multiplied by any
number equals Zero. One times any multiplier equals the multiplier.- Hide quoted text -

- Show quoted text -

Yikes! That's where my logic breaks down.

If you cannot use:

Sum( IIF( Tdate >=[Forms]![CashBook]![Starting] and
Tdate <=[Forms]![CashBook]![Ending] , 1, 0)
*
ItemAmount ) as Income1

Then you must create a secondary query based on vBalances.

SELECT A.Expenditure,
Sum( IIf( A.[ItemAmount] < 0, 0, A.[ItemAmount] )) AS Income,
Sum( IIf( A.[ItemAmount] > 0, 0, A.[ItemAmount] )) AS
Expense,
Sum( A.[ItemAmount] ) AS Balance,
B.Income1
FROM ExpenseItems as A
LEFT JOIN
( SELECT Summary, Sum( ItemAmount ) AS Income1
FROM vBalances
WHERE TDate >=[Forms]![CashBook]![Starting]
AND TDate <=[Forms]![CashBook]![Ending] )
GROUP BY Summary ) as B
ON A.ExpenditureID = B.Summary
GROUP BY A.Expenditure;