Counting duplicate records in a query. 1

[Rubius]

What I have is a query that has a few fields but the only important one is the problem one. the products are all sorted by the job's so one job can have the same problem more then once. Now, I need to generate a table that has another column that says how many times it has had this problem so that I don't print out the same thing a few times. SO generally I want it to look like this:<br>Go from this:<br>Job Desc<br>10&nbsp;&nbsp;Blah<br>10&nbsp;&nbsp;Blah<br>10&nbsp;&nbsp;Blah2<br>To this:<br>Job Desc Times<br>10&nbsp;&nbsp;Blah&nbsp;&nbsp;2<br>10&nbsp;&nbsp;Blah2 1<br><br>thanks in advance.

 

[kathryn]

If I understood your question, this is the SQL you need:<br><br>SELECT Table1.Job, Table1.Desc, Count(*) AS Times<br>FROM Table1<br>GROUP BY Table1.Job, Table1.Desc;<br><br>Let me know if you need anything else or more detail on the above.<br><br>Kathryn

 

[Rubius]

where do I enter this data?? and is it done through the query?? because I would like the query to generate that table like the one I displayed.

 

[kathryn]

Open the query and on the menubar, choose View...SQL.&nbsp;&nbsp;Replace the SELECT statement that is there with the above statement.&nbsp;&nbsp;Don't forget to make sure that there is a semicolon at the end of the statement (I always forget!)<br><br>When you have entered the SQL statement, choose View....Design to see what the query looks like in design view.<br><br>Let me know how it goes.

 

[Rubius]

Thanks Worked great!!!! All I ended up needing to do was add the Count(*) AS Times&nbsp;&nbsp;line to the select section of the query..<br>thanks!