Using DMax Function in a query

[KristenD]

I am having trouble getting DMax Function to work in a query.

I am looking to pull the most recent ID for one table. The DMax function will be used in 2 queries.

Currently this is what the query looks like without the DMax function:

SELECT tblEmp.EmployeeID, tblEmp.EmployeeName, tblEmpInfo.EmpInfoID, tblEmpInfo.JobNumberFK, tblJobs.JobSite, tblEmpInfo.EmpType, tblEmpInfo.CraftCode, tblLicensing.LicenseID, tblLicensing.License, tblLicensing.Category, tblLicensing.Qualifications, tblLicensing.ExpirationDate, tblLicensing.CertificateNum, tblLicensing.State
FROM tblJobs INNER JOIN ((tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK) INNER JOIN tblLicensing ON tblEmp.EmployeeID = tblLicensing.EmpIDFK) ON tblJobs.JobNum = tblEmpInfo.JobNumberFK;

I need to pull the most recent EmpInfoID. I had a column for the DMax function in the query but it would not run. I had Expr1: DMax ("EmpInfoID").

Please tell me what I am doing wrong!

 

[pbaldy]

For starters, that not the correct syntax:

http://access.mvps.org/access/general/gen0018.htm

This may be an alternative if it's what you're trying to do:

http://www.baldyweb.com/LastValue.htm

 

[Steve R.]

Since you are only requesting the retrieval of one number, why not use DMAX directly? Once you retrieve that number, it can be utilized as a variable in each query.

The code below would not be a solution, but a guide on syntax usage since DMAX and DCOUNT use a similar structure. My SQL skills are limited. I just recently, as an experiment, incorporated DCOUNT into an SQL sting.

strDcountCriteria = "DCount(""RecordIDnum"",""dbo_InspectionTable"",""Rep1Num = "" & [RepsNameIDnum])"
    Set BuildComboBoxRS = CurrentDb.OpenRecordset("SELECT * , " & strDcountCriteria & "  AS expr1, * " _
         & " FROM dbo_RepsList " _
         & " WHERE (((" & strDcountCriteria & "))> 0) " _
         & " ORDER BY dbo_RepsList.RepsName;", dbOpenDynaset, dbSeeChanges)

PS: pbaldy advice is more to the point.

 

[KristenD]

So I need to have 2 queries and then join them. How do I get it to look at an autonumber?

The field that I need is set up as an autonumber. I guess I'm confused on what to call it in the syntax. I am still fairly new at writing codes for queries.

Thank you!!!

 

[Steve R.]

The field that I need is set up as an autonumber.

Take a look at the design of the table. There should be one field were the data type is "AutoNumber". The name of that field is what you would bee looking up with DMAX.

=DMax("AutoIncrementName", "TableName", "strCriteria")

 

[KristenD]

Would the string criteria be Me.EmpInfoID?

I am having a hard time understanding what the last part is.

It is still giving me an error too in the query with the Expr1: column for the DMAX function as well.

If I'm understanding correctly (and I'm probably not) is in the criteria of the recordset I'm looking for (in this case EmpInfoID) I put the DMax function of

DMax=("EmpInfoID", "tblEmpInfo", "Me.EmpInfoID")

But when I look on line it looks like some of it is done in VBA, other's in SQL in the query, and I have yet to find an example where it is done in the actual query itself without using the SQL view.

Is what I have correct? I am sorry for being so thickheaded! :banghead:

 

[KristenD]

Ok, after spending more time researching I think I understand. I have played around with the query and get it to pull certain records but I can't get it to do what I want it to do.

I am looking for the query to return the largest/highest number for an employee ID. For example, an employee that is currently active has been on one job, he transferred to another job 6 months ago, 3 months ago he transferred again and now he transferred today. So in this table it shows that he is active on 4 jobs. But I only want to show that most recent entry. Instead what is currently happening in the query/report is that he is active on all 4 jobs.

The advice here was to use the DMAX function to pull the highest number for the EmpInfoID as that will be the most recent entry for that employee.

Here is what I am struggling with now. I don't know how to write in there for access to make it pull only that highest number for all the employees. In my messing around with the function I can get it to pull a certain number but that messes up the whole query/report as instead of giving me ALL employees it only pulls that specific EmpInfoID number.

What do I put in the last part of the criteria?

 

[pbaldy]

I think you'll find the query method I posted is more efficient than having a domain aggregate function in the query, but:

DMax("EmpInfoID", "tblEmpInfo", "EmployeeID = " & EmployeeID)

 

[KristenD]

I had played around with the method on your website but I'm afraid I didn't quite understand it to get it to work correctly.

So what you have wrote would go in the critera of EmpInfoID or a separate field?

 

[KristenD]

I am thoroughly confused now. It still has duplicate entries for all employees that have transferred jobs and it has a number in the query that I don't know where it came from.

 

[KristenD]

The query I am working on is qryOSHAJob.

Can someone please look at it and tell me what is wrong.

In that field, the DMax expression comes back with a number of 330. I don't know how or why it comes back that way.

But what I am looking for it to do, is for each EmpIDFK I want it to pull the most recent or largest EmpInfoID. Each EmpIDFK will have multiple entries under it with the EmpInfoID, I just want the largest one.

It may make more sense to put a field in there for DateOnJob and pull the info that way too. Would that make more sense? Again sorry to be so thickheaded! Thank you in advance!

 

[KristenD]

Pbaldy,

I tried to do what you had suggested and it the query will not run. I'm assuming I'm not using the correct syntax?

SELECT Max(tblEmpInfo.EmpInfoID) AS MaxDateEmpInfoID, tblEmpInfo.EmpInfoID
FROM tblEmpInfo
GROUP BY tblEmpInfoID.EmpIDFK

Please help!! Thank you!!

 

[Adam Caramon]

But what I am looking for it to do, is for each EmpIDFK I want it to pull the most recent or largest EmpInfoID. Each EmpIDFK will have multiple entries under it with the EmpInfoID, I just want the largest one.

Sounds like you're trying to do something similar to this:

SELECT tblEmp.EmployeeName, tblEmp.EmploymentStatus, Max(tblEmpInfo.EmpInfoID) AS MaxOfEmpInfoID, tblEmpInfo.EmpIDFK
FROM tblEmp INNER JOIN tblEmpInfo ON tblEmp.EmployeeID = tblEmpInfo.EmpIDFK
GROUP BY tblEmp.EmployeeName, tblEmp.EmploymentStatus, tblEmpInfo.EmpIDFK;

Understand, however, that when you add delineating fields, that the query will break down. Each unique individual is only unique as long as none of the additional fields you pull in have different values.

If you have a need to pull of this information in at the same time, you may need to reconsider your database design.

In other words, why do you need to have this query? What is its purpose? There's probably a better way to accomplish what you're trying to do.

 

[KristenD]

I will be running 2 reports each week using the most recent information in the tblEmpInfo (OSHA by Job report and a Licensing Log).

In the tblEmpInfo the JobNumFK and CraftCode may change. For example, EmpIDFK 4978 was at C43205 but then transferred to C43185. Currently that information is showing in both spots. The employee is showing up twice on the report when I only want them to show up on the most recent entry which would be the C43185 job.

I hope I am explaining this good enough.

 

[pbaldy]

Pbaldy,

I tried to do what you had suggested and it the query will not run. I'm assuming I'm not using the correct syntax?

SELECT Max(tblEmpInfo.EmpInfoID) AS MaxDateEmpInfoID, tblEmpInfo.EmpInfoID
FROM tblEmpInfo
GROUP BY tblEmpInfoID.EmpIDFK

Please help!! Thank you!!

I think you have the fields goofed. I'm not sure what the correct fields are, but since it sounds like you don't have a date field:

SELECT Max(AutonumberField) AS MaxDateEmpInfoID, FieldThatIdentifiesTheEmployee
FROM tblEmpInfo
GROUP BY FieldThatIdentifiesTheEmployee

 

[KristenD]

There is no date field in the table. Although I am thinking of adding one to better be able to track when these employees came and went so I can see a history by date rather than the auto number.

Although, that table should match up by Date to the tblEmpRating.

Would it make sense to have that date from that table in both forms?

Thank you, Adam that worked perfectly!!