Query not giving information

L

lila

Hi,
I have develop a query that pull hours worked by activity# , by pay period.

Here is the issue:
If an employee worked in diferent activity numbers, the data shows.
If the employee worked in same activity but diferent hours, the result will
show.
BUT IF teh employee worked in SAME activity SAME HOURS it DOES NOT SHOW in
the result, it only shows once.

Let's say that you are paid bi-weekly therefore 2 weeks of 40 hrs.
well, the query is showiing only 40 hrs instead of 80, if you worked in the
same activity.
but if you worked 40, 10 & 30 hrs in the same activity it does shows.

How can I fix this?
please advise
Thanks
:Lila
 
K

Ken Snell [MVP]

You'll need to tell us more about the data, the table's structure, and the
query that you're using.... we cannot see your database, so we have no idea
how you've set these things up.
 
L

lila

Here is the SQL statment:
As you can see it works perfectly for the employees who have same activity
but diferent hours, the problem if on employees who works on same activity
with same hours.

SELECT [DDS File PP24].EMPLID, [Employee Current Data - HR - 11].[Preferred
Name], [Employee Current Data - HR - 11].SSN, [Employee Current Data - HR -
11].[Job Title], AC.DESCRIPTION, AC.[Long Desc], [DDS File PP24].PAY_END_DT,
[DDS File PP24].ACTIVITY, [DDS File PP24].HOURS, [Total Hrs Total
Amount].SumOfHOURS, [Total Hrs Total Amount].SumOfAMOUNT,
[HOURS]/[SumOfHours] AS [% Effort], [AMOUNT]/[SumOfAMOUNT] AS [% Salary],
"100.00 %" AS [Total% Effort], "100.00 %" AS [Total %Salary], [FY05 PPE
Dates].Month
FROM (((AC INNER JOIN [DDS File PP24] ON AC.ACTIVITY = [DDS File
PP24].ACTIVITY) LEFT JOIN [Employee Current Data - HR - 11] ON [DDS File
PP24].EMPLID = [Employee Current Data - HR - 11].EmpID) LEFT JOIN [Total Hrs
Total Amount] ON ([DDS File PP24].EMPLID = [Total Hrs Total Amount].EMPLID)
AND ([DDS File PP24].PAY_END_DT = [Total Hrs Total Amount].PAY_END_DT)) LEFT
JOIN [FY05 PPE Dates] ON [DDS File PP24].PAY_END_DT = [FY05 PPE Dates].[Pay
Period Ending Date]
GROUP BY [DDS File PP24].EMPLID, [Employee Current Data - HR -
11].[Preferred Name], [Employee Current Data - HR - 11].SSN, [Employee
Current Data - HR - 11].[Job Title], AC.DESCRIPTION, AC.[Long Desc], [DDS
File PP24].PAY_END_DT, [DDS File PP24].ACTIVITY, [DDS File PP24].HOURS,
[Total Hrs Total Amount].SumOfHOURS, [Total Hrs Total Amount].SumOfAMOUNT,
[HOURS]/[SumOfHours], [AMOUNT]/[SumOfAMOUNT], "100.00 %", "100.00 %", [FY05
PPE Dates].Month
HAVING ((([DDS File PP24].PAY_END_DT) Like [Enter Pay Period Ending Date:])
AND (([DDS File PP24].ACTIVITY) Is Not Null));
 
K

Ken Snell [MVP]

I'm sorry, but just the SQL statement will not help me much with
troubleshooting. I still have no idea what you're storing in the table as a
record, nor what you mean by the 'same activity with same hours'.

Provide the additional information, and also provide some sample data from
the table and what the query is producing from those records, plus what you
want the query to produce.

--

Ken Snell
<MS ACCESS MVP>


lila said:
Here is the SQL statment:
As you can see it works perfectly for the employees who have same activity
but diferent hours, the problem if on employees who works on same activity
with same hours.

SELECT [DDS File PP24].EMPLID, [Employee Current Data - HR -
11].[Preferred
Name], [Employee Current Data - HR - 11].SSN, [Employee Current Data -
HR -
11].[Job Title], AC.DESCRIPTION, AC.[Long Desc], [DDS File
PP24].PAY_END_DT,
[DDS File PP24].ACTIVITY, [DDS File PP24].HOURS, [Total Hrs Total
Amount].SumOfHOURS, [Total Hrs Total Amount].SumOfAMOUNT,
[HOURS]/[SumOfHours] AS [% Effort], [AMOUNT]/[SumOfAMOUNT] AS [% Salary],
"100.00 %" AS [Total% Effort], "100.00 %" AS [Total %Salary], [FY05 PPE
Dates].Month
FROM (((AC INNER JOIN [DDS File PP24] ON AC.ACTIVITY = [DDS File
PP24].ACTIVITY) LEFT JOIN [Employee Current Data - HR - 11] ON [DDS File
PP24].EMPLID = [Employee Current Data - HR - 11].EmpID) LEFT JOIN [Total
Hrs
Total Amount] ON ([DDS File PP24].EMPLID = [Total Hrs Total
Amount].EMPLID)
AND ([DDS File PP24].PAY_END_DT = [Total Hrs Total Amount].PAY_END_DT))
LEFT
JOIN [FY05 PPE Dates] ON [DDS File PP24].PAY_END_DT = [FY05 PPE
Dates].[Pay
Period Ending Date]
GROUP BY [DDS File PP24].EMPLID, [Employee Current Data - HR -
11].[Preferred Name], [Employee Current Data - HR - 11].SSN, [Employee
Current Data - HR - 11].[Job Title], AC.DESCRIPTION, AC.[Long Desc], [DDS
File PP24].PAY_END_DT, [DDS File PP24].ACTIVITY, [DDS File PP24].HOURS,
[Total Hrs Total Amount].SumOfHOURS, [Total Hrs Total Amount].SumOfAMOUNT,
[HOURS]/[SumOfHours], [AMOUNT]/[SumOfAMOUNT], "100.00 %", "100.00 %",
[FY05
PPE Dates].Month
HAVING ((([DDS File PP24].PAY_END_DT) Like [Enter Pay Period Ending
Date:])
AND (([DDS File PP24].ACTIVITY) Is Not Null));






Ken Snell said:
You'll need to tell us more about the data, the table's structure, and
the
query that you're using.... we cannot see your database, so we have no
idea
how you've set these things up.
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

lila -

I have been tied up on another project, and haven't had chance yet to review
your info and post back... but I will!
:)
 
L

lila

Ken,

I cannot delete the hours,because I am using this query for a report and I
need the hours to be display by each activity the employee worked for.

If I used the sum on hours field. The report doean't show the hours.

please help, i need tohave this resolve asap.
214-820-1068
 
K

Ken Snell [MVP]

Then you'll need to add one more field to the GROUP BY clause so that the
records with the "same" hours and "same" activity for the employee are no
longer "the same" -- in other words, they'd be unique and thus show in your
query.

As I noted before, the reason your query isn't giving you the separate
entries is because the query is not seeing a difference for the values of
the fields that it's selecting for display.
--

Ken Snell
<MS ACCESS MVP>
 
L

lila

how will access recognized that ?
usif IF Clause?
If Hours "Is Null" the Hours?

is this statment the one I need to use?
 
K

Ken Snell [MVP]

Try adding the primary key field of the table that holds the employee's hour
data that you're trying to get. Then add that primary key field to the GROUP
BY clause.
 
Top