Can you group within a query?

I would like to see only one of each machineno and one total for that machineno. I'm not sure if you can do that. Is there a way to group by machineno?

thanks jeffro

Try this:

SELECT COUNT(*), machineno
FROM tablename
GROUP BY machineno

Where would I put the code you gave me within this sql statement?

SELECT DISTINCTROW MachineInfo.Machineno, MachineInfo.Division, MachineMaintenance.RepairTime, MachineMaintenance.SetupTime, MachineMaintenance.PMTime, ([MachineMaintenance]![RepairTime])+([MachineMaintenance]![SetupTime])+([MachineMaintenance]![PMTime]) AS Total
FROM MachineInfo INNER JOIN MachineMaintenance ON MachineInfo.Machineno = MachineMaintenance.Machineno;

Thanks jeffro

If you are selecting multiple fields you will need to remove the DISTINCT keyword and add each field that you have in the SELECT string to the GROUP BY.

EX:
SELECT MachineInfo.Machineno,
MachineInfo.Division,
MachineMaintenance.RepairTime,
MachineMaintenance.SetupTime,
MachineMaintenance.PMTime,
([MachineMaintenance]![RepairTime])+ ([MachineMaintenance]![SetupTime])+([MachineMaintenance]![PMTime]) AS Total

FROM MachineInfo
INNER JOIN MachineMaintenance
ON MachineInfo.Machineno = MachineMaintenance.Machineno;

GROUP BY MachineInfo.Machineno,
MachineInfo.Division,
MachineMaintenance.RepairTime,
MachineMaintenance.SetupTime,
MachineMaintenance.PMTime,
Total

I can't test this code but it should at least give you an idea of how to use the GROUP BY clause. Good Luck!

Thanks for the help Dr_Evil. But the query still is not showing only one Machineno and one total for all machineno's. I read that DistinctRow eliminates duplicate rows based on all columns of all tables included in the query. So I tried putting the distinctrow back into the sql statement, but it also did not have any effect.

Do you have any other ideas?

thanks again

Thanks for all the help Dr_Evil. I finally figured out how to get the group I want and all the totals.

jeffro!