One report for purchase and sale records of products from seperate tables.

Discussion created by akhlaq38 on Oct 17, 2016
Latest reply on Oct 21, 2016 by philmodjunk

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.