Keep getting duplicates in my results!!

[Lura]

I have two tables the query is based on are:
Table 1: Code, Asset Name, Shares, AccountNo

Table 2: Code, Action, Activity Level, Issue, Begin Date, End Date, Comments

Under the begin date in the query I have >=([Start])which works fine.

Under the AccountNo I have listed just certain accounts we want on this report. (i.e. "55511"Or"55512"Or"55513".

As soon as I run the query those that have shares in more than one account seem to multiply many times. Some seem to have duplicates or triplicates.

I'm not sure where to start first. Any suggestions are appreciated..

 

[KDg]

it might be a problem with the joins in the query - can you post the SQL here?

ta

Drew

 

[Lura]

Here's the SQL.
SELECT DISTINCTROW tbl_Actions.Code, [tbl_MonthEndTrust--fromComerica].Shares, tbl_Actions.Action, tbl_Actions.[Activity Level], tbl_Actions.Issue, tbl_Actions.[Begin Date], tbl_Actions.[End Date], tbl_Actions.Comment
FROM tbl_Actions INNER JOIN [tbl_MonthEndTrust--fromComerica] ON tbl_Actions.Code = [tbl_MonthEndTrust--fromComerica].Ticker
WHERE (((tbl_Actions.[Begin Date])>=([Start Date])) AND (([tbl_MonthEndTrust--fromComerica].AccountNo)="05510" Or ([tbl_MonthEndTrust--fromComerica].AccountNo)="05511" Or ([tbl_MonthEndTrust--fromComerica].AccountNo)="05512"))
ORDER BY tbl_Actions.Code;

 

[Lura]

I need show the issue in process with each company in the accounts listed, as well as the activity level, the number of shares for each code, what action was taken, when the action began and ended, and any comments about the action itself.

 

[Lura]

Upon looking closer at the original tables that the query is based on I do see that there is another column that I have overlooked. That is, the 'lot number', the shares have also been purchased in many lots, so there in I believe is where is appeared to be duplicates. So, my next question is how to I sum these so that the rest of the information doesn't continue to be repeated?

Secondly, I have tried to build a report and subreport as you requested and the supreport does not have any returns. When I run the report it just keeps asking for the [start date] over and over and if I click cancel I get the title of the subreport (rsubactions).

Thanks. I hope you can help.

 

[Lura]

Having little experience, I need to have more explanation. Option A sounds the easiest, but can I then enter the date each time I run the report, as we need this. If so, then can you help me to write the where clause and tell me exactly where to place it in the report. How to I know if I'm using the OpenReport Method? This is very new to me.

Also, when I changed the Group By operator to Sum for the field I want to sum and run query it returns, "Can't group on Memo or OLE object tbl.Actions.comment.



[This message has been edited by Lura (edited 08-20-2001).]