计算查询中的特定记录 [英] Counting particular Record in a Query

问题描述

好的 - 所以，我回来了。


我想计算一个特定记录出现在我的查询中一个表的字段中的次数，然后在同一查询中使用该值来计算平均。我正在使用Access 2000，我还在学习。


背景信息：


总的来说，我正在创建一个数据库来跟踪和计算有关的数据员工生产力。对于每个预定的日期，员工都有一定数量的积分可以赚取。员工可以在非预定的工作日赚取积分，以提高在薪资周期内获得的积分数，以获得奖金等。在整个周期中获得的积分数需要仅按预定工作日的数量进行平均。



在table-LOG中，字段[DayType}记录日期类型，Scheduled或Non-Scheduled。我还有字段存储员工姓名，服务日期，获得的积分数等等。



在查询中 -
< br $>
SumPoints：总结了指定整个时间周期内获得的所有积分。


平均值：平均值[SumPoints]


此时，[AvgEarn：]使用在指定日期范围内找到的记录总数作为[sumpoints]的分隔符。我需要它将[Sumpoints]除以预定的工作日数。


我试图在查询中设置一个字段来计算[daytype]，它会使用criteria = scheduled来计算我的预定天数。我希望然后创建AvgEarn：[sumpoints] / [count]。从这里开始，我需要使用AvgEarn来查询其他部分以计算其他内容等等。


这是解决问题的最佳方法。


再次感谢您的帮助！


-Jennifer

解决方案

Jennifer


将查询视图更改为SQL并按原样复制并粘贴查询。


Mary

好​​的 - 所以，我回来了。


我想计算一个特定记录出现在我的查询中一个表的字段中的次数，然后在同一查询中使用该值来计算平均。我正在使用Access 2000，我还在学习。


背景信息：


总的来说，我正在创建一个数据库来跟踪和计算有关的数据员工生产力。对于每个预定的日期，员工都有一定数量的积分可以赚取。员工可以在非预定的工作日赚取积分，以提高在薪资周期内获得的积分数，以获得奖金等。在整个周期中获得的积分数需要仅按预定工作日的数量进行平均。



在table-LOG中，字段[DayType}记录日期类型，Scheduled或Non-Scheduled。我还有字段存储员工姓名，服务日期，获得的积分数等等。



在查询中 -
< br $>
SumPoints：总结了指定整个时间周期内获得的所有积分。


平均值：平均值[SumPoints]


此时，[AvgEarn：]使用在指定日期范围内找到的记录总数作为[sumpoints]的分隔符。我需要它将[Sumpoints]除以预定的工作日数。


我试图在查询中设置一个字段来计算[daytype]，它会使用criteria = scheduled来计算我的预定天数。我希望然后创建AvgEarn：[sumpoints] / [count]。从这里开始，我需要使用AvgEarn来查询其他部分以计算其他内容等等。


这是解决问题的最佳方法。


再次感谢您的帮助！


-Jennifer

这是否为你工作？

展开 | 选择 < span class =codeDivider> | Wrap | 行号

SELECT DISTINCTROW Log.Clinician，Log.Location，Log.Expected，Sum（[Expected] -Log！[预计非工作日]）AS SumExpected，Sum（Log！DOMHA + Log！DOCS + Log！EAP + Log！[NRP Points]） AS SumEarned，Sum（Log.PTO）AS SumPTO，Avg（Log！DOMHA + Log！DOCS + Log！EAP + Log！[NRP Points]）AS AvgEarn，IIf（[AvgEarn]> [Expected]，[Expected] ，[AvgEarn]）AS Calc，[Calc] * [SumPTO] AS PTOPoint，Sum（Log！Admin）AS SumAdmin，[SumAdmin] * [Calc] AS AdminPoint，[SumEarned] + [PTOPoint] + [AdminPoint] AS TotPoints ，[TotPoints] / [SumExpected] AS Productivity

FROM Log

WHERE（（（Log.Date）> = [Forms]！[Report Date Range]！[[开始日期]和（Log.Date）< = [表格]！[报告日期范围]！[结束日期]））

GROUP BY Log.Clinician，Log.Location，Log.Expected;

Ok - So, I am back.


I would like to count the number of times a specific record appears in a field from one table in my query and then use that value in the same query to calculate an average. I am using Access 2000 and am still learning.

Background info:

Overall, I am creating a database to track and calculate data regarding employee productivity. For each scheduled day, employees have a set number of points to earn. Employees are able to earn points on non-scheduled work days to boost their number of points earned over the pay cycle to obtain bonuses and so on. The number of points earned over the entire cycle needs to be averaged by the number of scheduled work days only.



In table- LOG, the field, [DayType}, records the type of day, Scheduled or Non-Scheduled. I also have fields that store employee name, date of service, number of points earned and so on.



In the query -

SumPoints: summarizes all points earned for the entire time cycle specified.

AvgEarn: Avg [SumPoints]

At this time, [AvgEarn:] is using the total number of records found in the specified date range as the divider of [sumpoints]. I need it to divide [Sumpoints] by the number of scheduled work days only.

I tried to setup a field in the query to count [daytype] that would give me the number of scheduled days using criteria =scheduled. I was hoping to then create AvgEarn: [sumpoints]/[count]. From here I need to use AvgEarn for another portion of the query to calculate something else and so on.

This is the best I can do to explain the problem.

Once again, thank you in advance for your help!

-Jennifer

解决方案

Jennifer

Change the query view to SQL and copy and paste the query as it is now.

Mary

Ok - So, I am back.


I would like to count the number of times a specific record appears in a field from one table in my query and then use that value in the same query to calculate an average. I am using Access 2000 and am still learning.

Background info:

Overall, I am creating a database to track and calculate data regarding employee productivity. For each scheduled day, employees have a set number of points to earn. Employees are able to earn points on non-scheduled work days to boost their number of points earned over the pay cycle to obtain bonuses and so on. The number of points earned over the entire cycle needs to be averaged by the number of scheduled work days only.



In table- LOG, the field, [DayType}, records the type of day, Scheduled or Non-Scheduled. I also have fields that store employee name, date of service, number of points earned and so on.



In the query -

SumPoints: summarizes all points earned for the entire time cycle specified.

AvgEarn: Avg [SumPoints]

At this time, [AvgEarn:] is using the total number of records found in the specified date range as the divider of [sumpoints]. I need it to divide [Sumpoints] by the number of scheduled work days only.

I tried to setup a field in the query to count [daytype] that would give me the number of scheduled days using criteria =scheduled. I was hoping to then create AvgEarn: [sumpoints]/[count]. From here I need to use AvgEarn for another portion of the query to calculate something else and so on.

This is the best I can do to explain the problem.

Once again, thank you in advance for your help!

-Jennifer

Does this work for you?

Expand|Select|Wrap|Line Numbers

SELECT DISTINCTROW Log.Clinician, Log.Location, Log.Expected, Sum([Expected]-Log![Non Work Day Expected]) AS SumExpected, Sum(Log!DOMHA+Log!DOCS+Log!EAP+Log![NRP Points]) AS SumEarned, Sum(Log.PTO) AS SumPTO, Avg(Log!DOMHA+Log!DOCS+Log!EAP+Log![NRP Points]) AS AvgEarn, IIf([AvgEarn]>[Expected],[Expected],[AvgEarn]) AS Calc, [Calc]*[SumPTO] AS PTOPoint, Sum(Log!Admin) AS SumAdmin, [SumAdmin]*[Calc] AS AdminPoint, [SumEarned]+[PTOPoint]+[AdminPoint] AS TotPoints, [TotPoints]/[SumExpected] AS Productivity
FROM Log
WHERE (((Log.Date)>=[Forms]![Report Date Range]![Beginning Date] And (Log.Date)<=[forms]![Report Date Range]![Ending Date]))
GROUP BY Log.Clinician, Log.Location, Log.Expected;

这篇关于计算查询中的特定记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！