Max in Query Criteria (1 Viewer)

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
I need how to write criteria in append query for max in number field.

for example

if [Year] field is mentioned last five years 2018,2017,2016,2015,2014 i want query to show records for max value which is 2018 and all records before these should be excluded.

I dont want to make another query to get this hence, i am asking if anyone knows how to do it, i cant use the aggregate because their is more fields also on query.

Please provide any criteria that can perform the task.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:29
Joined
Jan 23, 2006
Messages
15,364
Please provide representative data and examples of starting info and what the intended result should be. It's hard to guess the exact requirement and some of the constraints you have identified. (I dont want to make another query to get this hence, i am asking if anyone knows how to do it, i cant use the aggregate because their is more fields also on query.)
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
I have append query for Employee tblEvaluation and tblincrements and i am appending the rows which is eligible for increment from the evaluation table based on [TotalPoints] >3.4 which i already placed on query design.


The fields which are used in append query is (from tblEvaluation to tblIncrements)

EvaluationID PK of tblEvaluation
EmpID PK of tblEmployees
EvalYear
TotalPoints
Result

Now i need [EvalYear] for max number which is Year, this field only mention the year like 2011 , 2012 i need the latest/recent year, in order to put criteria in [EvalYear] field before append so if anyone have 3 evaluation for year 2011, 2012, 2013 then query will append only the latest year which is 2013 row and not old ones.

for this i need criteria on [EvalYear] to complete the task, i already have criteria on [TotalPoints] >3.4 which will show only the records which is greater than 3.4 in total points.

I cannot include aggregate function because it is not suitable for other fields to select in totals.

Thanks.
 

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
Please demonstrate with data what you hope to achieve. Provide 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough data to show all cases.

B. Expected results of A. Show what data you hope to end up with when you start with the data from A.

Again, 2 sets of data, beginning and expected ending.
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
Ok, to make everybody's life easy i have attached herewith Trim version of my database.

Please note the following that i am trying to do.

1. From tblEvaluation i have created QryEmpEvaluation where i calculated the [TotalPoints] and [Result] fields.

Now i need this to be filled back in tblEvaluation for each EvaluationID regardless of any other criteria, now we have ready with tblEvaluation for all fields, in order to do so i have created QryEvaluationAppend (I dont know why it is not appending to this table).

2. From tblEvaluation once we complete the above task, i have to append the records that meet the following criteria and if its true than that rows should be append to tblIncrements, i also make this query but unless we complete QryEmpEvaluation and QryEvaluationAppend working, i cannot move forward.

This append query criteria is.

from tblEvaluation the field [TotalPoints] is >3.4 and [EvalYear] is with latest value EvalYear is Year i want to append only those fields which is latest year if anyone have 4 evaluations than it should select the latest one by year value [EvalYear] and in the same time it should not duplicate the records.

in both task if it does not meet the criteria than result should be zero.

I hope it is clear to understand.
 

Attachments

  • Test1.zip
    82 KB · Views: 97

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
When you initially talked APPEND queries my poor table structure sensors started to twitch. But I decided to not diagnose that as the issue and instead ask for 2 sets of sample data.

Since you didn't provide that but did offer up a sample of your database, I can now safely say you have improperly set up your tables.

1. tblEvaluations - Storing values in field names. When you name fields after specific things you are doing it wrong. All of those fields that are Evaluated (Attendance, TeamWork, Communication, etc.) should not exist. Instead you should have a table where each of those is a value in a field. Like so:

tblEvaluationElements
ElementID, autonumber, primary key
IDEvaluation, number, foreign key to tblEvaluation
ElementType, text, this will hold all those values that are now in field names (Attendance, Teamwork, etc.)
ElementScore, text, this will hold the value in the field now (eg. Outstanding, Below Expectations, etc)

That new table is where those values should be, not in field names of tblEvaluation

2. tblEvaluation & tblIncrements- Storing calculated values. Values that you can determine from other values in your database shouldn't be stored. Instead, you simply build a query and calculate them there and reference the query when you need the value.

I believe this is the crux of your issue now. You are trying to calculate some value and store it in your tables. That's not what you should do. You should be aiming to build a query to determine those values not have a rube goldberg set of queries that move data hither and yon finally dumping it in the table you want it to exist in.

You need to fix your tables. I believe tblEvaluation is a key element in determining the scores you want, but with the improper structure you have its more difficult than it needs to be.
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
Many thanks plo,g for your shrill but helping words.

I have completed the above task, and its working perfect, only one small issue is their may be field names etc.. but result are perfect.

it is giving me error message for "Enter Parameter Value" for the fields i have in [tblEvaluationPoints]![Catagory] each field pop up separately like "Attendance","SickLeave" etc.


Can you tell me that went wrong in parameter?
 

Attachments

  • Test1.zip
    73.8 KB · Views: 84

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
The reason for all those parameters is because your can't reference a calculated field in the same query you calculate it. [TotalPoints] adds up a bunch of calculated fields (Attendance, Teamwork, etc.).

For educational purposes, this is where I tell you that if you want to do that you would either use the underlying calculations that go into each calculated field for TotalPoints, or remove TotalPoints from this query and create another query using QryEmpEvaluation at its source and then you can reference those calculated fields simply by name there.

However, your entire method is incorrect. In tblEvaluationPoints the Score field needs to be numeric. Instead of Outstanding, Below Expectations, etc. in the table and then converting them to numeric scores in the query (e.g. Attendance=2, Meet Expectations=.5, etc.) you should store the numeric value in the table.

When you do that, your query to get the points for each catagory is a trivial aggregate query:

Code:
SELECT EvaluationID, EmployeeNo, EvalYear, EvalDate, Catagory, SUM(Score) AS CatagoryScore
FROM tblEvaluation INNER JOIN tblEvaluationPoints ON tblEvaluation.EvaluationID = tblEvaluationPoints.EvaluationID
GROUP BY EvaluationID, EmployeeNo, EvalYear, EvalDate, Catagory

IF you don't care about Catagory score and only want TotalPoints, that's even trivialer:


Code:
SELECT EvaluationID, EmployeeNo, EvalYear, EvalDate, SUM(Score) AS TotalPoints
FROM tblEvaluation INNER JOIN tblEvaluationPoints ON tblEvaluation.EvaluationID = tblEvaluationPoints.EvaluationID
GROUP BY EvaluationID, EmployeeNo, EvalYear, EvalDate
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
I am sorry pl.og but you look more confuse than me here, in your previous post you said it should be text fields now you are telling it should be numeric fields, this all because you don't spare time to have look first for what i am missing and what you have to reply or suggest me.

Code:
tblEvaluationElements
ElementID, autonumber, primary key
IDEvaluation, number, foreign key to tblEvaluation
ElementType, text, this will hold all those values that are now in field names (Attendance, Teamwork, etc.)
ElementScore, text, this will hold the value in the field now (eg. Outstanding, Below Expectations, etc)

I cant put numeric values to categories of evaluation like attendance, work load etc because the examiner should not aware how much points he is giving to each category.

Secondly I have to put all this information through form and not directly on table, so setup should be correct to enter these elements with combining all information for each evaluation.

I can remove the totalpoints field and it will work fine as it's already working now, but in tblEvaluationPoints for each EvaluationID I would have 8 rows for Category (Teamwork, Attendance etc) I don't know whether it's correct procedure or not, otherwise I have to redesign the tblEvaluationpoints.

Thanks prolog for your time and help.
 

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
When I provided you my initial recommendations I did not fully understand what you were trying to accomplish (I'm not 100% there right now because you still haven't posted data to demonstrate what the ultimate aim is). What I saw in your database was incorrectly structured tables, regardless of what you were tyring to accomplish. I told you how they should be properly structured without regard for what each field type represented.

Now that the light is getting a little better, I see that since you want to do math on the Score field, it needs to be numeric. So yes, Score needs to be changed to a number in your table.

To your second point, you absolutely can make the field numeric. Using a form to input this data, makes it even easier to do so. On a form, you can have the user view one thing (.e.g. Attendance, Teamwork, etc.) but have its corresponding value go into the table (e.g. 1.5, .5, etc.).

My advice is based on the information that you give me and you've been providing it piecemeal. If you would like to provide the data you hope to ultimately end up so I can see the ultimate aim of all this, that would be very helpful.
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
Please can you tell me the design for tblEvaluation fields and tblEvaluationPoints fields i am confuse here, if i make score to numeric than category fields holds the (Attendance, work load, etc) how i can convert the text score which is (Outstanding, Above expectation, etc) on frmEvaluation to numeric score fields? and also by this way each evaluation will have 8 rows in tblEvaluationPoints all this points i want to agree with you before proceed further.

Please remember i do not want examiner to know how much points he is giving for each category and also on frmEvaluation there will be fields to enter the data that also a issue if design is not prepared correctly.
 

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
Don't freak out, but now I am going to add yet another table and a new field to that table:

tblEvaluationValues
ev_ID, autonumber, primary key
ev_Text, text, will hold what user selects for an evaluation Score (e.g. Outstanding, Below Average, etc.
ev_Score, number, will hold what that evaluation value resolves to via scoring (e.g. .5, 1.5, etc)

That means tblEvaluationPoints changes to this to accomodate that:

tblEvaluationPoints
ep_ID, autonumber, primary key
ep_Catagory, text, catagory to score (Attendance, Communication, etc)
ID_Evaluation, number, foreign key to tblEvaluation
ID_EvaluationValue, number, foreign key to tblEvaluationValues.ev_ID
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
Sorry plog but it still a issue in query to show only Max EvalYear.

I have two evaluation for employees for 2012 and 2015 and i want query to show only latest one which is 2015 but it still showing me both for same employee.
 

Attachments

  • Test1.zip
    83.3 KB · Views: 61

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
When i remove EvaluationID then its giving me MAX on EvalYear but Score is wrong i need score for each year transactions and not sum of total years.


for example in EvalYear i have following

2015 = 1.5
2015 = 2
2015 = -0.5
2018 = 2
2018 = 1.5
2018 = -0.5

then based on MAX EvalYear the score sum should be for EvalYear 2018 = 3

but result is showing 5 summing total for all years which is wrong.

Secondly what will be the procedure to add EvaluationID to his calculation.

Please explain all in one as the thread is getting larger, and i am not professional programmer to understand everything.
 

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
Please demonstrate with data what you hope to occur. Provide 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you hope to end up with.

Again, 2 sets of data.
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

Firstly you have to understand its evaluation process, where examiner will grand each EmpID with Evaluation in this evaluation he will check the employee performance on various subjects like (Attendance, WorkLoad, SickLeaves etc)

Secondly in order to give him marks/score he has to select the grade for him which might be in numbers but i do not want examiner to manipulate the evaluation by seeing the numbers rather than that i make it in text fields which is (Outstanding, Above Expectations, meet expectations, etc)

Sample is attached in scan copy which show the sample data and the result that i want.


B. Expected results of A. Show me what data you hope to end up with.

I have attached the table design and expected result their.
 

Attachments

  • Sample Data.zip
    213.2 KB · Views: 99

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
I can't discern the logic to get the Expected results.

1. Why are EvaluationID=1 and EvaluationID=6 the only 2 records in the expected results?

2. Why does EvaluationID=6 in Expected results have TotalPoints=2? There is only 1 record in tblEvaluationCategory for EvaluationID=6 and it has EP_ID=2 which resolves to a Score=1.5
 

mba_110

Registered User.
Local time
Today, 02:29
Joined
Jan 20, 2015
Messages
280
All results should be as filters based on following.


1. EvalYear:
As i said may be ten times in this post EvalYear should be latest, for example employee no 1 have 3 evaluations for (2011,2012,2013) then ciriteria should select 2013 and leave the 2011,2012 as its old evaluations, hence EvaluationID will be set to latest by MAX.


2. EvaluationID:
Secondly each employee when he goes under evaluation will assign the EvaluationID regardless of out come positive or negative by this EvaluationID each employee retain the history of his evaluations from this we are selecting the latest ones which meet these three criteria (EvalYear, TotalPoints, EvaluationID) as i am listing here, so what evaluation meet this three criteria will obviously have EvaluationID and that will be this EvaluationID.


3. TotalPoints:
Again TotalPoints is outcome of employee evaluation Score or i can say each EvaluationID has TotalPoints regardless by any number, for that we need to select the EvaluationID that meet the above two criteria and plus this one for the EvaluationID that > 3.4 means equal or above 3.5.

These 3 criteria is the filter for each EvaluationID and off course each result should have EvaluationID how you identify it without unique field?
 

plog

Banishment Pending
Local time
Today, 04:29
Joined
May 11, 2011
Messages
11,613
Ok, I see it now. This will require 1 sub query to get the latest EvalYear for each employee:

Code:
SELECT tblEvaluation.EmpID, Max(tblEvaluation.EvalYear) AS LatestEvaluationYear
FROM tblEvaluation
GROUP BY tblEvaluation.EmpID;

Name that 'EvaluationTotal_sub1'. Then you use it in another query to get the results you want:

Code:
SELECT tblEvaluation.EvaluationID, EvaluationTotal_sub1.EmpID, EvaluationTotal_sub1.LatestEvaluationYear, Sum(tblEvaluationPerformance.Score) AS TotalPoints
FROM EvaluationTotal_sub1 INNER JOIN ((tblEvaluation INNER JOIN tblEvaluationCategory ON tblEvaluation.EvaluationID = tblEvaluationCategory.EvaluationID) INNER JOIN tblEvaluationPerformance ON tblEvaluationCategory.EP_ID = tblEvaluationPerformance.EP_ID) ON (EvaluationTotal_sub1.EmpID = tblEvaluation.EmpID) AND (EvaluationTotal_sub1.LatestEvaluationYear = tblEvaluation.EvalYear)
GROUP BY tblEvaluation.EvaluationID, EvaluationTotal_sub1.EmpID, EvaluationTotal_sub1.LatestEvaluationYear;

Let me know if you find any issues.
 

Users who are viewing this thread

Top Bottom