Report help: joining a many to one table

[Bennie47250]

Using Crystal Version 7.0 and an Access 97 database.

How should I approach this report design?

We want to show DSO (Days Outstanding) and Optimum DSO. DSO is determined by taking the previous 2 months sales and dividing it by the total amount owed by the customer and doing some other calculations. Optimum DSO is done the same why except you divide by the current amount owed.

The sales dollars are taken from the many table as it is the detailed sales information. I have created a couple of formulas to determine the beginning date and ending date to gather up the sales dollars so that part is done.

The amount owed is taken from the one table.

As we know, when I join the two tables, by the customer account number, I collect to much amount owed data.

What would be the beat way to approach this issue? I have considered building a new table in Access that would take the sales data and total it so the sales data would be in a “one” table too but not sure I could recreate the previous 2 months formula in Access. I also have considered having a report showing the sales data with a sub report of the amount owed and then passing the needed information between the two reports but not exactly know how to do to this.

Thanks in advance for your suggestions.

Bennie

 

[synapsevampire]

It sounds like you've thought this through.

My advice is to always let the database do the work, it's faster and more flexible at manipulating data.

Access can do any calculations that Crystal can, so don't get scared off at that level.

If you elect to use Crystal, supply example data and expected output, otherwise, let Access manipulate data, and Crystal demonstrate it.

-k