I chosen second table to get data and later added fields from first table. Both have their own date fields.
This is not clear, can you explain using the names of the tables and names of fields?
If you were to use the same data source table for Sale Line Items and Purchase Transactions, your reporting might be easier. You might use different Qty fields for purchase and sales as well as different match fields for billing and invoices, but all in one table.
Another option you might explore is a report table where you import records from these two separate tables in order to be the data into one table for report purposes.
Actually I chosen "Purchase Trans" table to get data to design report layout in list view. It has fields like ProductIDfk, Purchase Date, Qty Purchased. Then I added fields into same layout from "Sale Trans" which have fields like ProductIDfk, Sold Date, Qty Sold. As the field ProductIDfk is common in both tables and coming from one table "Products" so I made relationship of many to many. These both tables are children of main table Products as shown in above structure.
I worked on both of your suggested options. Both worked but not fine. Each have some drawbacks. In first option, I need at least two separate tables for purchase entries and sale bills which share a table of transactions. But then those have two separate fields for dates of purchases and sales which you know will not give required results for monthly report. If I make one table for both purchase entries and sale bills with a common table of transaction for portal, it will give accurate results in reports. Then I can create separate layouts for purchases and sales and if I create a record in purchase layout and enter some data of products in portal, it will show up again in second layout of sales with empty fields of sale qty. And vise versa. That is annoying and looks a poor design.
For second option, user need to import data from separate tables each time he want to see report of product summary.
Second option look attractive if there comes a button with script of import steps recorded which can import data before run a report.
phil I have seen you answered many of similar questions. Can you refer any of your answer which could help in my case. I searched a lot in this community but all are different cases.
Ok I have got the solution.
I kept the Purchase Order and Sale Bill separate tables. These two share data to their child table Transactions. This Transaction table has its own single field for Date. This date set to Creation date or to fill manually. Purchase and Sale tables have their own dates fields. Report will only see the date of transaction and show record on month or any date range from this field. Report of purchase and sale is working fine now and no drawback in my database.
Thank you for help as usual.
I did not see your earlier reply and questions, but I see that you finally figured out how to do what I was suggesting.