1

I have two lists of contracts One has past ending dates for each. Each time the ending date gets updated or extended a new record is createed in the original table. The other just has the contract number and product description. The first looks like this:

Contract   End Date
AAA001     12/01/2013 
AAA001     12/30/2013
BBB002     01/01/2014
BBB002     01/30/2014

You can see that for each contract the end date was updatedonce each.

Contract   Product
AAA001     Apples
AAA001     Apples
BBB002     Oranges
BBB002     Oranges

I need a query that will produce a table like this where only the maximum date value is returned.

Contract   Product   End Date
AAA001     Apples    12/30/2013
BBB002     Oranges   01/30/2014

Is it possible to use a Mas...In statement? I am using Access 2010.

MillaresRoo
  • 3,808
  • 1
  • 31
  • 37
user2907249
  • 839
  • 7
  • 14
  • 32

1 Answers1

5

Try this query

Select Contract, product, MAX(endDate) FROM table1 
INNER JOIN table2 ON table1.contract = table2.contract 
group by contract, product
Elias
  • 2,602
  • 5
  • 28
  • 57
  • Sorry it's still returning all the different dates with just the products added. – user2907249 Dec 19 '13 at 22:14
  • Ah, I see now, I'll try to give you a better example the next chance I get. I believe you will need something like a Common Table Expression, such as http://stackoverflow.com/a/18869541/1504882 – Elias Dec 20 '13 at 04:06
  • 1
    Hey thanks for pointing that out. I will mess around with that once I get back to work. That example is pushing the limits of my SQL knowledge, Will see if I can get it to work! – user2907249 Dec 23 '13 at 03:37
  • @user2907249 did you ever get this to work? Could you show me what the SQL above returned that was wrong? – Elias Aug 19 '14 at 16:03
  • Yes, sorry. go it to work. I just created a crosstab query and then pulled then end dates from there. Makes it a bit less efficient but the dataset is not so large that it matters much. – user2907249 Aug 20 '14 at 14:58
  • Could you show that in an edit on your question for future users? – Elias Aug 20 '14 at 15:27