Counts vs Grouping vs Rollup

[joesmithf1]

Hi,

I have an Employee Table with 3000 records. There are 3 records person employee. Now I was able to 'Group' the 3 records per employee into one by running a query. In this query, I used a "Count" function and Access gave me a result of 3000. HOWEVER, I do NOT want a count of 3000, b/c in reality, I have only 1000 employees total. What would I have to add to the query to get the correct count?

Thank you.

Joe

 

[pbaldy]

It would help to see your query, but in general you'd need to group by employee, watching out for the fact that if you group by other fields that aren't unique, you'll get more than one record per employee.

 

[joesmithf1]

Below is my query. Keep in mind that inside this "Biweekly_Temp_Table" there are 3 records per individual. I just want to have one count per employee. Thanks!




SELECT First(Biweekly_Temp_Table.DEDPLAN_CD) AS FirstOfDEDPLAN_CD, First(Biweekly_Temp_Table.DEDTYPE_CD) AS FirstOfDEDTYPE_CD, Sum(Biweekly_Temp_Table.[Employer Amt]) AS [SumOfEmployer Amt], Sum(Biweekly_Temp_Table.[Employer Actl]) AS [SumOfEmployer Actl], Sum(Biweekly_Temp_Table.[Admin Amt]) AS [SumOfAdmin Amt], Sum(Biweekly_Temp_Table.[Admin Actl]) AS [SumOfAdmin Actl], Sum(Biweekly_Temp_Table.[Employee Amt]) AS [SumOfEmployee Amt], Sum(Biweekly_Temp_Table.[Employee Actl]) AS [SumOfEmployee Actl], Biweekly_Temp_Table.RepUnit, Biweekly_Temp_Table.deductionType, Biweekly_Temp_Table.deductionLeftType, Biweekly_Temp_Table.SumOfNBR, First(Biweekly_Temp_Table.Tier) AS FirstOfTier, First(Biweekly_Temp_Table.Carrier) AS FirstOfCarrier, Plan.PlanDesc
FROM Biweekly_Temp_Table LEFT JOIN Plan ON (Biweekly_Temp_Table.DEDPLAN_CD = Plan.Plan) AND (Biweekly_Temp_Table.deductionLeftType = Plan.deductionType)
GROUP BY Biweekly_Temp_Table.RepUnit, Biweekly_Temp_Table.deductionType, Biweekly_Temp_Table.deductionLeftType, Biweekly_Temp_Table.SumOfNBR, Plan.PlanDesc
HAVING (((Biweekly_Temp_Table.RepUnit)="EW") AND ((Biweekly_Temp_Table.deductionType)="01") AND ((First(Biweekly_Temp_Table.Tier))<>"17" And (First(Biweekly_Temp_Table.Tier))<>"00"));

 

[pbaldy]

I guess it would also be helpful to know the nature of the data in each field, but my guess is that at least 1 of the 5 fields in the GROUP BY clause are different for each employee's 3 records, which would cause it to return all 3 records each instead of 1.

 

[joesmithf1]

Hi, I think i might have not understood your question, or you might have misunderstood my question.

Let me explain the nature of the "original" table. There are 3 records per individual because each record is associated with a 'type' of deduction(employee's deduction, employer's deduction, and administration's deduction). So what I did was I created a query to 'combine' these 3 records of each employee and put the amounts side-by-side. Therefore, the resulting query would look something like this.

Name|EmployeeDED |EmployerDED|AdminDED
Joe| $10| $20| $30

Now, since my table can have any numbers of employees, and NOT all employee may have 3 records, I am trying to find the 'correct' count of employees(single record). I hope this clarify my question.

The result is NOT returning 3 records; it is returning one, so the result of the query is correct. HOWEVER, the COUNT column is 'counting' 3 records, so the only incorrect result from the query is the count.

 

[Ghostjung]

I haven't tried this, but perhaps you could use your first query inside a new query to count the results of your first query?

i.e. query 1 shows 1000 records, but counts 3000. However, query 1 is now in query 2, and therefore, in theory, count should now be 1000 in query 2's output.

Edit: What happens when you don't use 'first' in your SELECT section of your query?

 

[joesmithf1]

Thanks for the sugestion, Ghostjung. I have tried query within a query before, but it causes some strange results once you start 'grouping' things.

To your second question; I tried using group by, last, max, etc. but that didn't work.

I'll try different things and see if there is any luck.

Joe

 

[tho28199]

Could you not just add another column to your first query, eg. Counter: 1, and then use sum([Counter]) in your second query?

The results of your first query would look something like:

Name|EmployeeDED |EmployerDED|AdminDED
Joe| $10| $20| $30| 1
Bill | $15| $20| $25| 1


Stu

 

[tho28199]

Oops! That should have read:

The results of your first query would look something like:

Name|EmployeeDED |EmployerDED|AdminDED |Counter
Joe| $10| $20| $30| 1
Bill | $15| $20| $25| 1

 

[Ghostjung]

Joe, I don't suppose you are just able to attach a zipped copy of your table? That way, we might have a clearer understanding of what could be happening.