I have a typical structure of my database like;
Customers -------<Sale Invoice--------<Sale Line Items>--------Products---------<Purchase Transactions>---------Purchase Bill
Every thing is working Ok and well setup. But when tried to design a report layout and I made many to many relationship of Sale Line Items and Purchase Trans tables. This report is critical as need to see which are the more running items to take future decisions. I chosen second table to get data and later added fields from first table. Both have their own date fields. I sorted data based on Product ID/Name and seen the data of purchase table shown correctly with all transaction with dates but record of sale table showing only first record repeated equal to purchase records. I came on a decision that I should redesign the database to make one table for both purchase and sale entries where there will be one date field for both sale and purchase transaction with different record numbers.
Before doing it I want to know if there is any other way or solution to do it with same structure?
Thanks for giving time.