<div>
Try this:<br />
Select ID, Date, sum(Harvest) from Table1 <br />
where Species = 'X' and Year = 'Y'<br />
group by ID, Date having count(*) &gt;&nbsp;1 and sum(Harvest) &gt;&nbsp;100<br />
<br />
PJ <br />

</div>


<div>
SELECT ID, DATE, SUM(HARVEST) FROM TABLE WHERE SPECIES=... AND YEAR=... GROUP BY ID, DATE HAVING COUNT(HARVEST)&gt;1 AND SUM(HARVEST)&gt;100<br />
should give the sumation, i.e.<br />
3 &nbsp;12/12/200Y &nbsp;110<br />
4 &nbsp;01/03/200Y &nbsp;110<br />
<br />
and I think this is about what you are after<br />
SELECT A.ID,A.DATE,A.HARVEST FROM TABLE A LEFT JOIN TABLE B ON A.ID=B.ID AND A.DATE=B.DATE &nbsp;WHERE B.SPECIES=... AND B.YEAR=... GROUP BY B.ID, B.DATE HAVING COUNT(B.HARVEST)&gt;1 AND SUM(B.HARVEST)&gt;100<br />

</div>


<div>
try something like this<br />

<pre><code class="code-1 nolinks" id="code-211015"><span>SELECT a.ID, a.DATE, a.HARVEST </span>
<span>FROM your_table a JOIN </span>
<span>(select id, date from your_table where Species = 'X' and Year(date) = 200Y</span>
<span>group by ID, Date having  sum(Harvest) &gt; 100</span>
<span>) b </span>
<span>on a.id = b.id and a.date = b.date </span>
<span>where a.Species = 'X'</span>
<span>order by a.id, a.date, a.HARVEST</span>
</code></pre>
</div>


<div>
Duplicate ID and Date combo - ID sounds a bit unique - maybe an autonumber column - are you sure about that combo ?<br />
<br />
select t.year, t.species, sum(t.harvest) as sum_harvest<br />
from table T<br />
where exists (select count(*), sum(harvest) from table b where t.year = b.year and t.species = b.species group by b.id, b.date having count(*) &gt;&nbsp;0 and sum(harvest) &gt;&nbsp;100)<br />
and t.year = 2008<br />
and t.species = 'X'<br />

</div>


<div>
The only thing I want to add is that I worked in Access so I had to make the JOIN become an INNER JOIN. Thanks! 
</div>


<div>
<div class="content wysiwyg-content">
I have one table named TABLE: with columns: ID, YEAR, SPECIES, HARVEST, DATE <br />
<br />
1) I need to restrict the records to SPECIES = X AND YEAR = Y<br />
2) For those records I need to find all instances of a duplicate ID/DATE combination<br />
3) For those duplicate ID/Date combinations I need to SUM the HARVEST<br />
4) If SUM(HARVEST) &gt;&nbsp;100, then I want those records returned, if not then I don't want to see them. <br />
<br />
I want the output to look like this: <br />
ID &nbsp; &nbsp; DATE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HARVEST<br />
3 &nbsp; &nbsp; &nbsp;12/12/200Y &nbsp; &nbsp; &nbsp; 55<br />
3 &nbsp; &nbsp; &nbsp;12/12/200Y &nbsp; &nbsp; &nbsp; 55<br />
4 &nbsp; &nbsp; &nbsp;01/03/200Y &nbsp; &nbsp; &nbsp; 60<br />
4 &nbsp; &nbsp; &nbsp;01/03/200Y &nbsp; &nbsp; &nbsp; 10<br />
4 &nbsp; &nbsp; &nbsp;01/03/200Y &nbsp; &nbsp; &nbsp; 40......etc<br />
<br />
PLEASE HELP! &nbsp;- Thanks!
</div>
</div>


I have one table named TABLE: with columns: ID, YEAR, SPECIES, HARVEST, DATE 

1) I need to restrict the records to SPECIES = X AND YEAR = Y
2) For those records I need to find all instances of a duplicate ID/DATE combination
3) For those duplicate ID/Date combinations I need to SUM the HARVEST
4) If SUM(HARVEST) > 100, then I want those records returned, if not then I don't want to see them. 

I want the output to look like this: 
ID     DATE                 HARVEST
3      12/12/200Y       55
3      12/12/200Y       55
4      01/03/200Y       60
4      01/03/200Y       10
4      01/03/200Y       40......etc

PLEASE HELP!  - Thanks!

filter data, find the duplicates, sum one column from duplicates, return all data where sum &gt; #

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

filter data, find the duplicates, sum one column from duplicates, return all data where sum &gt; #

filter data, find the duplicates, sum one column from duplicates, return all data where sum > #