union query report shows duplicate rows

[smile]

I have a report that is made from data in 2 tables.

My date format is Y-M-D

One table has income data the other expenses.

I made queries from these two tables with sums of each transaction.

I have union query:

Select 
Data
From kio_group
UNION Select 
Data
From kpo_group;

Then I made a query that combines all data fields I need in my report:

SELECT Query3.Data, kio_group.KIO, kio_group.[Isduoti:], kio_group.SumOfSavikaina, kpo_group.KPO, kpo_group.[Priimta is:], kpo_group.[Kaina su PVM]
FROM (Query3 LEFT JOIN kpo_group ON Query3.Data = kpo_group.Data) LEFT JOIN kio_group ON Query3.Data = kio_group.Data
GROUP BY Query3.Data, kio_group.KIO, kio_group.[Isduoti:], kio_group.SumOfSavikaina, kpo_group.KPO, kpo_group.[Priimta is:], kpo_group.[Kaina su PVM];

My problem is that:

If for example a certain date like 2008-01-02 has
1 income documents.
2 expense documents.

I get duplicate rows for income documents because access shows a row for every document and because that date had 2 expense documents it must have 2 rows, but why it duplicates the income two items.

Tried using report grouping feature to no avail, I still get 2 date fields and grouping makes no sense here.

Obviously the running sum shows nonsense, and it gets even more weird if for certain date there are like 10 income documents and 2 expense.

-------------------------

I think I would need to use sub report feature to include 2 separate reports? but how do I calculate total running sum then. running sum income - running sum expenses?

 

[smile]

Data isn't a very descriptive name for a column so I don't know what your join is based on. However, from your description, it seems that the two tables should not be joined at all. Just because two tables contain the same foreign key doesn't mean you can join them and produce a recordset that makes sense. When joining tables, your join will normally go from the primary key of one table to a data field (foreign key) in the other table. Joins that go data field to data field will produce Cartesian products which will appear to duplicate rows because the resulting recordset contains one row for every row in tblA matched to each "matching" row from tblB. So if you have three income documents and two expense documents you will end up with six rows in the recordset - 3x2 = 6 which is almost certainly not what you had in mind.

To produce an output that contains data from both of these tables, you will need to us a report that has two subreports. One for tblA and one for tblB.

Thanks, I used the subreport feature.