Query building

[kronar]

I have a subform based on a query of daily time entries by job number. I wish to display each employee’s time for the job selected. I can’t seem to get the employee times to list as one line per employee with the work hours summed and the wage totaled by the wage rate for that employee.

My query uses JBT_EmpTime table and EM_Emp table linked by the many to one relationship on JBT_EmpNumber and EM_EmpNumber fields. For job number field I select from the frm Menu using [Forms]![frmMenu]![txtJobNum] in the Criteria row. I sort Ascending JBT_EmpTime. And in the Totals row each field is “Group By” except JBT_WorkHrs from the JBT_EmpTime table which is “Sum”. I am tring to calculate a dollar amount in the Tot field using “Tot:[EM_Wage]*[JBT_WorkHrs]” which is also “GroupBy”.

While the Tot field seems to calculate properly, I am getting a line for each row in the JBT_EmpTime table instead of the rows for any one employee being combinded in one line.

I am using Access 97 with the built in query builder.

Hope I explained this well enough.
Thanks for any help.

 

[PHV]

What is the actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kronar]

I don't know. How do I access the SQL. I am using the Access GUI. It seems to me that with "Group By" in each field column except the field to SUM by, how would it know which field I wanted to group by ( I think it groups by a combination of all the fields "Gouped BY" which would give the results I am getting).

 

[lespaul]

Switch the view from Query Design Grid to SQL view.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kronar]

Thanks.
Select (JBT_EmpTime.JBT_WorkHrs) as Hbe,EM_Emp.EM_Wage,[EM_Wage]*[JBT_WorkHrs]as Tot

From EM_Emp Inner Join JBT_EmpTime on EM_Emp.EM_EmpNumber = JBT_EmpTime.JBT_EmpNum

Group by JBT_EmpTime.JB_JobBidNo, JBT_EmpTime.JBT_EmpNum,EM_Emp.EM_FirstName,EM_Emp.EM_LAstName,EM_Emp.EM_Wage,[EM_Wage]*[JBT_WorkHrs]

Having (((JBT_EmpTime.JB_JobBidNo)=[Forms]![frmMenu]![txtJobNum])) order by JBT_EmpTime.JBT_EmpNum;

I understand the select clause, not real clear on the Sum clause,don't understand the From clause syntax but as it seems to work- won't worry about it,I think the group by clause is incorrect- I think it should group by EmpNum only to get each employee's tot of hours and wage calculated.

Thanks for the help.

 

[lespaul]

You should do a little reading.

First read the Understanding SQL Joins article below, that will help you understand the INNER JOIN clause.

Then check out these threads:
Group By Explanation
Thread701-1258387

And because Access mangles the use of the HAVING clause, here's an explanation of that:
Thread701-1262760


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

And what about this ?
SELECT E.EM_EmpNumber, E.EM_Wage, Sum(J.JBT_WorkHrs) AS Hbe, E.EM_Wage * Sum(J.JBT_WorkHrs) AS Tot
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY E.EM_EmpNumber, E.EM_Wage
ORDER BY E.EM_EmpNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kronar]

To start simply, I tried this

SELECT J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, Sum(J.JBT_WorkHrs) AS HbE, E.EM_Wage
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY E.EM_EmpNumber
ORDER BY E.EM_EmpNumber;

I keep getting the error:
"You tried to execute a query that doesn't include the specified expression 'JB_JobBidNo' as part of an aggregate function.
when I try to run the query by itself.

 

[dhookom]

This is one of the more descriptive and accurate error messages. Try:

SELECT J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, Sum(J.JBT_WorkHrs) AS HbE, E.EM_Wage
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, E.EM_Wage
ORDER BY J.JBT_EmpNum;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kronar]

Thanks, That worked.
Now how do I get a total of hours Worked for that employee (Summed) times Wage as Tot?
I tried:
-E.EM_Wage*J.JBT_WorkHrs As Tot
-E.EM_Wage*HbE As Tot
-E.EM_Wage * Sum(J.JBT_WorkHrs) As Tot
keep getting syntax error on it

 

[lespaul]

should just be:

SELECT J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, Sum(J.JBT_WorkHrs) AS HbE, E.EM_Wage, (Sum(J.JBT_WorkHrs) * E.EM_Wage) As Tot
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
WHERE J.JB_JobBidNo=[Forms]![frmMenu]![txtJobNum]
GROUP BY J.JB_JobBidNo, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, E.EM_Wage
ORDER BY J.JBT_EmpNum;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kronar]

That worked great, thanks.
Next I want a summary line totaling everyones Tot(wage times hours). Do I need a seperate query not grouped?

 

[dhookom]

Totaling should be done in a report. You can aggregate values in a group or report footer or header section.

Forms also allow totals. If you don't want to do it this way you could create another totals query and remove a level of detail.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kronar]

I need to show the Total of wages on the form and update the main table with it.

I designed anouther query with this SQL:
SELECT J.JB_JobBidNo AS Exp, J.JBT_EmpNum, E.EM_FirstName, E.EM_LastName, J.JBT_WorkHrs, E.EM_Wage, J.JBT_WorkHrs*E.EM_Wage AS Expr1
FROM EM_Emp AS E INNER JOIN JBT_EmpTime AS J ON E.EM_EmpNumber = J.JBT_EmpNum
HAVING (((Sum(J.JB_JobBidNo))=[Forms]![frmMenu]![txtJobNum]))
ORDER BY J.JBT_EmpNum;

However I don't know quite how to sum Hours and Total wages.

 

[dhookom]

Does that query actually work? There is no GROUP BY in the SQL. Didn't Leslie's query provide totals?

Are you sure you want to update a main table with a value that can be calculated?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kronar]

No, it doesn't work. I probbly won't keep the total in the main table as it would need to be recalculated each time.
How would I write the SQL to total the Labor cost calcculated from each employees' wage rate times his hours?

 

[dhookom]

Modify Leslie's suggestion.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kronar]

Hey, Thanks Guys!
I used a field in the subform footer =Sum(HbE) and anouther =Sum(Tot)

It looks like it works, only took me two years!!
Thanks again.