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
21 2514
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? - SELECT LOG.Name, LOG.DayType, Sum(LOG.NoOfPoints) AS SumOfNoOfPoints, Avg(LOG.NoOfPoints) AS AvgOfNoOfPoints, Count(LOG.DayType) AS CountOfDayType
-
FROM LOG
-
WHERE LOG.[Date of Service] Between #1/1/206# And #12/31/2006#
-
GROUP BY LOG.Name, LOG.DayType
-
HAVING LOG.DayType="Scheduled";
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;
You can't have expected and sum of expected outputting in the same query as this is a contradiction.
Do you want the calculation by Clinician and Location or just Clinician?
Why are you using DISTINCTROW?
Do you want the points averaged by distinct log date or by some other date?
If you can answer the above I will try to put the query together for you.
Mary
I created the query in the design view. Due to my lack of experience and knowledge with SQL, I am not sure why it says DISTINCTROW. Sorry :(
I have expected and sum of expected for the following reasons:
I need the actual expected that remains constant for the clinician to calculate a...if less than... use expected..expression in field [calc]. I need the sum of expected which totals the expected less the nonwork day expected to be used to calculate a percentage in the same query. The reason I set it up that way with [expected] and then [nonworkexpected] was to zero out the expected. If I manually entered "0.00" in [expected] in my table. When I ran the query, it would give me two groupings for each clinician. Does that make sense?
I would like the data organized by clinician. I tried to remove the group by on location and expected fields in the query and received an error. So, I left them.
Lastly, I need to total all points in the DOCS, DOMHA, EAP fields for all records in the cycle. However, I need to count the number of days in the cycle that have value "scheduled" in the [daytype] field of my table [log].
Did that answer your question or make the waters muddier???
Thanks!
OK, try this and tell me what's working and what's not: - SELECT Clinician, First([Expected]) As Expected,
-
Expected-First([Non Work Day Expected]) AS SumExpected,
-
Sum(DOMHA+DOCS+EAP+[NRP Points]) AS SumEarned, Sum(PTO) AS SumPTO,
-
Sum(IIf([daytype]="scheduled",1,0)) As CountScheduled,
-
SumEarned/CountScheduled AS AvgEarn, IIf(AvgEarn>Expected,Expected,AvgEarn) AS Calc,
-
Calc*SumPTO AS PTOPoint, Sum([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.Date;
Mary
NeoPa 32,556
Expert Mod 16PB
This sort of stuff is very hard to get right without the actual tables to run it past.
I noticed a circular reference in Mary's SQL so I'm posting a slightly different version. This doesn't change the intended effect, just the SQL used to get there. Another way (to avoid repeating First(Log.Expected) all the time) would be to name it something like FstExpected or something. This could then be used throughout the rest of the query. - SELECT Clinician, First(Log.Expected) As Expected,
-
First(Log.Expected-[Non Work Day Expected]) AS SumExpected,
-
Sum(DOMHA+DOCS+EAP+[NRP Points]) AS SumEarned,
-
Sum(PTO) AS SumPTO,
-
Sum(IIf([daytype]='scheduled',1,0)) As CountScheduled,
-
SumEarned/CountScheduled AS AvgEarn,
-
IIf(AvgEarn>First(Log.Expected),
-
First(Log.Expected),AvgEarn) AS Calc,
-
Calc*SumPTO AS PTOPoint, Sum([Admin]) AS SumAdmin,
-
SumAdmin*Calc AS AdminPoint,
-
SumEarned+PTOPoint+AdminPoint AS TotPoints,
-
TotPoints/SumExpected AS Productivity
-
FROM Log
-
WHERE (Log.Date Between
-
[Forms]![Report Date Range]![Beginning Date] And
-
[forms]![Report Date Range]![Ending Date])
-
GROUP BY Log.Clinician, Log.Date;
Thank you for your help. It is still not right. For the last query given, I have errors for values and it is showing each individual record rather than giving me one grand total for all records during the specified date range.
Should I paste my Table?
Thank you for your help. It is still not right. For the last query given, I have errors for values and it is showing each individual record rather than giving me one grand total for all records during the specified date range.
Should I paste my Table?
Putting in the table schema would be useful. However, trying to count the unique dates for the average calculation and suming the total points over the date range will involve the use of sub queries so I'll leave that to NeoPa as he's better at those than I am :)
Can you tell us which values are showing errors?
Mary
NeoPa 32,556
Expert Mod 16PB
...so I'll leave that to NeoPa as he's better at those than I am :)
Thanks :) (I think :confused: )
Ever feel like you've been slipped a sly one?
Thanks :) (I think :confused: )
Ever feel like you've been slipped a sly one?
Now would I do that to you Ade :D
Ok - In hopes of reducing the number of calculations that need to be done in the query, I reduced the number of fields in my table. I am going to list the fields in the table, list the fields and their functions for the query, the current SQL for the query, and hopefully this will help :? Sorry for the change.
Table . SERVICELOG
ID - Primary Key
Clinician - text field
Location - text field
Date
Expected - number field
DOMHA - number field
DOCS - number field
EAP - number field
NRP - number field
PTO - number field - can only select "0", ".5" or "1"
Admin - number field - can only select "0", ".5" or "1"
DayType - text field - can only selected "scheduled", "non-scheduled", "admin"
For ServiceLogQuery
I need the following
Grouped by Clinician for specified date range
Clinician
Date - enter criteria
MaxExpect: Max value in [expected] for individual clinician
SumExpect: Sum [expected]
CountScheduled: Sum number of days = "scheduled" in [daytype]
SumEarn: Sum[DOMHA]+ [DOC]+[EAP]+[NRP]
AvgEarn: [SumEarn]/[WorkCount]
CalcPoint: IIF ([AvgEarn]>[Expected], [expected],[AvgEarn])
SumPTO: Sum [PTO]
SumAdmin: Sum [Admin]
PTOPoint: [SumPTO]*[CalcPoint]
AdminPoint: [SumAdmin]*[CalcPoint]
TOTPoints: [SumEarn]+[PTOPoint]+[AdminPoint]
Productivity: [TOTPoints]/[SumExpect]
CURRENT QUERY IN SQL -
SELECT servicelog.Clinician, Max((IIf([expected]<0,[Expected],0))) AS MaxExpect,
-
Sum(servicelog.Expected) AS SumExpect,
-
Sum(IIf([daytype]="Scheduled",1,0)) AS CountScheduled,
-
Sum([DOMHA]+[DOCS]+[EAP]+[NRP]) AS SumEarn,
-
[SumEarn]/[CountScheduled] AS AvgEarn,
-
IIf([AvgEarn]<[MaxExpect],[MaxExpect],[AvgEarn]) AS CalcPoint,
-
Sum(servicelog.PTO) AS SumPTO, Sum(servicelog.Admin) AS SumAdmin,
-
[SumPTO]*[CalcPoint] AS PTOPoint, [SumAdmin]*[CalcPoint] AS AdminPoint,
-
Sum([PTOPoint]+[AdminPoint]+[SumEarn]) AS TOTPoints,
-
[TOTPoints]/[SumExpect] AS Productivity
-
FROM servicelog
-
WHERE (((servicelog.Date)>=[forms]![Report Date Range]![Beginning Date]
-
And (servicelog.Date)<=[forms]![Report Date Range]![Ending Date]))
-
GROUP BY servicelog.Clinician;
-
All of that said – I am receiving the following error -
“Subqueries cannot be used in the expression” 9sum9[sumPTO]*iif([sumearn]/[countscheduled]<[MaxExpect],
-
[maxepect],[sumearn]/[countscheduled])[sumadmin]*iif([sumearn]/[countscheduled],
-
[maxexpect],[maxexpect],[sumearn]/[countscheduled]) + [sumearn]).
-
NeoPa 32,556
Expert Mod 16PB
Your specification for ServiceLogQuery includes a field [WorkCount] which isn't defined anywhere. This will prompt the operator to enter a value. In your existing SQL you use [CountScheduled] here in place of [WorkCount] so I will propagate that through. Try this and let us know how you get on : - SELECT Clinician,
-
[Date],
-
Max([Expected]) AS MaxExpect,
-
Sum([Expected]) AS SumExpect,
-
Sum(IIf([DayType]='Scheduled',1,0)) AS CountScheduled,
-
Sum([DOMHA]+[DOCS]+[EAP]+[NRP]) AS SumEarn,
-
[SumEarn]/[CountScheduled] AS AvgEarn,
-
IIf([AvgEarn]<[MaxExpect],[AvgEarn],[MaxExpect]) AS CalcPoint,
-
Sum([PTO]) AS SumPTO,
-
Sum([Admin]) AS SumAdmin,
-
[SumPTO]*[CalcPoint] AS PTOPoint,
-
[SumAdmin]*[CalcPoint] AS AdminPoint,
-
[PTOPoint]+[AdminPoint]+[SumEarn] AS TOTPoints,
-
[TOTPoints]/[SumExpect] AS Productivity
-
FROM ServiceLog
-
WHERE [Date] Between [forms]![Report Date Range]![Beginning Date]
-
And [forms]![Report Date Range]![Ending Date]
-
GROUP BY Clinician,[Date];
*Pulling out hair*
First, thank you so much for all of your help with the SQL writing. It worked and I did a huge happy dance. The happiness ended when I came to work and found out that there was a change from yesterday and now I need to subtract from the [sumexpected] the points assigned to [PTOPoint] and [AdminPoint]. I attempted to add this function in the design view of my query and I am back to receiving the subquery error.
Why do I receive the error and what should I do to prevent it? I have the Access for dummies and the Microsoft Access 2000 book and neither are very helpful in explaining subqueries why and how to use or change??
Current Code: - SELECT Clinician,
-
[Date],
-
Max([Expected]) AS MaxExpect,
-
Sum([Expected]) AS SumExpect,
-
Sum(IIf([DayType]='Scheduled',1,0)) AS CountScheduled,
-
Sum([DOMHA]+[DOCS]+[EAP]+[NRP]) AS SumEarn,
-
[SumEarn]/[CountScheduled] AS AvgEarn,
-
IIf([AvgEarn]<[MaxExpect],[AvgEarn],[MaxExpect]) AS CalcPoint,
-
Sum([PTO]) AS SumPTO,
-
Sum([Admin]) AS SumAdmin,
-
[SumPTO]*[CalcPoint] AS PTOPoint,
-
[SumAdmin]*[CalcPoint] AS AdminPoint,
-
Sum([PTOPoint]+[AdminPoint]) AS AdminPTO,
-
Sum([SumExpected]-[AdminPTO]) AS TotExpected,
-
[PTOPoint]+[AdminPoint]+[SumEarn] AS TOTPoints,
-
[TOTPoints]/[SumExpect] AS Productivity,
-
Location
-
FROM ServiceLog
-
WHERE [Date] Between [forms]![Report Date Range]![Beginning Date]
-
And [forms]![Report Date Range]![Ending Date]
-
GROUP BY Clinician,Location;
Receiving Error:
Subqueries cannot be used in the expression: -
(Sum([sumpto]*iif([Sumeearn]/[countscheduled]<[maxexpect],
-
[sumearn]/[countscheduled],[maxexpect])+[sumadmin]*
-
iif([sumeearn]/[contscheduled]<[maxexpect],[sumearn]/
-
[countscheduled],[maxexpect])))
-
NeoPa 32,556
Expert Mod 16PB
Jennifer,
I know it's gonna be work for you, but could you post your SQL in the code tags and formatted as I had it in my posts. Otherwise trying to decipher it is a bit like swimming through treacle.
I will do it for you again this time but please, in future, try to make it a little less work for me.
In the mean-time, while I'm looking at this, check out Subqueries in SQL.
Jennifer,
I know it's gonna be work for you, but could you post your SQL in the code tags and formatted as I had it in my posts. Otherwise trying to decipher it is a bit like swimming through treacle.
I will do it for you again this time but please, in future, try to make it a little less work for me.
In the mean-time, while I'm looking at this, check out Subqueries in SQL.
Just done it Ade:
But I agree Jenn you have to try to do it.
Mary
NeoPa 32,556
Expert Mod 16PB
Lol - I just did too :)
I went to update and saw code tags there which confused me horribly - I understand now ;)
NeoPa 32,556
Expert Mod 16PB
Thanks for doing that last update too Mary.
I was just building up a long and involved answer that found a bunch of probs with the SQL when my PC crashed without saving the changes :Grrrr:
Will try to do it all again tonight.
NeoPa 32,556
Expert Mod 16PB
Current Code: - 1 SELECT Clinician,
-
2 [Date],
-
3 Max([Expected]) AS MaxExpect,
-
4 Sum([Expected]) AS SumExpect,
-
5 Sum(IIf([DayType]='Scheduled',1,0)) AS CountScheduled,
-
6 Sum([DOMHA]+[DOCS]+[EAP]+[NRP]) AS SumEarn,
-
7 [SumEarn]/[CountScheduled] AS AvgEarn,
-
8 IIf([AvgEarn]<[MaxExpect],[AvgEarn],[MaxExpect]) AS CalcPoint,
-
9 Sum([PTO]) AS SumPTO,
-
10 Sum([Admin]) AS SumAdmin,
-
11 [SumPTO]*[CalcPoint] AS PTOPoint,
-
12 [SumAdmin]*[CalcPoint] AS AdminPoint,
-
13 Sum([PTOPoint]+[AdminPoint]) AS AdminPTO,
-
14 Sum([SumExpected]-[AdminPTO]) AS TotExpected,
-
15 [PTOPoint]+[AdminPoint]+[SumEarn] AS TOTPoints,
-
16 [TOTPoints]/[SumExpect] AS Productivity,
-
17 Location
-
18 FROM ServiceLog
-
19 WHERE [Date] Between [forms]![Report Date Range]![Beginning Date]
-
20 And [forms]![Report Date Range]![Ending Date]
-
21 GROUP BY Clinician,Location;
Lines 13 & 14 use the Sum() function around previously summed values. This is like Summing squared and is not supported (nor does it make sense in this context). - [PTOPoint]+[AdminPoint] AS AdminPTO,
-
[SumExpected]-[AdminPTO] AS TotExpected,
Line 21 has had the Location field added but the [Date] field has been lost. - GROUP BY Clinician,[Date],Location;
So the code to try is : - SELECT Clinician,
-
[Date],
-
Max([Expected]) AS MaxExpect,
-
Sum([Expected]) AS SumExpect,
-
Sum(IIf([DayType]='Scheduled',1,0)) AS CountScheduled,
-
Sum([DOMHA]+[DOCS]+[EAP]+[NRP]) AS SumEarn,
-
[SumEarn]/[CountScheduled] AS AvgEarn,
-
IIf([AvgEarn]<[MaxExpect],[AvgEarn],[MaxExpect]) AS CalcPoint,
-
Sum([PTO]) AS SumPTO,
-
Sum([Admin]) AS SumAdmin,
-
[SumPTO]*[CalcPoint] AS PTOPoint,
-
[SumAdmin]*[CalcPoint] AS AdminPoint,
-
[PTOPoint]+[AdminPoint] AS AdminPTO,
-
[SumExpected]-[AdminPTO] AS TotExpected,
-
[PTOPoint]+[AdminPoint]+[SumEarn] AS TOTPoints,
-
[TOTPoints]/[SumExpect] AS Productivity,
-
Location
-
FROM ServiceLog
-
WHERE [Date] Between [forms]![Report Date Range]![Beginning Date]
-
And [forms]![Report Date Range]![Ending Date]
-
GROUP BY Clinician,[Date],Location;
Thanks for doing that last update too Mary.
I was just building up a long and involved answer that found a bunch of probs with the SQL when my PC crashed without saving the changes :Grrrr:
Will try to do it all again tonight.
Just love it when that happens, don't you.
NeoPa 32,556
Expert Mod 16PB
Not a lot! I have to say :(
It's hard to force yourself to do all the work again - especially in as much detail.
I think it should suffice to move forward though :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Reply via newsgroup |
last post by:
Folks,
When performing an update in mysql (using PHP), can I find out how many
records were matched?
mysql_affected_rows() won't work... and I have the following problem
that I thought I...
|
by: Chad Reid |
last post by:
Okay, I have a query that generates a list of ClientID's that I want
included in a count. Take a look at the following query and make any
changes that you see fit to make it operational.
SELECT...
|
by: Skully Matjas |
last post by:
I am using the following code (created by the wizard) to
allow to bring my form to a particular entery. But when I
edit the entery (ex: put new information into a blank
cell), it puts that record...
|
by: Glenn Cornish |
last post by:
I have 5 fields in a table into which numbers between 1 and 45 can be
entered. What I am having trouble with is being able to find out is how
many times a particular number appears, regardless of...
|
by: ChadDiesel |
last post by:
Hello Again,
I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to...
|
by: ChadDiesel |
last post by:
I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other...
|
by: remove |
last post by:
Hi all,
I have a fairly simple database containing details of broadcast news
reports. Each record contains information about the programme title, date,
time, presenter, and details of the...
|
by: darrel |
last post by:
I have a userControl that displays a repeaterControl to display data. I have
this set up so that it pages, 10 records per page.
This works fine.
Now, for some of the data, it'd be nice if the...
|
by: BillH |
last post by:
As new Records are added I need to in a Query, count the number of
occurrences of several specific Fields that have change from the
previous record.
As a reference point, each Record is unique for...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |