I have a table WorkorderDetails and a Subform WorkorderSubform that are linked. I want to run a query that sums the quantity field of all records with the same part number as the one on the current record in the subform. I hope this isn't too confusing. Any suggestions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Sum query
- Thread starter Fonzie
- Start date
I hope this helps. I am not a qualified programmer, but I had a similar issue, and sought help in a forum.
My problem was that I needed to Sum hours spent per client--> per job number--> for a given pay period entered on a form. Here is the solution I developed from the help I was given:
My query is this:
PARAMETERS [[Forms]!TimeSheetReport![BeginningDate]] DateTime, [[Forms]!TimeSheetReport![EndingDate]] DateTime;
SELECT TimeSheet.ClientID, TimeSheet.JobID, Sum(TimeSheet.Pieces) AS Pieces, Sum(TimeSheet.PieceHours) AS PieceHours, Sum(TimeSheet.TotRegHours) AS TotRegHours, Sum(TimeSheet.LunchHours) AS LunchHours, Sum(TimeSheet.DaysPaid) AS DaysPaid, Sum(TimeSheet.RefToWork) AS RefToWork, Sum(TimeSheet.Downtime) AS DownTime, Sum(TimeSheet.Vacation) AS Vacation
FROM Client INNER JOIN TimeSheet ON Client.ClientID = TimeSheet.ClientID
WHERE (((TimeSheet.Date) Between [Forms]![TimeSheetReport]![BeginningDate] And [Forms]![TimeSheetReport]![EndingDate]))
GROUP BY TimeSheet.ClientID, TimeSheet.JobID;
This reads the beginning date and ending date from the fields on the form TimeSheetReport, sorts out all records in the TimeSheet table, orders them by client, by job, then Sums all hours performed by a particular client for a particular job. I believe the fact that you are quering a sub form will not make any difference because ultimately with the query you are quering a tabl. Make sure the relationship is visible in the query design window if you need to add the table the subform is reading from.
I hope this helps.
My problem was that I needed to Sum hours spent per client--> per job number--> for a given pay period entered on a form. Here is the solution I developed from the help I was given:
My query is this:
PARAMETERS [[Forms]!TimeSheetReport![BeginningDate]] DateTime, [[Forms]!TimeSheetReport![EndingDate]] DateTime;
SELECT TimeSheet.ClientID, TimeSheet.JobID, Sum(TimeSheet.Pieces) AS Pieces, Sum(TimeSheet.PieceHours) AS PieceHours, Sum(TimeSheet.TotRegHours) AS TotRegHours, Sum(TimeSheet.LunchHours) AS LunchHours, Sum(TimeSheet.DaysPaid) AS DaysPaid, Sum(TimeSheet.RefToWork) AS RefToWork, Sum(TimeSheet.Downtime) AS DownTime, Sum(TimeSheet.Vacation) AS Vacation
FROM Client INNER JOIN TimeSheet ON Client.ClientID = TimeSheet.ClientID
WHERE (((TimeSheet.Date) Between [Forms]![TimeSheetReport]![BeginningDate] And [Forms]![TimeSheetReport]![EndingDate]))
GROUP BY TimeSheet.ClientID, TimeSheet.JobID;
This reads the beginning date and ending date from the fields on the form TimeSheetReport, sorts out all records in the TimeSheet table, orders them by client, by job, then Sums all hours performed by a particular client for a particular job. I believe the fact that you are quering a sub form will not make any difference because ultimately with the query you are quering a tabl. Make sure the relationship is visible in the query design window if you need to add the table the subform is reading from.
I hope this helps.
I should add that you can use the Parent/Child binding quality on the form (Main Form/Sub Form) in the query by using the Inner Join. I didn't use a sub form on my Timesheet form, but I did use the foreign key of my Clients table to identify clients. This allows me to sort all clients (and job numbers) out.
Good Luck!
Good Luck!
- Status
Similar threads
- Locked
- Question
- Locked
- Question