Thank you for your help!
Here are the tables that feed Query #1:
FullName (*EmployeeID, FirstName, LastName, MI, Full)
EmployeeRecord (*EmployeeID, DailyHours, Status, other misc. employee info)
PeriodLookUp (*PeriodID, CycleEndDate, Period, PeriodDays)
EmployeeCoS (*EmployeeID, *PeriodID, COS)
Here's the Query #1 SQL:
SELECT EmployeeRecord.EmployeeID, [Full Name].Full,
EmployeeRecord.DailyHours, PeriodLookUp.PeriodDays, PeriodLookUp.Period,
PeriodLookUp.CycleEndDate, ([DailyHours]*[PeriodDays]*([EmployeeCoS].[COS]))
AS MonthlyCOS, EmployeeCoS.COS
FROM ((EmployeeRecord INNER JOIN [Full Name] ON EmployeeRecord.EmployeeID =
[Full Name].EmployeeID) INNER JOIN (ProjectLookup INNER JOIN (PeriodLookUp
INNER JOIN EmployeeRevenue ON PeriodLookUp.Period = EmployeeRevenue.Period)
ON ProjectLookup.ProjectNumber = EmployeeRevenue.Project) ON
EmployeeRecord.EmployeeID = EmployeeRevenue.Emp_ID) INNER JOIN EmployeeCoS ON
(PeriodLookUp.PeriodID = EmployeeCoS.PeriodID) AND (EmployeeRecord.EmployeeID
= EmployeeCoS.EmployeeID);
This query should give me a line for each employee for each month with their
totalCOS for the month. This seems to be working and returning what I was
hoping for.
Then I have Query #2 that's based on Query #1:
SELECT qRpt_EmployeeCOS_1.EmployeeID, Sum(qRpt_EmployeeCOS_1.MonthlyCOS) AS
SumOfMonthlyCOS
FROM qRpt_EmployeeCOS_1
GROUP BY qRpt_EmployeeCOS_1.EmployeeID;
The total numbers it brings back aren't right -- if you add the numbers for
each employee from each month from the first query, then don't equal the
total on the 2nd query.
Thank you so much for your assistance!
Klatuu said:
What is the layout of the table you are starting with?
Where does MonthlyCOS come from? If you can help me understand your table
structure, I think we can get it to work for you.
--
Dave Hargis, Microsoft Access MVP
:
The underlying query seems to be working correctly for the few employees I've
checked.
I basically just want a total for the "MonthlyCOS" for each Employee.
Basically, what is their "MonthlyCOS" for the year so far? I thought I could
have the underlying query sum it up by month -- which is working. Now I just
need to sum the months together to come up with a total? Am I off track with
what I'm trying to do?
Thanks!!
:
Then your query would be expected to return 192 rows, not 5.
You are grouping by EmployeeID and summing the MonthlyCOS field.
What is the underlying query doing? Have you validated it is working
correctly?
--
Dave Hargis, Microsoft Access MVP
:
There are 192 employees -- they each have 5 entries in the underlying query
(qRpt_EmployeeCOS_1) -- 1 entry for each month for January - May. MonthlyCOS
is supposed to be a currency data type -- it's a calculation on the
underlying query. I never specify it as a currency, but the items ini the
calculation are either number or currency data types. MonthlyCOS will never
have a Null value.
Thanks!
Heather
:
How many employees do you have in the table?
What data type is [MonthlyCOS]?
Can [MonthlyCOS] ever have a Null value?
--
Dave Hargis, Microsoft Access MVP
:
I think this should be a simple query to do, but I can't get the sum values
correct. Here's my query:
SELECT qRpt_EmployeeCOS_1.EmployeeID, Sum(qRpt_EmployeeCOS_1.[MonthlyCOS])
AS SumOfMonthlyCOS
FROM qRpt_EmployeeCOS_1
GROUP BY qRpt_EmployeeCOS_1.EmployeeID;
I just want a sum of MonthlyCOS by each employee. When I run the report,
the sums are completely off. Also, when I changed the sum to a count it also
returned the wrong value -- it came back with 14 when it should only have
been 5. Any ideas?
Thanks,
Heather
