If Statement - Complex

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone! I have two tables concerned, one 'tblProducts' the other
'tblOrders'. I have two fields in 'tblProducts' - (QuantityOrdered) and
(ItemsInStock). When the number of (ItemsInStock) falls below 25% of the
number (Quantity Ordered) Then insert record into 'tblOrders'.

Any help on compiling something together would be great...

Thanks
 
Hi everyone! I have two tables concerned, one 'tblProducts' the other
'tblOrders'. I have two fields in 'tblProducts' - (QuantityOrdered) and
(ItemsInStock). When the number of (ItemsInStock) falls below 25% of the
number (Quantity Ordered) Then insert record into 'tblOrders'.

Any help on compiling something together would be great...

Thanks
Click to expand...

Create a Query based on tblProducts.

Include a calculated field:

PercentOnhand: [ItemsInStock] / [QuantityOrdered]

Include the ProductID in the query (together with any other required
fields in tblOrders - perhaps a calculated field with Date() or Now()
if you record the date or date/time when the order was generated)

Change the query into an Append query using the Query menu option.

Run the query.

Note that this will NOT change the QuantityOrdered, you'll either need
a separate update query to do so - or, probably better, ELIMINATE the
quantity-ordered field from tblProducts and count the actual quantity
ordered from the Orders table.

John W. Vinson[MVP]
 
Could you be a little more precise on what I am supposed to do? I'm not very
familiar with MS Access.
I have provided a field list from both tables:
(tblProducts):
- ProductCode
- CompanyID
- ProductName
- Weight
- WeightUnit
- UnitPrice
- QuantityOrdered
- ItemsInStock

(tblOrders)
- OrderNo
- CompanyID
- ProductCode
- Quantity
- OrderSent

The two tables are related through [ProductCode].

Thanks, your help is much appreciated.
 
Could you be a little more precise on what I am supposed to do? I'm not very
familiar with MS Access.
I have provided a field list from both tables:
(tblProducts):
- ProductCode
- CompanyID
- ProductName
- Weight
- WeightUnit
- UnitPrice
- QuantityOrdered
- ItemsInStock

(tblOrders)
- OrderNo
- CompanyID
- ProductCode
- Quantity
- OrderSent

The two tables are related through [ProductCode].

Thanks, your help is much appreciated.
 
Could you be a little more precise on what I am supposed to do?
Click to expand...

No, I cannot, because I don't know your business model. How many items
do you want to order? If it's variable, how do you determine that
number? What's the CompanyID, and why is it in both tables? Where does
QuantityOrdered come from? Should QuantityOrdered change when you add
records to tblOrders (if so, it's suspicious because that's redundant
derived data)?

John W. Vinson[MVP]
 
Back
Top