How can I construct a query to track changes in account balances over time?

[Colleyville Alan]

I have some data in a table structured like this:

Date Cust_ID CUSIP Amount
01/31/2005 060208 02507M303 27,061.84
02/28/2005 060208 02507M303 32,049.00

Is there a way to construct a query that will give me the difference by
customer and CUSIP by month?

The real app has over 200,000 records (12 months with 20,000+ entries per
month) and I want to be able to track changes each month by the two fields.
I know how to sum them, group them, append, delete, etc. I even can figure
out how to do this if they were in separate tables, but I'm thinking there
must be a way to do it with the data in one table.

Any ideas?

Thanks.

p.s. not that it matters, but CUSIP is a code to track stocks, bonds, and
mutual funds.

[cclarke]

If by 'difference' you mean the Amount column field values then;

In the query edit;
- Include your table
- Include the Cust_Id, CUSIP and Amount fields
- Create a column with heading MonthDate : Format([Date], "mmyyyy")

Click on the group by button and;
- Group by Cust_Id, CUSIP, MonthDate
- Sum Amount

You might want to sort on 1/more of the grouped-by fields.

Colleyville Alan wrote:

I have some data in a table structured like this:

Date Cust_ID CUSIP Amount
01/31/2005 060208 02507M303 27,061.84 02/28/2005 060208 02507M303 32,049.00
Is there a way to construct a query that will give me the difference by customer and CUSIP by month?

The real app has over 200,000 records (12 months with 20,000+ entries per month) and I want to be able to track changes each month by the two fields. I know how to sum them, group them, append, delete, etc. I even can figure out how to do this if they were in separate tables, but I'm thinking there must be a way to do it with the data in one table.

Any ideas?

Thanks.

p.s. not that it matters, but CUSIP is a code to track stocks, bonds, and mutual funds.

[Colleyville Alan]

<cc*****@slingshot.co.nz> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

If by 'difference' you mean the Amount column field values then;

In the query edit;
- Include your table
- Include the Cust_Id, CUSIP and Amount fields
- Create a column with heading MonthDate : Format([Date], "mmyyyy")

Click on the group by button and;
- Group by Cust_Id, CUSIP, MonthDate
- Sum Amount

You might want to sort on 1/more of the grouped-by fields.

No, by "difference" I mean the change from one month to the next. In the
example I've shown
Date Cust_ID CUSIP Amount
01/31/2005 060208 02507M303 27,061.84
02/28/2005 060208 02507M303 32,049.00

The difference between Jan and Feb balances is an increase of 4,987.16
If I had the data in two separate tables, one with Jan info and the other
with Feb info,
I could link the tables by the Cust_ID and by CUSIP and calculate the
change. The output
would look like this:

CUSIP Cust_ID Date_1 Date_2 Amount_1
Amount_2 Diff
02507M303 060208 01/31/2005 02/28/2005 27,061.84
32,049.00 -4987.16

But how do I do that when both dates are in the same table? I've tried a
crosstab query, but all I managed
to do was to get the amounts by date side-by-side and not calc the
difference.

Cust_ID CUSIP Jan Feb Mar Apr
May Jun Jul Aug Sep Oct Nov Dec
000087 02507M303 27,061.84 32,049.00

I have a bunch of blank months off to the right, but no change between the
two. The query wizard does not help much.

Any ideas on how to solve this?

Thanks.
Alan

I have some data in a table structured like this:

Date Cust_ID CUSIP Amount
01/31/2005 060208 02507M303

27,061.84

02/28/2005 060208 02507M303

32,049.00

Is there a way to construct a query that will give me the difference

by

customer and CUSIP by month?

The real app has over 200,000 records (12 months with 20,000+ entries

per

month) and I want to be able to track changes each month by the two

fields.

I know how to sum them, group them, append, delete, etc. I even can

figure

out how to do this if they were in separate tables, but I'm thinking

there

must be a way to do it with the data in one table.

Any ideas?

Thanks.

p.s. not that it matters, but CUSIP is a code to track stocks, bonds,

and

mutual funds.

[Bob Quintal]

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in
news:37*************@individual.net:

<cc*****@slingshot.co.nz> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

If by 'difference' you mean the Amount column field values
then;

In the query edit;
- Include your table
- Include the Cust_Id, CUSIP and Amount fields
- Create a column with heading MonthDate : Format([Date],
"mmyyyy")

Click on the group by button and;
- Group by Cust_Id, CUSIP, MonthDate
- Sum Amount

You might want to sort on 1/more of the grouped-by fields.

No, by "difference" I mean the change from one month to the
next. In the example I've shown
Date Cust_ID CUSIP
Amount 01/31/2005 060208 02507M303
27,061.84 02/28/2005 060208 02507M303
32,049.00

The difference between Jan and Feb balances is an increase of
4,987.16
If I had the data in two separate tables, one with Jan info
and the other with Feb info,
I could link the tables by the Cust_ID and by CUSIP and
calculate the change. The output
would look like this:

CUSIP Cust_ID Date_1 Date_2
Amount_1 Amount_2 Diff
02507M303 060208 01/31/2005 02/28/2005
27,061.84 32,049.00 -4987.16

But how do I do that when both dates are in the same table?
I've tried a crosstab query, but all I managed
to do was to get the amounts by date side-by-side and not calc
the difference.

Instead of separate tables, you can use separate queries on the
same table and use those in a query. So first build a query that
returns the previous month's balance, and join it to a query of
the current month's balance. It means you have two queries in the
database instead of one, but it's rarely a problem. You can
actually put the subquery as a field definition and only have
one query definition in the database, but it's tricky and
requires a bit of experience with SQL.

--
Bob Quintal

PA is y I've altered my email address.

[Colleyville Alan]

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1108850576.fc04aaadf7959632f709135bbc11eac5@t eranews...

Instead of separate tables, you can use separate queries on the
same table and use those in a query. So first build a query that
returns the previous month's balance, and join it to a query of
the current month's balance. It means you have two queries in the
database instead of one, but it's rarely a problem. You can
actually put the subquery as a field definition and only have
one query definition in the database, but it's tricky and
requires a bit of experience with SQL.

I never knew you could put a saved query in a new query. I always thought
you could only add tables.
Thanks

[Bob Quintal]

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in
news:37*************@individual.net:

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1108850576.fc04aaadf7959632f709135bbc11eac5@t eranews...

Instead of separate tables, you can use separate queries on
the same table and use those in a query. So first build a
query that returns the previous month's balance, and join it
to a query of the current month's balance. It means you have
two queries in the database instead of one, but it's rarely a
problem. You can actually put the subquery as a field
definition and only have one query definition in the
database, but it's tricky and requires a bit of experience
with SQL.

I never knew you could put a saved query in a new query. I
always thought
you could only add tables.
Thanks

Why this escapes so many neophytes is beyond me. As soon as
someone opens the query builder, a dialog menu pops up, with the
tabs "tables", "queries", "both". It seems logical to me that
they are there to be used.

I have one report at work that contains nine layers of queries
back to the tables. It's slow, but it works.

--
Bob Quintal

PA is y I've altered my email address.