Would subquery fix this problem? (Access 2003)

A

Ann Scharpf

I am creating my first database. I read through some postings and I THINK a
nested query would fix this problem but I'm not sure ... and, if it IS the
fix, I am not sure of the right syntax to use.

I have a query that is calculating overtime totals for employee time
postings. I have TWO overtime codes (OS & OU). Right now, I am getting TWO
overtime totals per employee. I would like the query to give me a per
employee grand total of the time for both codes.

Here is the SQL view of my query:

SELECT [Study Personnel, Study Tasks].Name, Sum([Study Personnel, Study
Tasks].TotalHours) AS [SumOfTotal Hours],
Sum([TotalHours]*[CivilianOvertimeRate]) AS OvertimeCost INTO [Study Overtime
Cost]
FROM [Study Personnel, Study Tasks], [Standard Variables]
GROUP BY [Study Personnel, Study Tasks].Name, [Study Personnel, Study
Tasks].[Hour Type Code]
HAVING ((([Study Personnel, Study Tasks].[Hour Type Code])="os" Or ([Study
Personnel, Study Tasks].[Hour Type Code])="ou"));

Thanks for any help you can give me.
 
B

Brian Camire

You might try something like:

SELECT
[Study Personnel, Study Tasks].Name,
Sum([Study Personnel, Study Tasks].TotalHours) AS [SumOfTotal Hours],
Sum([TotalHours]*[CivilianOvertimeRate]) AS OvertimeCost
INTO
[Study Overtime Cost]
FROM
[Study Personnel, Study Tasks],
[Standard Variables]
WHERE
[Study Personnel, Study Tasks].[Hour Type Code] = "os"
Or
[Study Personnel, Study Tasks].[Hour Type Code] = "ou"
GROUP BY
[Study Personnel, Study Tasks].Name;
 
A

Ann Scharpf

That worked perfectly! Thank you. Now I will sit down and read it carefully
and try to understand it.

Ann

Brian Camire said:
You might try something like:

SELECT
[Study Personnel, Study Tasks].Name,
Sum([Study Personnel, Study Tasks].TotalHours) AS [SumOfTotal Hours],
Sum([TotalHours]*[CivilianOvertimeRate]) AS OvertimeCost
INTO
[Study Overtime Cost]
FROM
[Study Personnel, Study Tasks],
[Standard Variables]
WHERE
[Study Personnel, Study Tasks].[Hour Type Code] = "os"
Or
[Study Personnel, Study Tasks].[Hour Type Code] = "ou"
GROUP BY
[Study Personnel, Study Tasks].Name;


Ann Scharpf said:
I am creating my first database. I read through some postings and I THINK a
nested query would fix this problem but I'm not sure ... and, if it IS the
fix, I am not sure of the right syntax to use.

I have a query that is calculating overtime totals for employee time
postings. I have TWO overtime codes (OS & OU). Right now, I am getting TWO
overtime totals per employee. I would like the query to give me a per
employee grand total of the time for both codes.

Here is the SQL view of my query:

SELECT [Study Personnel, Study Tasks].Name, Sum([Study Personnel, Study
Tasks].TotalHours) AS [SumOfTotal Hours],
Sum([TotalHours]*[CivilianOvertimeRate]) AS OvertimeCost INTO [Study Overtime
Cost]
FROM [Study Personnel, Study Tasks], [Standard Variables]
GROUP BY [Study Personnel, Study Tasks].Name, [Study Personnel, Study
Tasks].[Hour Type Code]
HAVING ((([Study Personnel, Study Tasks].[Hour Type Code])="os" Or ([Study
Personnel, Study Tasks].[Hour Type Code])="ou"));

Thanks for any help you can give me.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION 4
Worksheet problem 3
Join Query Problem 2
unique records with summed quantities 2
You tried to execute a query that does not include the specified expression... 1
Calculations in Access and Excel don't match 2
Getting Cartesian Product in query but Access 2003 prevents outer 4
Append Query 0

Top