Select highest values

[Brogrim]

I have a query that returns 2 records, each record has a field called

memberid which is an integer.

I want the query to return the highest memberid.

am i correct in thinking i could use the min max function

 

[lespaul]

yes, you use the max function

SELECT MAX(MEMBERID) FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Brogrim]

Thanks for the tip.

I am in design view of a query

where does MAX(MEMBERID) go.

If I put it in criteria

 

[lespaul]

Sorry I don't work in the Access Design Grid, so I don't know. You can start the query in the design grid (add the field you need) and then switch to the SQL view, add the Max function to the SQL and switch back. That should convert it in the design grid for you.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Brogrim]

Thanks

I am unsure where it goes, here is the sql.


SELECT [SecondName] & " " & [FirstName] AS NameDOB, tblMember.DOB, tblMember.BadgeHolder, tblMember.Member, tblMember.MemberID, tblMember.DateEntered, tblMember.AssesmentDate, tblMember.ReferralDate, tblMember.CommencementOfService, tblMember.TitleID, tblMember.FirstName, tblMember.SecondName, tblMember.GenderID, tblMember.MemStatusID, tblMember.Spokeout, tblMember.HouseNo, tblMember.HouseLetter, tblMember.HouseName, tblMember.Address1, tblMember.Address2, tblMember.Address3, tblMember.CountyID, tblMember.PostCode, tblMember.PostCodePrefix, tblMember.PSDSID, tblMember.AgeGroupID, tblMember.ROCID, tblMember.Telephone, tblMember.Email, tblMember.PrimaryDiagnosticID, tblMember.SecondaryDiagnosticID, tblMember.DisabilityLevelID, tblMember.DisabilityNotes, tblMember.HealthBoardID, tblMember.ComCareAreaID, tblBadge.DateofIssue, tblBadge.DateOfExpiry, tblBadge.BadgeNo, tblBadge.MakeOfCar, tblBadge.[Reg No], tblBadge.Color, tblBadge.ChassisNo, tblBadge.YearofIssue, tblBadge.PrimaryMedicalCertificate, tblBadge.BlindRegistration, tblBadge.[2Year], tblBadgeComments.Date, tblBadgeComments.Comments, tblBadgeComments.BadgeStatusID, tblBadgeComments.GardaRef

FROM (tblMember INNER JOIN tblBadge ON tblMember.MemberID = tblBadge.MemberID) INNER JOIN tblBadgeComments ON tblBadge.BadgeNo = tblBadgeComments.BadgeNo

WHERE ((([SecondName] & " " & [FirstName])=[Forms]![frmSwitchboard]![cboName]) AND ((tblMember.DOB)=[Forms]![frmSwitchboard]![cboDOB] And (tblMember.DOB) Like "*") AND ((tblMember.BadgeHolder)<>0));

 

[lespaul]

assuming you want the highest MemberID number as stated in your OP:

SELECT [SecondName] & " " & [FirstName] AS NameDOB, tblMember.DOB, tblMember.BadgeHolder, tblMember.Member, MAX(tblMember.MemberID), tblMember.DateEntered, tblMember.AssesmentDate, tblMember.ReferralDate, tblMember.CommencementOfService, tblMember.TitleID, tblMember.FirstName, tblMember.SecondName, tblMember.GenderID, tblMember.MemStatusID, tblMember.Spokeout, tblMember.HouseNo, tblMember.HouseLetter, tblMember.HouseName, tblMember.Address1, tblMember.Address2, tblMember.Address3, tblMember.CountyID, tblMember.PostCode, tblMember.PostCodePrefix, tblMember.PSDSID, tblMember.AgeGroupID, tblMember.ROCID, tblMember.Telephone, tblMember.Email, tblMember.PrimaryDiagnosticID, tblMember.SecondaryDiagnosticID, tblMember.DisabilityLevelID, tblMember.DisabilityNotes, tblMember.HealthBoardID, tblMember.ComCareAreaID, tblBadge.DateofIssue, tblBadge.DateOfExpiry, tblBadge.BadgeNo, tblBadge.MakeOfCar, tblBadge.[Reg No], tblBadge.Color, tblBadge.ChassisNo, tblBadge.YearofIssue, tblBadge.PrimaryMedicalCertificate, tblBadge.BlindRegistration, tblBadge.[2Year], tblBadgeComments.Date, tblBadgeComments.Comments, tblBadgeComments.BadgeStatusID, tblBadgeComments.GardaRef

FROM (tblMember INNER JOIN tblBadge ON tblMember.MemberID = tblBadge.MemberID) INNER JOIN tblBadgeComments ON tblBadge.BadgeNo = tblBadgeComments.BadgeNo

WHERE ((([SecondName] & " " & [FirstName])=[Forms]![frmSwitchboard]![cboName]) AND ((tblMember.DOB)=[Forms]![frmSwitchboard]![cboDOB] And (tblMember.DOB) Like "*") AND ((tblMember.BadgeHolder)<>0))

GROUP BY [SecondName] & " " & [FirstName], tblMember.DOB, tblMember.BadgeHolder, tblMember.Member,tblMember.DateEntered, tblMember.AssesmentDate, tblMember.ReferralDate, tblMember.CommencementOfService, tblMember.TitleID, tblMember.FirstName, tblMember.SecondName, tblMember.GenderID, tblMember.MemStatusID, tblMember.Spokeout, tblMember.HouseNo, tblMember.HouseLetter, tblMember.HouseName, tblMember.Address1, tblMember.Address2, tblMember.Address3, tblMember.CountyID, tblMember.PostCode, tblMember.PostCodePrefix, tblMember.PSDSID, tblMember.AgeGroupID, tblMember.ROCID, tblMember.Telephone, tblMember.Email, tblMember.PrimaryDiagnosticID, tblMember.SecondaryDiagnosticID, tblMember.DisabilityLevelID, tblMember.DisabilityNotes, tblMember.HealthBoardID, tblMember.ComCareAreaID, tblBadge.DateofIssue, tblBadge.DateOfExpiry, tblBadge.BadgeNo, tblBadge.MakeOfCar, tblBadge.[Reg No], tblBadge.Color, tblBadge.ChassisNo, tblBadge.YearofIssue, tblBadge.PrimaryMedicalCertificate, tblBadge.BlindRegistration, tblBadge.[2Year], tblBadgeComments.Date, tblBadgeComments.Comments, tblBadgeComments.BadgeStatusID, tblBadgeComments.GardaRef;

Since you are using an aggregate function (MAX, SUM, COUNT), you must have a GROUP BY clause in the query. Every field in the SELECT statement that IS NOT an aggregate MUST be included in the GROUP BY clause.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design