Urgent Access query help required

[aty01]

hi

i have a issue with my database

i have a customer, orders, stock tables

i have customers placing orders

however the stock doesn't update. to get around this i created an update query, so that the stock will automatically update as i process orders.

however the problem that i have is that once i run the update query it apply to all of the orders again

e.g.

item 1 - qty in stock 100

item 2 - qty in stock 100

if i place a order for item 1 and i require #50 item 1's.

and i run the update query, it will work fine

but if i place a order for item 2 (#50 qty) it will take off 50 for item 2 but it will also apply the query to item 1 again leaving no item 1 left

please help

many thanks



Aty01

[Bob Fitz]

Sounds like a problem with the criteria used in the query.
Can you show us the SQL of the query.

[aty01]

hi bob

i didnt use sql but what i put in the update column was

[stock]![qty in stock] - [orderline]![quantity]

qty in stock - qty ordered

[Bob Fitz]

So have you set any criteria for the update. If not, then all records in the table will be updated.

[aty01]

hi bob

what do i put in the criteria

am not sure

please help


Cheers

[Bob Fitz]

Take a look at this link:
http://office.microsoft.com/en-us/ac...010076527.aspx
Post back if you need more help.

[aty01]

hi bob

i do realise that i am supposed to put something into the criteria box,

but am not sure wht to put in there.

all i want is to apply the update to the last order placed

[Bob Fitz]

What are the names of the fields in your stock table

[aty01]

stock id
supplier id FK
stock name
price
qty in stock


the fields that i`m trying to query are qty in stock & quantity ordered

qty in stock - quantity ordered

but i need to put something in the criteria part

[Bob Fitz]

I think that will be something like:
"[stock id] = Something that matches"
This is often a number.