last number in a table

[steve111]

hi ,

I have 2 tables one with an "NCO " number in it and another table with a "PCO" number in it

the field name is NCONO and the number could be "12345"
the field name BATCH NO could be "PCO1234"

the table that holds the PCO number is called orders
the table that holds the Batch in is called order details

I have a form that I want to put the LAST ncono and batch number into it
that was entered into the tables

any help appreciated

steve

 

[smig]

Look if DMax() or DLast() give you what you want

 

[spikepl]

Tables have no inherent order so you need to define "last" - what value in the record tells you that that is the last record? A time stamp, or some incrementing key, could be used to order records. Look at the Dmax function, as per smig's advice. Do not use Dlast - that function is tricky, because at times it does not give you what you'd expect.

 

[CJ_London]

also remember that strings sort in a different order to numbers

so for numbers
22
1234

1234 is greater than 22

but for text
"PCO22"
"PCO1234"

"PCO22" is greater than "PCO1234"

 

[steve111]

thanks

they are using the next number/ pco number , but they have no record of the previous number only in an excel sheet , what I want to do is show them the number they used last in the form where they enter that number so they don't look into the excel sheet, but confused how to do it

I could use a list box and do it decending but I wanted to do it without using list/combo box

steve

 

[CJ_London]

to get a 'last entered' number you would need to use a timestamp or incrementing key as Spike suggested

 

[gemma-the-husky]

steve

do you mean the "highest" number, rather than the last.

if so then simply

highestnumber = dmax("numberfield","tablename")

if you mean the last number added, then it is more difficult, as you need some other information to identify that value, such as the date/time of entry.

 

[steve111]

hi , thanks yes it is the last number they entered , but it will / should be the next highest number

 

[RainLover]

You should treat the current record that is being added as the last record.

Not edited, added.

Does this help

 

[CJ_London]

and don't forget post #4 re the difference between the maximum of text and numbers

 

[RainLover]

Where is the reason behind this request.

What if there are several uses. Which one is then the highest.

Then what are you going to do with the result.

I just thought that that the answer to these questions could have an impact on what you have been asked to do. Things may be fine for ages then all of a sudden the ?????
hits the fan.

Best wishes for the project.

 

[steve111]

they have an order number that could last for many orders which can be added to
then they have an NCO NO that defines that which part of the order that is.
this number was just recorded in an excel sheet
then the have a PCO number That is for the part to be purchased which could consist of many parts to purchase

again located in an excel sheet
all I was trying to do is stop them looking in an excel sheet for the last number used

steve

 

[RainLover]

That is explained a lot better.

So the number you want, is it the highest number in that table or is it the highest number for a particular Customer.

At the moment DMax is looking to be the command you will need to use. Suggest that you look at Access Help for a better understanding. Also a Google search may help.

Please get back with what you find. There may be a better approach.

 

[steve111]

it will be the highest in the table

 

[RainLover]

Highest is not a good word to describe what you want. Highest Value would be better.

This being the case then DMax is the command you need to use.

Have a go at it and if you do not succeed then post a copy of what you can do and someone will have a look at it for you.

Please use real Field Names and Table Names.

 

[CJ_London]

steve - just to clarify - looking at your spreadsheet right now, what value would you expect to be returned as the 'highest number'

 

[RainLover]

CJ

I believe you are looking for something more. I agree. Something is missing. The way it has been explained the result is a number. Say 3763353. This result would be the answer in all cases.

Leave it with you. Bed time for me.

 

[CJ_London]

 The way it has been explained the result is a number. Say 3763353.

Not in the original post - it would be something like PCO1234 which being a string, max would not work - at least not without some additional manipulation

Also not clear whether PCO number is a subset of the order number or just increments independently.

 

[steve111]

hi

the table names is called orders which has a field NCONO
The other table is called order details which has a Field called PCONO

they enter an NCO for every new order even an order within a order
they enter a pco no for every part ordered within the NCOno ( could be 6 parts ) therefore having 6 pco numbers

at present it seems that just pluck numbers out of the air and record in an excel sheet ) then just use another set of numbers for the next order.

at present nco numbers are

1717, 1719, 1725, 1726, 1730 etc

pco 1123,pco1124, pco 1235, pco1236

this is poor

so I was going to show them

1730
pco 1236


I want to stop this

I want they use the next number in sequence if possible , that's why I want to see the last number they used so they can use the next number in sequence

I don't know if this is the only way to do it.

if it was possible that the next number either in the ncono field or pcono field could be generated automactically after entering other information into the form it would be better , but I cannot use the autonumber as the database and links etc are all done

steve

 

[CJ_London]

they enter a pco no for every part ordered within the NCOno

OK so a list might look like

 NCONo  PCONo
 1234   PCO0001
 1234   PCO0002
 1234   PCO0003
 1235   PCO0001
 1235   PCO0002
 1236   PCO0001
 1244   PCO0001
 1244   PCO0002