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;

 

[Pat Hartman]

If you include AccountNo in the select clause, you may see that the rows are not actually duplicates. Can you explain in words what you want this query to do?

 

[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.

 

[Pat Hartman]

Why do you think that you have duplicates? When you join two tables together and those tables have a 1-to-many relationship, The data from the "1" side table is "duplicated" for each matching row of the "many" side table. That is not considered duplication. It is how you want a relational database to work.

If you are trying to sum() some column from the "1" side, you cannot do that in this query. You need to do this with a report and subreport. The report would use only data from the "1" side and the subreport would use only data from the "many" side. The master/child links would keep the two recordsets synchronized.

 

[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.

 

[Pat Hartman]

To get rid of the multiple date prompts you need to do one of the following:
a)If you are opening the report with the OpenReport Method, use the optional where clause to supply the date criteria and remove it from the query.
B)The other option is to replace the parm prompt in the query with a reference to a field on an open form -
>= Forms!YourForm!YourDate
C)You could also use a Public variable to hold the date parm, but this requires writing a Function that you can call from the query since you cannot reference VBA variables directly in SQL.

To sum the lots, remove the lot field from the select part of the query and press the sigma button to make the query a totals query. Change the Group By operator to Sum for the field(s) that you want to sum.

 

[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).]