How best to attack this problem? Sorting and summing transactions by date

[Justin]

We currently have a solution to this coded in VBA in Excel, but the
255 column limitation, general slowness, and instability of Excel are
rapidly becoming problems. Access has been suggested as a good
user-friendly solution.

The situation is this: every day we get an Excel list (or comma
delimited, or XML, however we specify) of payments for different
products on different days, going out to 2035 or so.

For example, assume today is December 1, 2004. Product A has a
purchase of $50 on December 1, January 1, and February 1. Product B
has a purchase of $75 on December 1, March 1, and a special purchase
(say on credit instead of cash, noted in another column on the input
sheet) on June 1.

I need to output one sheet that has, across the top, all products and
down the side all dates to show the purchases--like this:

Prod A cash | Prod A credit | Prod B cash | Prod B credit
12/1 $50 $0 $0 $75
12/2 $0 $0 $0 $0
12/3 $0 $0 $0 $0
12/4 $0 $0 $0 $0
....


a sheet that shows every day out to the end that I have $0 cash
purchases except for Dec 1, which has $125, Jan 1, which has $50,
February 1, which has $50, and March 1 which has $75. Credit
purchases are $0 except for June 1, which has $75, like this:

Cash Credit
12/1 $125 $0
12/2 $0 $0
12/3 $0 $0

Is the best solution to use queries, VB, or something in between? We
aren't tied to Access either, but I'm only a temporary employee and
I'd like to leave them with something maintainable. I'm looking more
for methods to attack the problem and database design guidelines than
actual code.

Much appreciated,
-Justin

[Carlos Nunes-Ueno]

What you're looking for is a crosstab query in Access. Read up in
Access Help about them, they're exactly what you're looking for.
There's also a wizard that will help you set it up. Play around with
that a little bit.

The nice thing about the crosstab query option is that once it's set up
you shouldn't have to mess with it any more.

Hope that helps,

Carlos

Justin wrote:

We currently have a solution to this coded in VBA in Excel, but the
255 column limitation, general slowness, and instability of Excel are
rapidly becoming problems. Access has been suggested as a good
user-friendly solution.

The situation is this: every day we get an Excel list (or comma
delimited, or XML, however we specify) of payments for different
products on different days, going out to 2035 or so.

For example, assume today is December 1, 2004. Product A has a
purchase of $50 on December 1, January 1, and February 1. Product B
has a purchase of $75 on December 1, March 1, and a special purchase
(say on credit instead of cash, noted in another column on the input
sheet) on June 1.

I need to output one sheet that has, across the top, all products and
down the side all dates to show the purchases--like this:

Prod A cash | Prod A credit | Prod B cash | Prod B credit
12/1 $50 $0 $0 $75
12/2 $0 $0 $0 $0
12/3 $0 $0 $0 $0
12/4 $0 $0 $0 $0
...


a sheet that shows every day out to the end that I have $0 cash
purchases except for Dec 1, which has $125, Jan 1, which has $50,
February 1, which has $50, and March 1 which has $75. Credit
purchases are $0 except for June 1, which has $75, like this:

Cash Credit
12/1 $125 $0
12/2 $0 $0
12/3 $0 $0

Is the best solution to use queries, VB, or something in between? We
aren't tied to Access either, but I'm only a temporary employee and
I'd like to leave them with something maintainable. I'm looking more
for methods to attack the problem and database design guidelines than
actual code.

Much appreciated,
-Justin