Financial Comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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;

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;

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.
 
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;

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;

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.
 
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;

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?
 
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;

I really wish that I could consolidate/normalize these tables, however, I am
not really familiar with our SQL server, where a part of the data is coming
from and its only saving it temporarily.

However, the table that I have locally, which is being merged with the query
from the server, should be indexed which I haven't done. Thanks for that tip.

Steven
 
What is <gdr>?

Allen Browne said:
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.
Click to expand...
 
Grin, Duck, and Run.

It's what you do when you make a suggestion someone might thump you for.
:-) Kinda like an emoticon, because people can't see your face when you
say these things.
 
Thanks. I will use it myself.

Allen Browne said:
Grin, Duck, and Run.

It's what you do when you make a suggestion someone might thump you for.
:-) Kinda like an emoticon, because people can't see your face when you
say these things.
Click to expand...
 
Back
Top