how to make such an invoice number?

[ariansman]

I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". Can we assign a unique number, named invoicenumber here, for each set of orders that a costumer puts? Please see this table:

ID   costumer     product     quantity  unitprice    orderdate      invoicenumber

12      jack         A            4        10        25/11/2014          8612
13      jack         B            5         5        25/11/2014          8612
14      jack         C            1         4        25/11/2014          8612
15      jack         F            3         2        25/11/2014          8612
16      Steve        G            4         8        25/11/2014          8613
17      Steve        H            1         7        25/11/2014          8613
17      John         C            1         5        28/11/2014          8614
17      John         D            3         6        28/11/2014          8614
17      John         G            1         8        29/11/2014          8615
17      John         L            1         3        29/11/2014          8615
17      Jack         C            2         5        29/11/2014          8616

Actually I’d like to know how I can design the invoicenumber field so that they are set automatically, increase sequentially, and unique for a set of orders like the above example.
Thank you

 

[JHB]

Your structure isn't optimal, you need to split it op in two tables, else you'll get redundant data. Search the Internet for "Database Normalization".
One (main) which hold the information about the customer, orderdate, and invoicenumber.
And one (sub) which hold the information about product, quantity, unitprice + invoicenumber which is the link field to the main table.

 

[RainLover]

There is a Function DMax that finds the highest number.

Do that then add ONE.

Help is available in the archives.

 

[ariansman]

Your structure isn't optimal, you need to split it op in two tables, else you'll get redundant data. Search the Internet for "Database Normalization".
One (main) which hold the information about the customer, orderdate, and invoicenumber.
And one (sub) which hold the information about product, quantity, unitprice + invoicenumber which is the link field to the main table.

thank you,
so for the first( main) table, how I can design the invoicenumber field so that it puts numbers automatically, increase sequentially, and unique for a set of orders like the above example?

 

[JHB]

thank you,
so for the first( main) table, how I can design the invoicenumber field so that it puts numbers automatically, increase sequentially, and unique for a set of orders like the above example?

You do it by using a form->subform and as RainLover mention, you can use the function DMax, (DMax+1). The invoicenumber field is a normal Number field.

 

[RainLover]

The ID or Primary key should do nothing except identify a record.

Never use it in a manner that the user can see it.

Tyr Google "What Auto Numbers are and what they are not"

Hopefully you can find something useful there.