Relations between tables

[pbs]

Hello,


I am new at this forum, because i have a (medium) problem.
Perhaps anyone can help me with it.

I need to make a database to control my goods.

i have made three tables.

table 1

OrderID auto
order text
call text
date date
....

table 2

ArtikelID auto
artikel text
Artikelname text
stock number


table 3

SerieID auto
Serialnumber text
Objectnumber text
Place text
...

If i get a order then i fill this in in table1.order
a order can consist from multiple artikels table2.artikel
then the last step if in a order they orderd two of the same artikels you can have two serialnumbers table3.serialnumber


See uploaded picture

The problem is that the table 2 has around 2000 artikels now, and the artikels have a atribute no duplicates.
The only way to alter the table 2 is when i need to add a new artikel It checks for duplicate artikels.
Second problem is for example artikel 2 there are 40 pieces in stock (table2), they ask for two pieces.
Where do i (which table ?) fill in 2 pieces so i can recalculate the stock 40-2=38
The next time they the same artikel it must show 38 instead of 40
O ja table 2 is a import from a other system therefor is must stay intact, as i said before.

Please, can anyone help me with this problem, i have done so many attemps already.
i made a fourt table help , but is didn't solve my problem so far, every time table2 wil be altered, and then i get a message duplicate not allowed. ( and that is good )
Is this also possible with split tables ( i want to use have the tables on a central server ) so that i can make differt forms for other people (purchase)
They only need to fill a order, the artikels and how many of them they need. the rest is for me.

Thanks in advance ( Sorry for the not 100% correct english, i am dutch )

[orange]

See this free data model for a generic arrangement of Customer, Order, OrderDetail, Item(article)

[ranman256]

The serial# could be in tabl2.
but is tabl3 the tStock table?
when the order is Submitted, that is when you run an update query to reduce the count in the tStock table.