Financial Comparison

I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions for a
particular date in one column and the month-to-date results in the next,
based upon grouping the Charge field. So, it should look something like this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two queries.

Use a Totals query to calculate the total for each date + charge code, and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The subquery will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to rename
it. Date is a reserved word in VBA (for the system date), so sooner or later
it will catch you out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" Steven wrote in message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions for
a
particular date in one column and the month-to-date results in the next,
based upon grouping the Charge field. So, it should look something like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Allen;

Thanks. I take it that it will not only give me a MTD but also a running
balance as well.

As for the Date issue, thanks for the heads up.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code, and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The subquery will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to rename
it. Date is a reserved word in VBA (for the system date), so sooner or later
it will catch you out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" Steven wrote in message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions for
a
particular date in one column and the month-to-date results in the next,
based upon grouping the Charge field. So, it should look something like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Allen;

I have tried this out however it keeps giving me an error indicating Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer to
Table1, do I simply have to refer to qryunionCombined OR do I have to go all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together thus far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from qryCombinedResults as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid = qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a valid field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code, and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The subquery will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to rename
it. Date is a reserved word in VBA (for the system date), so sooner or later
it will catch you out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" Steven wrote in message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions for
a
particular date in one column and the month-to-date results in the next,
based upon grouping the Charge field. So, it should look something like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Hi Steven

I'm not sure I ever tried this in a UNION query, but in theory it should
work, and I would expect it to match the field name of the first table in
the UNION statement (which could be different from the others).

Just to get this going, are you able to get a result if the subquery's WHERE
clause matches just one field?

If that works, is there any chance that the UNION query is unable to make
the match because it misunderstands the data type? (The clue would be a
field that left-aligns like text in the query output, when you expect it to
right-align like a number.)

Of course, all this assumes that Dept is the field name, and not DeptID or
some such.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" wrote in message
...
Allen;

I have tried this out however it keeps giving me an error indicating Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer to
Table1, do I simply have to refer to qryunionCombined OR do I have to go
all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together thus
far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from qryCombinedResults as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid = qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a valid
field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code,
and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The subquery
will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to rename
it. Date is a reserved word in VBA (for the system date), so sooner or
later
it will catch you out.


"Steven Cheng" Steven wrote in message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions
for
a
particular date in one column and the month-to-date results in the
next,
based upon grouping the Charge field. So, it should look something
like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Allen;

Thanks again for your help.

I have checked my data types and they are the same. Actually, they should
be considering that I used a make table query using the select query, which
both are a part of the union query. Basically, the union query is combining
a select query (consisting of an under lining pivot query) and a table, that
is appended to by the select query. Hope that makes sense as I had trouble
writing this paragraph out.

I have tried to remove the WHERE conditions one by one and it only works
when there are no conditions in place. However, I have also tried to remove
the alias Dupe to see if it will run with the conditions in place and it
does. But not the way I wanted it to show.

I don't know if this matters however I am running Access 97 and while
reading the Help notes on subquery, it indicates that a subquery's SELECT
statement can't define a union or crosstab query. Does this imply that I
cannot create a union/cross tab query within a subquery statement OR that I
cannot use a union/crosstab query within itself. Very confusing statement to
me.

I have tried to run the subquery on the table and query BEFORE the union
takes place and I was surprised. The subquery worked fine on the table
alone, however, when I tried it on the query (the one with crosstab), it
didn't like it. Maybe it is "too much to handle".

Any thoughts on this?

"Allen Browne" wrote:

Hi Steven

I'm not sure I ever tried this in a UNION query, but in theory it should
work, and I would expect it to match the field name of the first table in
the UNION statement (which could be different from the others).

Just to get this going, are you able to get a result if the subquery's WHERE
clause matches just one field?

If that works, is there any chance that the UNION query is unable to make
the match because it misunderstands the data type? (The clue would be a
field that left-aligns like text in the query output, when you expect it to
right-align like a number.)

Of course, all this assumes that Dept is the field name, and not DeptID or
some such.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" wrote in message
...
Allen;

I have tried this out however it keeps giving me an error indicating Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer to
Table1, do I simply have to refer to qryunionCombined OR do I have to go
all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together thus
far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from qryCombinedResults as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid = qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a valid
field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code,
and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The subquery
will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to rename
it. Date is a reserved word in VBA (for the system date), so sooner or
later
it will catch you out.


"Steven Cheng" Steven wrote in message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions
for
a
particular date in one column and the month-to-date results in the
next,
based upon grouping the Charge field. So, it should look something
like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Allen;

I finally got it.

I have followed something that was in the Help option about defining the
column headings for the crosstab query that I have and it works. It is a
compromise as the Field headings, could change however given that for my
application it is once in a blue moon, I can make the change.

Thanks again.

"Allen Browne" wrote:

Hi Steven

I'm not sure I ever tried this in a UNION query, but in theory it should
work, and I would expect it to match the field name of the first table in
the UNION statement (which could be different from the others).

Just to get this going, are you able to get a result if the subquery's WHERE
clause matches just one field?

If that works, is there any chance that the UNION query is unable to make
the match because it misunderstands the data type? (The clue would be a
field that left-aligns like text in the query output, when you expect it to
right-align like a number.)

Of course, all this assumes that Dept is the field name, and not DeptID or
some such.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" wrote in message
...
Allen;

I have tried this out however it keeps giving me an error indicating Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer to
Table1, do I simply have to refer to qryunionCombined OR do I have to go
all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together thus
far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from qryCombinedResults as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid = qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a valid
field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code,
and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The subquery
will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to rename
it. Date is a reserved word in VBA (for the system date), so sooner or
later
it will catch you out.


"Steven Cheng" Steven wrote in message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all transactions
for
a
particular date in one column and the month-to-date results in the
next,
based upon grouping the Charge field. So, it should look something
like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Hi Steven

When Help says:
A subquery's SELECT statement can't define a union
or crosstab query.
I think it means that you cannot use the result generated from a subquery as
the source for UNION query or crosstab query, so that's going the opposite
direction from the problem you have.

I just tried this in Access 97, combining the records form a Client table
and a Customer table that both have a meaningless Amount field, and then
collecting an AmountSoFar field that totals the Amount up to that primary
key value. The output has no meaning, but it did work.

FWIW, the UNION query was:
SELECT tClient.ClientNum AS ID,
tClient.Surname,
tClient.FirstName,
tClient.Amount
FROM tClient
WHERE ((tClient.IsCorporate = False) AND (tClient.ClientNum 100))
UNION ALL
SELECT tblCustomers.CustomerID AS ID,
tblCustomers.Surname,
tblCustomers.FirstName,
tblCustomers.Amount
FROM tblCustomers
ORDER BY ID;

And this query accumulated a total-to-date type of result:
SELECT qryCombinedResults.ID,
qryCombinedResults.Surname,
qryCombinedResults.FirstName,
qryCombinedResults.Amount,
(SELECT Sum(Dupe.Amount) AS MTD
FROM qryCombinedResults AS Dupe
WHERE (Dupe.ID = qryCombinedResults.ID) ) AS AmountSoFar
FROM qryCombinedResults
ORDER BY qryCombinedResults.ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" Steven wrote in message
...
Allen;

Thanks again for your help.

I have checked my data types and they are the same. Actually, they should
be considering that I used a make table query using the select query,
which
both are a part of the union query. Basically, the union query is
combining
a select query (consisting of an under lining pivot query) and a table,
that
is appended to by the select query. Hope that makes sense as I had
trouble
writing this paragraph out.

I have tried to remove the WHERE conditions one by one and it only works
when there are no conditions in place. However, I have also tried to
remove
the alias Dupe to see if it will run with the conditions in place and it
does. But not the way I wanted it to show.

I don't know if this matters however I am running Access 97 and while
reading the Help notes on subquery, it indicates that a subquery's SELECT
statement can't define a union or crosstab query. Does this imply that I
cannot create a union/cross tab query within a subquery statement OR that
I
cannot use a union/crosstab query within itself. Very confusing statement
to
me.

I have tried to run the subquery on the table and query BEFORE the union
takes place and I was surprised. The subquery worked fine on the table
alone, however, when I tried it on the query (the one with crosstab), it
didn't like it. Maybe it is "too much to handle".

Any thoughts on this?

"Allen Browne" wrote:

Hi Steven

I'm not sure I ever tried this in a UNION query, but in theory it should
work, and I would expect it to match the field name of the first table in
the UNION statement (which could be different from the others).

Just to get this going, are you able to get a result if the subquery's
WHERE
clause matches just one field?

If that works, is there any chance that the UNION query is unable to make
the match because it misunderstands the data type? (The clue would be a
field that left-aligns like text in the query output, when you expect it
to
right-align like a number.)

Of course, all this assumes that Dept is the field name, and not DeptID
or
some such.


"Steven Cheng" wrote in message
...
Allen;

I have tried this out however it keeps giving me an error indicating
Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer to
Table1, do I simply have to refer to qryunionCombined OR do I have to
go
all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together
thus
far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from qryCombinedResults
as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid =
qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a valid
field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code,
and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The
subquery
will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to
rename
it. Date is a reserved word in VBA (for the system date), so sooner or
later
it will catch you out.


"Steven Cheng" Steven wrote in
message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all
transactions
for
a
particular date in one column and the month-to-date results in the
next,
based upon grouping the Charge field. So, it should look something
like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

Allen;

I guess I had posted my message too soon. I started to add the WHERE
clauses back in however and the query is running, however, seems to stall
causing Access to hang. Any ideas other the UNION ALL statement to speed
things up, any other suggestions?

"Allen Browne" wrote:

Hi Steven

When Help says:
A subquery's SELECT statement can't define a union
or crosstab query.
I think it means that you cannot use the result generated from a subquery as
the source for UNION query or crosstab query, so that's going the opposite
direction from the problem you have.

I just tried this in Access 97, combining the records form a Client table
and a Customer table that both have a meaningless Amount field, and then
collecting an AmountSoFar field that totals the Amount up to that primary
key value. The output has no meaning, but it did work.

FWIW, the UNION query was:
SELECT tClient.ClientNum AS ID,
tClient.Surname,
tClient.FirstName,
tClient.Amount
FROM tClient
WHERE ((tClient.IsCorporate = False) AND (tClient.ClientNum 100))
UNION ALL
SELECT tblCustomers.CustomerID AS ID,
tblCustomers.Surname,
tblCustomers.FirstName,
tblCustomers.Amount
FROM tblCustomers
ORDER BY ID;

And this query accumulated a total-to-date type of result:
SELECT qryCombinedResults.ID,
qryCombinedResults.Surname,
qryCombinedResults.FirstName,
qryCombinedResults.Amount,
(SELECT Sum(Dupe.Amount) AS MTD
FROM qryCombinedResults AS Dupe
WHERE (Dupe.ID = qryCombinedResults.ID) ) AS AmountSoFar
FROM qryCombinedResults
ORDER BY qryCombinedResults.ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" Steven wrote in message
...
Allen;

Thanks again for your help.

I have checked my data types and they are the same. Actually, they should
be considering that I used a make table query using the select query,
which
both are a part of the union query. Basically, the union query is
combining
a select query (consisting of an under lining pivot query) and a table,
that
is appended to by the select query. Hope that makes sense as I had
trouble
writing this paragraph out.

I have tried to remove the WHERE conditions one by one and it only works
when there are no conditions in place. However, I have also tried to
remove
the alias Dupe to see if it will run with the conditions in place and it
does. But not the way I wanted it to show.

I don't know if this matters however I am running Access 97 and while
reading the Help notes on subquery, it indicates that a subquery's SELECT
statement can't define a union or crosstab query. Does this imply that I
cannot create a union/cross tab query within a subquery statement OR that
I
cannot use a union/crosstab query within itself. Very confusing statement
to
me.

I have tried to run the subquery on the table and query BEFORE the union
takes place and I was surprised. The subquery worked fine on the table
alone, however, when I tried it on the query (the one with crosstab), it
didn't like it. Maybe it is "too much to handle".

Any thoughts on this?

"Allen Browne" wrote:

Hi Steven

I'm not sure I ever tried this in a UNION query, but in theory it should
work, and I would expect it to match the field name of the first table in
the UNION statement (which could be different from the others).

Just to get this going, are you able to get a result if the subquery's
WHERE
clause matches just one field?

If that works, is there any chance that the UNION query is unable to make
the match because it misunderstands the data type? (The clue would be a
field that left-aligns like text in the query output, when you expect it
to
right-align like a number.)

Of course, all this assumes that Dept is the field name, and not DeptID
or
some such.


"Steven Cheng" wrote in message
...
Allen;

I have tried this out however it keeps giving me an error indicating
Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer to
Table1, do I simply have to refer to qryunionCombined OR do I have to
go
all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together
thus
far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from qryCombinedResults
as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid =
qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a valid
field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge code,
and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you the
month-to-date figure. In the Total row, choose Expression. The
subquery
will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to
rename
it. Date is a reserved word in VBA (for the system date), so sooner or
later
it will catch you out.


"Steven Cheng" Steven wrote in
message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all
transactions
for
a
particular date in one column and the month-to-date results in the
next,
based upon grouping the Charge field. So, it should look something
like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create two
queries.

How about normalizing the data design, so you have all the records together,
and don't need a UNION query? gdr

I'm not sure what else to suggest. Guess you could write the data into a
temp table with indexes, so it can retrieve it efficiently.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" wrote in message
...
Allen;

I guess I had posted my message too soon. I started to add the WHERE
clauses back in however and the query is running, however, seems to stall
causing Access to hang. Any ideas other the UNION ALL statement to speed
things up, any other suggestions?

"Allen Browne" wrote:

Hi Steven

When Help says:
A subquery's SELECT statement can't define a union
or crosstab query.
I think it means that you cannot use the result generated from a subquery
as
the source for UNION query or crosstab query, so that's going the
opposite
direction from the problem you have.

I just tried this in Access 97, combining the records form a Client table
and a Customer table that both have a meaningless Amount field, and then
collecting an AmountSoFar field that totals the Amount up to that primary
key value. The output has no meaning, but it did work.

FWIW, the UNION query was:
SELECT tClient.ClientNum AS ID,
tClient.Surname,
tClient.FirstName,
tClient.Amount
FROM tClient
WHERE ((tClient.IsCorporate = False) AND (tClient.ClientNum 100))
UNION ALL
SELECT tblCustomers.CustomerID AS ID,
tblCustomers.Surname,
tblCustomers.FirstName,
tblCustomers.Amount
FROM tblCustomers
ORDER BY ID;

And this query accumulated a total-to-date type of result:
SELECT qryCombinedResults.ID,
qryCombinedResults.Surname,
qryCombinedResults.FirstName,
qryCombinedResults.Amount,
(SELECT Sum(Dupe.Amount) AS MTD
FROM qryCombinedResults AS Dupe
WHERE (Dupe.ID = qryCombinedResults.ID) ) AS AmountSoFar
FROM qryCombinedResults
ORDER BY qryCombinedResults.ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven Cheng" Steven wrote in message
...
Allen;

Thanks again for your help.

I have checked my data types and they are the same. Actually, they
should
be considering that I used a make table query using the select query,
which
both are a part of the union query. Basically, the union query is
combining
a select query (consisting of an under lining pivot query) and a table,
that
is appended to by the select query. Hope that makes sense as I had
trouble
writing this paragraph out.

I have tried to remove the WHERE conditions one by one and it only
works
when there are no conditions in place. However, I have also tried to
remove
the alias Dupe to see if it will run with the conditions in place and
it
does. But not the way I wanted it to show.

I don't know if this matters however I am running Access 97 and while
reading the Help notes on subquery, it indicates that a subquery's
SELECT
statement can't define a union or crosstab query. Does this imply that
I
cannot create a union/cross tab query within a subquery statement OR
that
I
cannot use a union/crosstab query within itself. Very confusing
statement
to
me.

I have tried to run the subquery on the table and query BEFORE the
union
takes place and I was surprised. The subquery worked fine on the table
alone, however, when I tried it on the query (the one with crosstab),
it
didn't like it. Maybe it is "too much to handle".

Any thoughts on this?

"Allen Browne" wrote:

Hi Steven

I'm not sure I ever tried this in a UNION query, but in theory it
should
work, and I would expect it to match the field name of the first table
in
the UNION statement (which could be different from the others).

Just to get this going, are you able to get a result if the subquery's
WHERE
clause matches just one field?

If that works, is there any chance that the UNION query is unable to
make
the match because it misunderstands the data type? (The clue would be
a
field that left-aligns like text in the query output, when you expect
it
to
right-align like a number.)

Of course, all this assumes that Dept is the field name, and not
DeptID
or
some such.


"Steven Cheng" wrote in
message
...
Allen;

I have tried this out however it keeps giving me an error indicating
Jet
doesn't recognize one of my variables in the WHERE statement.

I actually have to do this query on a union query, so when you refer
to
Table1, do I simply have to refer to qryunionCombined OR do I have
to
go
all
the way back to the tables that qryunionCombined refers to.

The following is the subquery statement that I have parsed together
thus
far:

MTD_RegHours: (select sum(dupe.reghours) as MTD from
qryCombinedResults
as
Dupe where (dupe.dept = qryCombinedResults.Dept) and (dupe.job =
qrycombinedresults.job) and (dupe.Batchid =
qryCombinedResults.batchid))

and qryCombinedResults is the union query. The Jet error message is
indicating that it doesn't recognize qryCombinedResults.Dept as a
valid
field
name.

"Allen Browne" wrote:

Use a Totals query to calculate the total for each date + charge
code,
and a
subquery to calculate the month-to-date figure.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Date and Charge fields into the grid.
Accept Group By under these fields.

4. Drag the Amount field into the grid.
In the Total row, choose Sum.

5. In a fresh column in th Field row, enter a subquery to give you
the
month-to-date figure. In the Total row, choose Expression. The
subquery
will
be something like this:

MonthToDate: (SELECT Sum(Dupe.Amount) AS MTD
FROM Table1 AS Dupe
WHERE (Dupe.Charge = Table1.Charge)
AND (Dupe.Date Between
DateSerial(Year(Table1.Date), Month(Table1.Date), 1)
And Table1.Date) )


BTW, if you really do have a field named "Date", you might want to
rename
it. Date is a reserved word in VBA (for the system date), so sooner
or
later
it will catch you out.


"Steven Cheng" Steven wrote in
message
...
I have a table that contains the following fields:

Date, BatchID, Charge, Description, Amount

I want to create a comparative report show the sum of all
transactions
for
a
particular date in one column and the month-to-date results in
the
next,
based upon grouping the Charge field. So, it should look
something
like
this:

Date ChargeCode Amount
01/01/04 A 160.00
01/01/04 B 125.00

I believe that my tables are normalized. Do I have two create
two
queries.