Merging two tables onto one report

[Former Member]

Hi there

I am having some fun with Crystal Reports 2008 and I would like to ask for some assistance.

I have two tables of data that I would like to summarise into one table. The desired output is the following:

ItemNumber, ItemDescription, QTY, Price, Average Selling Price

I have a table showing the stock details for Items:

ItemNumber, ItemDescription, QTY, Price.

The second table shows the history of items that have been sold, and I wish to take the average price when sold:

InvoiceNumber, ItemNumber, Price

What would the best method be for creating this report showing just single lines for each ItemCode?

Thank you in advance of your help.

Regards

Dave Ainsworth

[Former Member · Accepted solution]

Hi Daivid,

Create the report using the two tables, and equal join them based on the item number.

Then, group by the item number in the item ID in the second table (the one that contains the details that you're interested in displaying, not the values you want to average)

The detail line will then be each line item in the first table you described. You would then want to use a summary function to give you the average.

Put all of your 'detail' fields into the group headder, or footer (your choice) add the average field into the mix, supress the detail line to get rid of the extranious lines, and details you don't need, and voila...

Bob's yer uncle.