How best to attack this problem?

[localtalent]

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

 

[KKilfoil]

Is there more than one client from whom they receive such a list? Assuming no...

Optional: Assuming there COULD be more than two types of transactions, beyond 'cash' and 'credit', make a lookup table called [TransactionTypes] that contains an ID field and a value field. Initially, it will have just two records, one for cash and one for credit.

Make a table called [JustDates] that contains one field, a unique date. Populate this table with a record for every day into the future.

Now work out a means to easily convert your imported file into a table, call it, say [ImportedTransactions], where records will look like:
TransID, Product, Amount, Date, TransactionType

Now make a crosstab query of [JustDates] (you may want to parameterize the date range) and [ImportedTransactions] for each of your desired outputs.

HTH