Max Expression is NOT working 2

[roaml]

Hello All,

I am seeking help from all of you experts out there.

I am having a difficult time trying to retrieve the last record entered in the following table for “WOID” 12972. For example, for WOID 12972, I only want to retrieve the last entry, which is Seq # 4.

**********************************************************
Seq # WOID StausID EnterDateTime Status Type
1 12972 7312 1/5/05 1:33 PM Completed by PM
2 12972 7311 1/6/05 1:26 PM Received from PM
3 12972 7313 1/7/05 5:33 PM Returned to File
4 12972 7647 1/24/05 2:35 PM Assigned to Field
**********************************************************

I appreciate your time. Thank you.

 

[traingamer]

Try Max([Seq #]) ... group by WOID...

or Last([WOID]) ... group by WOID...

or Max([EnterDateTime]) or any number of other ways

You may need to nest some queries if you need the rest of the data from that record.

traingamer

 

[roaml]

Hi Traingamer,

I have the Max statement in my query, but it is still not working. I have provided the query for your viewing.

[BEGIN CODE]

SELECT wo.woid, status.status.ID, status.status_type
Max([status].[enter_date]+[status].[enter_time]) AS
EnterDateTime, status.status_type
FROM wo INNER JOIN status ON wo.woid = status.woid
WHERE (((status.enter_date) Between [Start Date] And
[End Date]))
GROUP BY wo.woid , status.ID, status.status_type
ORDER BY wo.woid;

[END CODE]

I do apprecaite your help with this frustrating problem. Thank you.

 

[traingamer]

It doesn't look like you need table wo at all (unless this is just a subset of your real query).

If you group by status.ID and status.status_type that will return each disticnt combination.

You may be able to use Last() for all of the fields other than WOID whcich should be your only group by field. Someone else may be able to give you a better answer before I can give this the thought it needs.

traingamer

 

[traingamer]

It doesn't look like you need table wo at all (unless this is just a subset of your real query).

If you group by status.ID and status.status_type that will return each distinct combination.

You may be able to use Last() for all of the fields other than WOID which should be your only group by field. Someone else may be able to give you a better answer before I can give this the thought it needs.

traingamer

 

[dhookom]

Try something like this but add in your status table after confirming it works:

SELECT WO.*
FROM WO
WHERE EnterDateTime =
(SELECT Max(EnterDateTime)
 FROM WO W 
 WHERE W.WOID = WO.WOID)

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]

 

[roaml]

Hi Duane,

I tried your suggestion and the "(SELECT Max(EnterDateTime)" statement prompts me to enter a DateTime. When I enter a date (1/1/05), it outputs all the records in the table and unknown dates in the DateTime field.

 

[roaml]

Hello,

I think my problem may be the the EnterDateTime. I have it working for the most part, but I noticed on random records that the "Last" function did not output the correct record. I think this is occuring because the Enter Time field is formated with seconds. Example: 1:36:11 PM. So, I would need the Last function to filter on the entire date and time (including seconds). Any ideas?

**********************************************************
WOID StausID EnterDateTime Status Type
12972 7312 1/5/05 1:33:10 PM Completed by PM
12972 7311 1/6/05 1:26:11 PM Received from PM
12972 7313 1/7/05 5:33:12 PM Returned to File
12972 7647 1/7/05 2:35:14 PM Assigned to Field
**********************************************************

 

[dhookom]

Looking back, I now notice that EnterDateTime is a derived field and not actually in your table.

Are you showing us actual records with actual field names? What happened to Seq #? Was that a real field name? Does the Max(StausID) value for a particular WO represent the record you want returned? Is the StausID in which table?



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]

 

[roaml]

Duane,

1) Yes, they are actual records and field names.
2) Seq # is not a field in my table. It was a column
added to show the last record I wanted to retrieve.
3) Yes, StatusID is the MAX value for a particular WO that
I want to return.
4) StatusID is in the Status table.

I have two tables, a WO (Work Order) table and a Status table. The WO table stores the WO numbers for each work order. The Status table stores status events associated with a particular work order. Instead of listing all status records assigned to a work order, I want to retrieve the very last record logged for that work order.

I hope this helped. I am really close, but see some inconsistencies and I believe it may be with the time format. What do you think?

 

[dhookom]

You can try something like the following. I was guessing at your table structure and field primary and foreign keys based on your previous responses.

  SELECT wo.*, status.*
  FROM wo INNER JOIN status ON wo.woid = status.woid
  WHERE StatusID = 
     (SELECT Max(StatusID) FROM Status S
      WHERE S.WOID = Status.WOID)

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]

 

[roaml]

Well Duane, with your help, I finally got it to work.

Thank you so much for your assistance. Now I can get some sleep.