SQL statement in one table

[af193]

Hello;


I am new with MS Access and MS Access is the only tool I have now. I want to write a select SQl statement where the output is the maximum effective date of all records.


The data lay out as below

TABLE

ID EFFDT
1 25-05-2015
1 18-04-2015
1 22-05-2015
2 12-03-2015
2 11-02-2015
2 10-01-2016

I want the output as below:

ID EFFDT
1 25-05-2015
2 10-01-2016

I would write the sql like below (Oracle SQL standard ) but syntax is not accept by MS Access. How to translate this SQl into MS Access acceptable .

/* note that there is only one table name = TABLE , in Oracle SQL language A & B here are used as reference
Select A.ID, A.EFFDT from TABLE A where A.EFFDT = MAX(Select B.EFFDT from TABLE B where B.ID = A.ID);

Thank you

[JoeM]

Access has a nice query builder that will create this code for you. Simply do the following:

1. Create a new query in Query Designer
2. Add your table in the Query Builder
3. Add the two fields in the Query Builder
4. Click on the Totals button in the Query Designer toolbar (looks like a Sigma)
5. This will add a "Totals" row under each field. In the Totals row for the EFFDT field, change the phrase "GROUP BY" to "MAX".
6. View your results

If you wish to see what the SQL code for this query looks like, change the query to SQL View. You can also use SQL View to write your code directly if you do not like using the Query Builder.

[af193]

it worked...thank you

[JoeM]

You are welcome!