Can you group within a query?

[jeffro]

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

[Dr_Evil]

Try this:

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

[jeffro]

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

[Dr_Evil]

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!

[jeffro]

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

[jeffro]

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

jeffro!