Combine rows/records based on field

[Former Member]

Hello,

I have what is probably a very simple problem, but I am new at this, and couldn't find the answer. I have a report on a manufacturing process which retrieves production status, with fields customer_name, PO_number, due_date, order_qty and mfg_qty, problem is the mfg_qty may be based on several job order checkouts, so my report will have several rows for some POs with duplication in all but the mfg_qty field, such as:

Customer PO DueDate Qty Mfg

MyCustomer PO1234 11/01/2008 150 50

MyCustomer PO1234 11/01/2008 150 75

MyCustomer PO1234 11/01/2008 150 25

What I would like to do is combine these rows into 1 while creating a sum of mfg_qty in the final column. I hope I have explained this well enough, and someone can enlighten me.

Regards,

Kevin

[Former Member · Accepted solution]

sounds like you want the report to display the

customer_name,

there associated PO_numbers

the due_date of each PO by customer?

the order_qty of each PO by customer?

and mfg_qty of each PO by customer?

if that is correct

create a group by customer ID, you want this to be unique

create a group by PO number

place the associated fields for the po due_date, order_qty and mfg_qty

then use manual running totals to total the mfg qty by the po and by customer.

[Former Member]

Hi Kevin,

This can be done in two ways :

1. You write a SQL statement in Add Command like ..

Select customer_name, PO_number, due_date, order_qty sum(mfg_qty) As Mfg_Qty from TableName

Group by customer_name, PO_number, due_date, order_qty

This will sum up all your mfg_qty fields and pull the date into our report.

2. Pull all records into your report and group on Po_number and insert the summary of your mft_qty in group footer.

Hope this will help you,

Thanks,

Sastry