Remove duplicates

[dragon78]

Hi All.. make sure is this the right category for my problem:

Inside my db, tbl_invoice_items i have:

prod_code | monthOrder | invoiceNum | quantity| remarks

111 | Jan | ref001 | 50 |

112 | Jan | ref001 | 20 |

111 | Feb | ref002 | 10 |

113 | Feb | ref003 | 40 |

... and so on...

I need to find out the top 10 best selling products. I have done a field to calculate the total unit sold. I know i can use subsummary to preview the top 10... but i need the effect of a table view and able to enter the data (remarks) at the same time. If i just sort it and view it in a table view , i will have duplicates records showing up...

Please help....

thanks

[Keith M. Davie]

Why would you have duplicate records? That does not seem like a very good idea to me.

[dragon78]

actually there is another db call tbl_invoice which keeps track of all the invoice number and the billing organisation's address. then the tbl_invoice_product will have the invoice number and the list of products under that invoice. in 1 month, there could have many invoices, ordering the same products. I need to find out the top10 most sellable products. Is there a way to achieve it without using sub summary, as sub-summary is in preview mode and i need to enter some data, like remarks for each most sellable item.

thanks~

[Keith M. Davie]

"I know i can use subsummary to preview the top 10... but i need the effect of a table view "

You "can use subsummary to preview the top 10..."; to me that means that you are finding the necessary set of records to have an accurate subsummary. (Let's call that field: s_s_item)

Here is one design approach you might consider.

Create a new layout. On that layout and in the body place the field s_s_item. (not in a subsummary part). Go to the layout in which you "use subsummary to preview the top 10..." and perform the find which displays the top 10 in the subsummary report. Now go to the new layout. Does the field s_s_item display the correct number of the found set? Can you construct a "table" to display the data you want displayed. including blank fields to allow comment entries? Or do you need to go to Table View? And if the latter, can you arrange the layout such that when you go to Table View you get the results you desire?

If this more or less works, but you are having problems with duplicate records, then you must design your solution to not have duplicate records.

Solutions are basically a matter of design.