Highest Value Query

[ra.john]

Hi.

I have a table, which has an autonumber as its primary key.
Secondly, I have a field which is called AssetPlantID -

this holds the unique Plant ID for each record stored, the ID is based
apon the department code which is stored in the department table, and
then a number, 1,2,3 etc

in the table I have many records, i would like to increment this
AssetPlant ID automaticly for each new asset added to the database -

getting the DepartmentCode to appear before the ID is fine, its
querying the previous IDs to find the last is the problem

I am aware of the Dmax function, but this is no use with the Id having
letters before the numbers

for example

A1, A2, A3, A4, B1, B2, B3 etc are all AssetPLant IDs

How would I find the last number which is prefixed with an A with a
query?

 

[Van T. Dinh]

The problem is that your database structure violates the First Normal Form
(Data must be atomic) which specifies that each Field value must store only
ONE item of data, not multiple items of data (i.e. a list) or a combined
value. In your case, the AssetPlantID Field value actually store 2 items of
data: the DeptCode and a SeqNo.

It is much better that you restructure the Table to store the AssettPlantID
in 2 different Fields. If you need to combine these 2 values for display,
it is always easier to combine them than to extract 2 or more components
from a single value.

That's said ... but if your DeptCode is always, one character, then you can
use:

Val(Mid([AssetPlantCode], 2))

to obtain the SeqNo from the AseetPlantID.

 

[ra.john]

thanks, i have thought of using the Val(mid) thing, but one or 2 of the
Dept Codes are 2 Chars!

I am aware of the problem with the 1nf, the problem being, I am
designing a new Relational database - and have to keep the data from
the old database the same, esp AssetPlantID which was used as a primary
key for an un-normalised table in the previous db

I think i am going to restructure the table as you said, to hold the
plant id in one field (numeric, and allow duplicates) then query the
department first, then do dmax on that data in the query, which would
give me the largest value of that department code!

Thanks for your reply, weight of my head!

Hope to use this Group more often!