Originally Posted by
ArviLaanemets
Create a saved query:
qrySNList: SELECT DISTINCT SerialNumber FROM tblObjednavkyDetail
1. step after that - create a query like this (it is on fly, so I can't quarantee it works without some tweaking, but I think you get the general pricipe):
SELECT sn.SerialNumber, odet.Cena AS PurchasePrice, prod.Cena As SalesPrice
FROM ((qrySNList sn LEFT JOIN tblObjednavkyDetail odet ON odet.SerialNumber = sn.SerialNumber AND odet.FKObjednavkaID Is Not Null) LEFT JOIN tblObjednavkyDetail prod ON prod.SerialNumber = sn.SerialNumber AND prod.FKProduktID Is Not Null)
(NB! I had to correct it a bit!)
When this works, then continue with joining table tblObjednavky to odet and tblProdukty to prod.
When this works also, then continue with joining table tblFirmy to tblObjednavky and tblFirmy to tblProdukty. NB! you have to join the table tblFirmy twice, so use different aliases for table tblFirmy in those joins.
An afterthought: Use only outer joins (LEFT/RIGHT JOIN) in query - inner join excludes all rows where join condition is not valid (i.e. where part is only purchased or only sold).