Help! (1 Viewer)

THL

New member
Local time
Today, 12:19
Joined
Jun 30, 2015
Messages
4
Hi,

I am new to access and I am trying to create my first database. I am trying to create my first query which is to do with stock levels, I have one table that is stock levels and one with stock to be removed. The information for the stock to removed comes from a form that then stores the info in the table.

Both tables are linked with a relationship between the field 'EquiptmentID' I therefore want to use an Update Query to be able to then update the current stock level from the stock that is being removed. Can anyone help my create an update query to be able to do this?

Many Thanks,

THL
 

Ranman256

Well-known member
Local time
Today, 08:19
Joined
Apr 9, 2015
Messages
4,339
I would think you have 1 table... tEquip,
The EqupID record is either Available or Not, say field [RemovedFromInventory] = yes

update tEquip set [RemovedFromInventory] = yes where [EquipID] = 123
(use the query wizard)
 

THL

New member
Local time
Today, 12:19
Joined
Jun 30, 2015
Messages
4
I would think you have 1 table... tEquip,
The EqupID record is either Available or Not, say field [RemovedFromInventory] = yes

update tEquip set [RemovedFromInventory] = yes where [EquipID] = 123
(use the query wizard)


Hi Ranman,

Thank you for replying, I have two tables one called 'tblEquiptment' which has multiple columns but in this case the important ones are 'EquiptmentID' and 'Current Stock Level'. The next table is called 'tblDeleteStock' and comprises of 'Remove Quantity' and 'EquiptmentID'. In the update query page could you explain it in context to that?

Many thanks,

THl
 

khurram7x

Registered User.
Local time
Today, 17:19
Joined
Mar 4, 2015
Messages
226
Update Query could be something like below, based on your tables. Paste in SQL view and have a look in Design view.
Make sure you place all other conditions in place, like remove quantity should not be more than quantity in stock, or whatever your conditions are.

UPDATE tblEquipment INNER JOIN tblDeleteStock ON tblEquipment.ID = tblDeleteStock.EquipID
SET tblEquipment.CurrentStockLevel = tblEquipment!CurrentStockLevel - Forms!frmDeleteStock!RemoveQuantity
WHERE tblEquipment.ID = Forms!frmDeleteStock!EquipID
;
 

Users who are viewing this thread

Top Bottom