I think I need to really step back and ask about your data structure.  I think that addressing that will actually make a number of these problems practically resolve themselves.

How are you generating the monthly totals now?  Are you using an OLAP structure?  A CASE structure in SQL?  Or do your tables actually have columns for each month?

You mention that you're linking two very similar tables, and ending up with duplicate records.  The most logical reason is that you have duplicate values in the foreign key field.  The best solution for this is to group on the foreign key, and use summary fields or suppression to only show the values you want.

Creating an average for each line is going to depend heavily on the data structure.  It may be extremely simple.  Or, it may be pretty complicated.

At this point, you should decidedly avoid OLAP or CASE.  Those are fairly advanced topics, and it sounds like you're still working through some of the lower topics.

The foreign key issue is fairly simple.  You created a link (or join) between two tables, in a one-to-many relationship.  For each record on the "many" side, you'll get a row returned.  If you're getting too many rows, that's likely where the problem lies.  Review the data in your tables, and the logic of your join, to see what you can do.


Unfortunately, I'm not able to get to my Crystal right now, but my memory is telling me that tacking an average on the end of the row is going to be tricky at best, and potentially not possible at all.

One option, if you have a fairly static number of months, is to use a series of formulas to create this look.  Especially given the requirement of adding in data from a third table, this may be your best bet.

Let's suppose you want to show the last three entire months on the report.  First, put in a selection criteria for your report that looks something like:

InvoiceDate IN DateAdd(m,-3,DataDate - Day(DataDate)) _TO (DataDate - Day(DataDate)

DataDate is the date on which the report was last refreshed.  Personally, I find this to be better in the long run than CurrentDate.
"DataDate - Day(DataDate)" gives you the last day of the previous month.  This is because the Day function returns the day value of the date (e.g., for February 6, it would return 6).  When you subtract it, it moves it back to one day before the first of the month, which is the end of the previous month.
The "_TO" means that you do not include the first date (as that is the last day of four months ago), but do include the second date.


Create three formulas.  These should be added to your details section.  They should look like:

If Month({MyReport.InvoiceDate}) = Month(DataDate) - 3 //or 2 or 1, for your other two formulas
Then
QTY
Else
0

Create a group based on the Item#.  In the group footer, put the following:
The SUM of your three formulas (this will be your "Jan 07", "Feb 07", "Mar 07" fields)
The SUM of all the records (this will be your "Total" field)
The AVG of all the records (this will be your "Average" field)

Suppress all the detail records.  This should end up giving you the look you want.