how to make invoice number?

[ariansman]

I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:

ID   costumer     product     quantity  unitprice    orderdate


12      jack         A            4        10        25/11/2014
13      jack         B            5         5        25/11/2014
14      jack         C            1         4        25/11/2014
15      jack         F            3         2        25/11/2014
16      Jack         G            4         8        25/11/2014
17      Jack         H            1         7        25/11/2014

Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
Thank you

[June7]

Generating custom unique identifier is a common topic. For start review: https://www.accessforums.net/forms/a...ing-23329.html

[A S MANN]

The order table should have details of order with invoice no as primary key.
The details shown above in table order should be in table Order_Details having 1 to many relationship with Table Order.
You are facing this problem due to wrong table design.

[Demerit]

Just like A S MANN said, your table structure is not good
Create a table called: ORDERS
ORDERS:
OrderID PK
customerID
Orderdate



ORDER_DETAILS
ID PK
orderid SK
productId
quantity
unitprice

[orange]

ariansman,

See the links on this page to help you understand table design, keys, and relationships.


Also you should advise readers that you have cross posted when you post the same/similar question on multiple sites.

http://www.access-programmers.co.uk/...d.php?t=272788

http://www.utteraccess.com/forum/ind...wtopic=2024854