Retrieving the last time for a Specific Date

In Access 2000 I have a table (Table1) in which has multiple fields. I only need two, the Dept ID and the Date/Time Stamp for each transaction. I'm trying to create a second table (Table2) that would grab the DeptID field (able to do), and also grab the Date/Time Stamp, but here is the thing. I only need the last date time stamp per business day (BD) and if there wasn't any, reflect that also. So, Table2 has DeptID, BD1, BD2, until BD10. For example, if dept 1020 had let’s say 5 transactions on BD, 1 and 3 on BD 3, I need it to show on column BD1 the last Date/Time stamp, on BD2 leave blank and BD3 last Date/Time stamp. This is the formula that I'm using: DLookUp("10/06/2006*","[Table 1]![LastPostedDate]","[Dept ID]=" & [Dept ID] & "")

One Possible Solution Below:

I think what you need is a query, rather than a table. I also think you probably need a cross tab query, but first you will need to create a query to base the cross tab query on.

Build a new query in Design view;

Add the table that has the fields you require;

Add the DeptID and then add the Date/Time stamp twice. In front of one of the Date/Time stamp fields add a new field name i.e.

NewName:Date/Time stamp

Right-click on the first Date/Time stamp and select properties and set the format to short date. Repeat for the second time/date field and set the properties to short time. Now both the date and time will be displayed in different columns.

Close and save the query.

We will now create the cross tab query.

Click on NEW and double-click on CrossTab query wizard;

Add the new query you have just created to the query wizard > next;

Move DeptID accross to the right hand side > Next;

Choose the time/date field that you have formatted to short date as the column heading. > next;

Choose to group by 'date' > next;

Select Max from the right hand list > next;

Name your query > Finish.

You may need to re-open the cross tab query in Design view and reset the time field back to the format of short time - it sometimes gets lost in the process.

This crosstab query will display the latest time for each day.