Query not giving information

[Guest]

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

 

[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.

 

[Guest]

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 [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>

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));

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.

 

[Guest]

Ken,

what kind of information do you need me to provide?
I have 5 Tables in thiw query (as shown by the SQL I sent)
1)AC Table.- has the ACtivity # activity description
2) Employee current data has employee#, name, last name, job code, job code
description
3) FY05 PPE Dates has the pay period ending date and the month that the Pay
PEriod Endnf correspond to.
4) Total Hrs Total Amont
has employee #, sum of hours worked for specific PPE, and the sum of amount
worked for same PPE (pay perio ending date)
5) DDS File P24 .- has employee #, Activity#, Hour worked, Payperiod ending
date, AMount, Accounting Unit, etc.

I have develop this query to produce a Report that shows how many hours the
employee has work during a pay period, for each activity and if activityis
blank then show hours and amont worked for each accounting unit ( ona
difrent query/subreport).
Now, let's say that one employee orked on PPE (Pay period ending 12/12/04 (2
weeks) is sum hours ) 20 hr on Activity 430018
15 hrs on Activity 430018 and 45 hr in aactivity 411120
The report shows :
Activity Hrs
430018 20
430018 15
411120 45

But if you worked 20 hrs in 430018 and 20 hr in 430018 and 40 hr in 411120.
the query only shows:
Activity Hrs
430018 20
411120 40

As you see the other 20 hours is missing.
plese let me now if you need more infor, or proviude me your e-mail address
to send you some screen shots

Thanks in advance for your prompt response
Lila

 

[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!

 

[Ken Snell [MVP]]

I'm still not 100% certain of what is happening, but I believe the reason
your query is not producing the correct results is because it is a totals
query that is grouping on numerous fields, twoof which are the number of
hours and the work activity. Thus, if you have two records in the source
data with the same values for these two fields for the employee, then only
one will be displayed.

Either change the query that you posted to a select query (get rid of the
GROUP BY and HAVING clause), or delete the hours from the group by clause,
or use a sum for the hours field.

 

[Guest]

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

 

[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>

 

[Guest]

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

is this statment the one I need to use?

 

[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.