query doble entries

[MacAcc]

Hi, this drives me crazy: I did a table with (stock) transactions (purchase and sales with all the details on quantity, price, fees etc). Then I made a first query (qryBestand) filtering out only the open transactions (not the sold) and made totals. So now I have for each security the right number of stock currently hold in the porfolio. I then made a second query (qryValuation) importing basically the fields form the first query (qryBestand) but adding some colums with calculated fields and joining to a price table. Everything Works fine but what is really strange is that now some (but not all) of the records show up twice with exactly the same information. I checked the underlaying query and table. But they are ok, only 1 entry, it is only going from the first to the second query that some record doble. Really weird.
Does anyone have an idea what this could be.
Tks in advance



Markus

[alansidman]

I suspect without seeing your queries that you have a join issue. Post your SQL statements for analysis and evaluation.

[MacAcc]

First Query

SELECT tblConsGroup.ConsGroupName, tblClientName.ClientName, tblClientNr.ClientNr, tblTrx.TrxPortfolio, Sum(tblTrx.TrxQty) AS SommeDeTrxQty, tblTrx.TrxSecID, Sum(tblTrx.TrxNetWoAccr) AS SommeDeTrxNetWoAccr, Sum(tblTrx.TrxNetEurWoAccr) AS SommeDeTrxNetEurWoAccr, tblTrx.TrxStatus
FROM ((tblClientName INNER JOIN (tblConsGroup INNER JOIN tblClientNr ON tblConsGroup.ConsGroupID = tblClientNr.ClientConsGroup) ON tblClientName.ClientNameId = tblClientNr.ClientName) INNER JOIN tblPortfolios ON tblClientNr.CientID = tblPortfolios.ClientNr) INNER JOIN tblTrx ON tblPortfolios.PortfolioID = tblTrx.TrxPortfolio
GROUP BY tblConsGroup.ConsGroupName, tblClientName.ClientName, tblClientNr.ClientNr, tblTrx.TrxPortfolio, tblTrx.TrxSecID, tblTrx.TrxStatus, tblTrx.TrxOpen, tblTrx.TrxClose
HAVING (((tblTrx.TrxStatus)="open"));

Second Query

SELECT qryBestand.ConsGroupName, qryBestand.ClientName, qryBestand.ClientNr, tblHldgs.HldgsIsin, qryBestand.TrxPortfolio, qryBestand.SommeDeTrxQty, tblHldgs.HldgsBloombergID, qryBestand.SommeDeTrxNetWoAccr, qryBestand.SommeDeTrxNetEurWoAccr, tblHldgs.HldgsName, tblCurPrices.CPrice, CCcyRate.CCcyPrice, IIf([HldgsQtyType]="NOMINAL",[SommeDeTrxQty]*[CPrice]/100,[SommeDeTrxQty]*[CPrice]) AS ValuationCcy, IIf([HldgsQtyType]="Nominal",[SommeDeTrxQty]*[CPrice]/[CCcyPrice]/100,[SommeDeTrxQty]*[CPrice]/[CCcyPrice]) AS ValuationEUR, [ValuationCcy]/[SommeDeTrxNetWoAccr]-1 AS PLCcy, [ValuationEUR]/[SommeDeTrxNetEurWoAccr]-1 AS PLEUR
FROM CCcyRate INNER JOIN (tblCurPrices INNER JOIN (qryBestand INNER JOIN tblHldgs ON qryBestand.TrxSecID = tblHldgs.HldgsIsin) ON tblCurPrices.PriceId = tblHldgs.HldgsBloombergID) ON CCcyRate.CcyBloom = tblHldgs.HldgsBloomCcyId;

[NTC]

Although not as you expect, the results are not weird at all. If in table 1 there is the value xyz (in the join field) and that value appears twice in table 2 - - then the query result will have it twice.

Some correct queries can only result in duplications. Assuming the query is fundamentally correct then one alters it to be a distinct query. You do this by having the query in design view; put your cursor in the upper half, and right click - look in the property sheet at right for 'Unique Values' - change the default from 'No' to 'Yes' and then save and close. When you rerun the query it will not have duplicates.

[MacAcc]

You made my day, thank you so much!!!

Markus