I have a list of duplicate names in the column of a query and I want to group them in the query (so a name appears only once) rather than a report. How do I do I?
I have a list of duplicate names in the column of a query and I want to group them in the query (so a name appears only once) rather than a report. How do I do I?
Either make a query,set property Unique values= true
or,
make a sum query on name
use a group by query - in query design view, click on the Epsilon (Totals) button in the ribbon.
Sigma sign,not epsilon.
oops! Think I've made that mistake beforeSigma sign,not epsilon.
Sorry Ajax and Ranman 256 but neither suggestions have worked. Maybe I wasn't clear enough. I have attached a pdf of the screenshot. This shows the results for one days results. I am trying to group together the part name so I just have one entry. So I have one entry per model.
It would return the following result.
Model Part name Quantity OK Total of Quantity
L462 Grille surround 3 15
group by model and partname, sum the rest - query would be something like
SELECT Model, [Part Name],sum(QuantityOK) as SumofQuantityOK, sum([Total of Quantity]) as sumoftotalofquantity
FROM [qry_weekly report_ppm)
GROUP BY Model, [Part Name]
if that doesn't work, post the sql you are actually using
Sorry Ajax I am not familiar with the terminology you have used.
I have added another screenshot of the design view I have so far with the error message which has been returned.
your attachment does not have an error message
1. open a new query
2. close the 'show table' form
3. click on the sql view button on the ribbon
4. copy and paste this code into the sql window
5. run the queryCode:SELECT Model, [Part Name],sum(QuantityOK) as SumofQuantityOK, sum([Total of Quantity]) as sumoftotalofquantity FROM [qry_weekly report_ppm) GROUP BY Model, [Part Name]
Copied and pasted in. When I try to run it I get a syntax error and the word SELECT is highlighted as the error?
Error message also states- Check the subquery's syntax and enclose the subquery in parentheses.
my guess is you have not removed the default SELECT that was already there, so try again but this time delete any code that is there before you paste
Screenshot attached.
that is not a query
SORRY Ajax. I missed out line 3 on your original instructions (click sql button).
I don't have any experience of that. I'm still learning really.
Just followed your instructions again and IT WORKS (just needed to change 1 bracket).
Thanks very much for your help.