Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DMAX if only one record?

Status
Not open for further replies.

angelandgreg

Technical User
Sep 20, 2006
106
US
I tried searching to see if i can find something first but did not find anything ...


I have a query that looks for the last date for an order number.


But if the order number only exists once it doesn't show.
It only shows the DMAX date IF the order number exists more then once.


How or what should i use to have my query show only the last or only record for an order number?

 
Why not posting your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Code:
UPDATE (((SO2_SOEntryDetailLine ON OrdersToReprint.SalesOrderNumber = SO2_SOEntryDetailLine.SalesOrderNumber) INNER JOIN ControlLogArchives ON OrdersToReprint.SalesOrderNumber = ControlLogArchives.SalesOrderNumber 

SET ControlLogArchives.LogStatus = "BrghtBk"

WHERE (((ControlLogArchives.ControlLogDate)=DMax("ControlLogDate","ControlLogArchives")));
 
Since this is an update query that seems to only involve the single table ControlLogArchives, why do you need the other tables?

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]
 
hi dhookom!

it needs to link where the order number exists in the SO table as well and update the status in the ControlLogArchives where it's the only one or the lastest one, hence the need to use the DMAX.

I think it's working though. I tried it on one that had only once instance in the ControlLogArchives and the DMAX didn't bring that one up but tested with another order number that was in the table more then once and it worked.

BUT i tried it again yesterday for an order number that was in only once and it seemed to work.

should it?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top