Help with repeating records

[teiben]

My table has the following fields

ItemNo Name ImageName DateRecd
123 Foo 00123.jog 1/1/06
123 Foo 00FOO.jpg 10/11/06
123 FOO FOO123.jpg 7/5/04
456 bar 00456.jog 6/10/06
456 BAR 00BAR.jpg 7/5/00
456 Bar BAR456.jpg 11/23/06

How would I write / get a query to run and show me, the 2nd item numbers, the 123 but only the most recent date so 10/11/06 and for itemno 456 the item dated 11/23/06

 

[grnzbra]

Create a query selecting the item number an the date. Then set it up as an aggregate query, grouping on the item number and select the max date.

 

[teiben]

I tried it and still get a list of the same item numbers. I only need the most current date returned, not all the other dates. Are there any sample databases anywhere

 

[gemma-the-husky]

you need two queries

- the first query just to have fields item no and date received - make this a totals query, and select max for the date column

- the second query to take the first query and the original table and join the tables on the item no AND the date received

- this seems really sloppy, joining tables on a date, doesn't it

but there really doesn't seem to be any other way to do this with one query, as Access just doesn't allow you to specify "information columns" - if you try putting say expression in the info columns, you get a totals query error, as you probably have already experienced

Hope this helps

 

[raskew]

Hi-

Not sure if it's your desire to also show the name but, if so, here's how you could do it with one query. Copy/paste this code to a new query, replacing each occurence of 'tblFoo' with your table name:

SELECT
    t.ItemNo
  , Max(t.DateRecd) AS MaxOfDateRecd
  , tblFoo.Name
FROM
   tblFoo AS t 
INNER JOIN
   tblFoo 
ON
   (t.DateRecd = tblFoo.DateRecd) 
AND
   (t.ItemNo = tblFoo.ItemNo)
GROUP BY
   t.ItemNo
  , tblFoo.Name;

HTH - Bob

 

[teiben]

I tried it and it didn't work. I created the table and pasted in your code and it just listed all the records. I'm using 97, I wouldn't think that would matter