Selecting Amounts by Month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tim - 06 Apr 2006 14:58 GMT

I am using an Access database that contains the following fields:

ClientNumber, LastPaymentDate, LastPaymentAmount, 2ndLastPaymentDate,
2ndLastPaymentAmount

The database contains a history of the last 12 payment and amounts
regardless of what dates on which the payments where made. I need a query
that will give me the following information from these records:

ClientNumber, Payment Amounts made in March, Payment Amounts made in
February, Payment Amounts made in January, Payment Amounts made in December

Any help is greatly appreciated

Tim
Access 2000

Reply to this Message

Rick B - 06 Apr 2006 15:08 GMT

Your structure is flawed. If your field contains data (last, next to last,
etc.) then you probably have a flawed structure.

You should have two or more tables. One to store the Client data (number,
name, etc.) and one to store the transactions. (ClientNumber, PaymentDate,
PaymentAmount). The Client table would have one entry for each client. the
transaction table would have one entry for each payment. It could have one
entry for a new customer, or many for an older client.

If you normalize your data, then you can easily pull all "transactions" for
a given month. Or you can group and sort by month.

Signature

Rick B

>I am using an Access database that contains the following fields:
>
[quoted text clipped - 13 lines]
> Tim
> Access 2000

Reply to this Message

tim - 06 Apr 2006 19:04 GMT

That what I suspected.

That you for your reply

Tim

> Your structure is flawed. If your field contains data (last, next to last,
> etc.) then you probably have a flawed structure.
[quoted text clipped - 25 lines]
> > Tim
> > Access 2000

Reply to this Message