Finding the highest value

[noahaz]

I am trying to find the highest value I have a site_number and a site_program_year. I need to find the highest site program year for each site number. I was told to use this DMAX("site_program_year","tblsss","site_number=" & "****")but all this is doing is pulling up the highest site program year.
Could someone please give me some help??

 

[dhookom]

Is this in code or a query or a control source or what? Is Site_Number numeric or text?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[noahaz]

It is a query and site number is numeric.

 

[dhookom]

I would think you would set up a column/field with the expression:
MaxYear: DMAX("site_program_year","tblsss","site_number=" & [Site_Program_Year])

If that doesn't work, come back with some actual specifications with table and field names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjonesal]

Use a group query such as:

SELECT Table2.SiteNumber, Max(Table2.SiteProgramYr) AS MaxOfSiteProgramYr
FROM Table2
GROUP BY Table2.SiteNumber;

You can paste it into a new query based on the underlying table using SQL View (assuming you change the table/field names)

or

in Design View, create a group query (using the Totals button on the toolbar - that looks like a Greek Sigma symbol), and add the following:

Two columns:

1. Site Number - Select Group By in the Total row
2. Site Number Year - Select Max in the Total row

Hope this helps.

j

 

[noahaz]

Using MaxYear: DMAX("site_program_year","tblsss","site_number=" & [Site_Program_Year])
I get an invalid . or! operator or invalid parentheses error

 

[jjonesal]

In this part you compare site_number to the year:

site_number=" & [Site_Program_Year]

Those fields will won't be the same.

Is that really what you want to do - those are different fields? right?

j

 

[dhookom]

noahaz,
Can you just tell us what you have and what you want to do?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[noahaz]

I am trying to run create a query that will pull the highest site program year for each site number. I have created a query with all the fields that I need and just need to build a expression to pull the highest site program year from each site number.

 

[dhookom]

It isn't clear if you want all fields or just the site number and site year. Assuming additional fields, try something like:

SELECT *
FROM tblsss
WHERE Site_Program_Year IN 
  (SELECT Max(site_program_year)
   FROM tblsss s
   WHERE s.Site_Number = tblsss.Site_Number);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[noahaz]

I see what you are talking about now.
I thought it would be much simpler than this sorry for my ignorance.
I have two tables joined by site number and emp id,
IN tblSites I have
The fields I want to include in my query are
EMpID tblSites
Site_Number tblsss
EmpName tblsites
SiteName tblsites
Adressline tblsites
NumberOfemployees tblsites
covtrips tblsss
covmiles tblsss
site_program year

I am trying to run create a query that will pull the highest site program year for each site number which would include these fields from both tables.
Thanks

 

[dhookom]

Are there possible duplicates of Site Program Year per Site Number in tblsss? Isn't Site Number in both tables? I assume site_Program year is in tblsss.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[noahaz]

site number and site program year are in tblsss
and site number is in tblsites

 

[PHV]

What about this ?

SELECT A.EMpID, A.Site_Number, A.EmpName, A.SiteName, A.Adressline, A.NumberOfemployees, B.covtrips, B.covmiles, B.[site_program year]
FROM (tblSites AS A
INNER JOIN tblsss AS B ON A.Site_Number = B.Site_Number)
INNER JOIN (SELECT Site_Number, Max([site_program year]) AS LastYear
FROM tblsss GROUP BY Site_Number
) AS C ON B.Site_Number = C.Site_Number AND B.[site_program year] = C.LastYear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noahaz]

PHV when I enter this code it asks for a parameter value for each field. I want it to find the highest program year # for each site.

 

[PHV]

What is YOUR actual SQL code ?
What is the whole content of the input box asking for a parameter value ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noahaz]

SELECT tblSites.EmpId, tblSites.SiteNumber, tblSites.EmpName, tblSites.AddressId, tblSSS.sov_trips_percent, tblSSS.sov_miles_percent, tblSSS.site_program_year
FROM tblSites INNER JOIN tblSSS ON tblSites.EmpId = tblSSS.emp_id
WHERE (((tblSSS.site_program_year)=DMax("site_program_year","tblsss","site_number=" & [Site_Program_Year])));

This was the code before entering your code.

 

[noahaz]

With that code I get Syntax error (missing operator) in query expression 'site_number='

 

[noahaz]

When I put in your code I get microsoft access can't represent the join expression a.site_number = b.site-number in design view.
one or more fields may have been deleted or renamed
the name of one or more fields or tables specified in the join expression may be misspelled
the join may use and operator that isn't supported in design view
same with b.site =b.site
SELECT A.EmpId, A.Site_Number AS Expr1, A.EmpName, A.SiteName, A.Adressline AS Expr2, A.NumberOfEmployees, B.covtrips AS Expr3, B.covmiles AS Expr4, B.[site_program year] AS Expr5
FROM tblSites AS A, tblsss AS B INNER JOIN [SELECT Site_Number, Max([site_program year]) AS LastYear
FROM tblsss GROUP BY Site_Number
]. AS C ON B.site_number = C.Site_Number;

when I run this a enter parameter value dialogue box pops for site program year, a.site number,a.aderessline, b.covtrips, b.covmiles, b.site program year,