Return the largest value

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

My data base is linked to a SQL server that contains
several bill of materials (BOM's). For example; one BOM
is GAS100777-01.R10 and the other is GAS100777-01.R11. How
do I get the query to return the R11 BOM without having to
type GAS100777-01.R11 in the criteria? This is need
because I don't want to have to update the query everytime
a BOM is updated. Instead I want the query to give the
latest BOM automatically.
 
Steve

Could you add a column called "ts" (for TimeStamp) and when a new BOM is
created, put the current date/time value in the "ts" column. Then create a
query in descending order. The first record is the most current BOM.

HTH

--

Rob

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Problems with your database? Need to upgrade your application?
Contact the FMS Professional Solutions Group: www.fmsinc.com/consulting

Need a Book Recommendation?
www.fmsinc.com/toplevel/books.htm

Need software tools for Access, VB or .NET?
http://www.fmsinc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Perhaps you could store the revision number in a separate field and then use
that. Otherwise you will need to calculate the BOM value.

Could you post the SQL of your current query and tell us what you want to see
returned? Something like the SQL shown MAY work depending on the data structure
of the field.

SELECT A.*
FROM TableName as A
WHERE A.BOM IN
(SELECT Max(B.BOM)
FROM TableName As B
GROUP BY LEFT(B.BOM,Instr(B.BOM,".R")-1))

That relies on there being a ".R" on every BOM and it being there only once. It
also relies on the BOM Revision two being entered as .R02 and not .R2. If the
first BOM entered does not have a ".R00" at the end then the above will also fail.
 
Back
Top