-
Nov 15th, 2000, 01:28 PM
#1
Thread Starter
Addicted Member
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
-
Nov 15th, 2000, 04:27 PM
#2
Lively Member
Try this:
SELECT COUNT(*), machineno
FROM tablename
GROUP BY machineno
Dr_Evil
Senior Programmer
VS6 EE
VS.NET EA
-
Nov 15th, 2000, 04:44 PM
#3
Thread Starter
Addicted Member
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
-
Nov 15th, 2000, 05:03 PM
#4
Lively Member
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!
Dr_Evil
Senior Programmer
VS6 EE
VS.NET EA
-
Nov 17th, 2000, 08:58 AM
#5
Thread Starter
Addicted Member
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
-
Nov 17th, 2000, 10:07 AM
#6
Thread Starter
Addicted Member
Thanks for all the help Dr_Evil. I finally figured out how to get the group I want and all the totals.
jeffro!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|