Unable to deduct sold quantity and get final ROB (1 Viewer)

[captgnvr]

D/ALL

'THANKS TO THIS FORUM', In the last 10 days have started from scratch and managed to make tables, forms, queries and report for crew and items sold.

I am stuck in my last lap i.e:

1. In one query "Q_SALES_QTY" able to get various staff buying various items as sum of quantity sold in the expression [ROB1] for each item.

2. I am trying to use an update query joining the item_table with this "Q_SALES_QTY"; and wanting to update the [ROB] field in the "item_table" by putting the following in the update to [Q_SALES_QTY].[ROB1]

Why am I unable to do?? Some reading up suggested I cant use the query which has a calculated field like the one I am having [ROB1].

So pls help me with above problem or suggest other ways to deduct from the original quantity onboard, as this will be the last hurdle before I put the project to full use.

brgds/captgnvr

 

[Brianwarnock]

Access help is pretty clear on this

I'm getting the message "Operation must use an updatable query."
This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (total) was calculated for the field (using either the Totals row or a domain function in the Field row). To update a field using the aggregate of another field, calculate the aggregate in the update query itself, not a different query.

Brian

 

[captgnvr]

D/BRIAN

Thank you, I was constantly looking for response. Now shall I do the sum of quantities sold in the update query itself??. Will try in the mean time and I am sure I will be stuck again bcos dont know if it will accept equations etc.
In the mean time if u could tell me any way to do this-- it will be of immense help.
brgds/captgnvr

 

[captgnvr]

D/BRIAN
I tried to do it in the same query where I got the quantities sold to be grouped and by using the expression 'sum_of_qty' and summing up the quantities sold. But when I click update the 'sum_of_qty' will not be available to use. So can u help me in tackling this?

 

[PNGBill]

Why do you need to have the SumOfQtySold retained as a field value in your Table ?

You seldom would refer to a "Table" to get your data but rather one or more Queries will collect the data to be displayed on a Form or a Report and the QtySold would then be calculated.

Should this be important then it should not be hard. I will try and do an example and post it.

 

[PNGBill]

This qry will sum your sales.

SELECT Products.ProductID, Count(Products.ProductName) AS CountOfProductName, Count(Orders.OrderDate) AS CountOfOrderDate, Sum([Order Details].Quantity) AS SumOfQuantity
FROM Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID
GROUP BY Products.ProductID;

This qry will create a new table to hold your ROB Qty

SELECT QrySalesQty.ProductID, QrySalesQty.SumOfQuantity AS NewROB INTO _TblROBUpdate
FROM QrySalesQty;


And this qry will update your Product table from the new table (temp table)

UPDATE _TblROBUpdate INNER JOIN Products ON [_TblROBUpdate].ProductID = Products.ProductID SET Products.ROB = [_TblROBUpdate].[NewROB];

What is ROB??

 

[captgnvr]

D/BILL
I was looking out for help despertly. Was feeling awful bcos this is the last hurdle where got stuck. ROB is short for quanity 'remaing on board'. On ships we use it extensively for cargo to bunkers to provisions.

Sum of quantity sold is required for each person. For example 5 c/s of beer etc purchased over a period of time.

At present export tanker operations are going on till Monday late P.M.. However I am happy for getting a head start in this reagard. I was also going thro one link I found by Mr. Allen Browne 'inventory control.

Thanks for the sql and I will study in detail and pls help in finishing this. Once I get this then other fields like items purchased etc will follow suit.
brgds/captgnvr

 

[PNGBill]

Didn't know you were allowed beer on a tanker - but assuming flammable cargo.

Still believe QtyOnHand (ROB) should be a calculated value each time it is needed.

Same goes for QtySold for each Customer (Crew).

Just like your Bank Balance is not set in concrete but calculated each time you require the result .

Apart from keeping your Tables to the "Important" Data you are also assured that value reflects the activities that you wish to include.
eg say you want the ROB for 31st December 2009 then your query selects only transactions dated before or on the 31st December and your ROB result is correct.

Not sure if we had to create a temporary table but you should know about this as from time it is needed. The next time you do the actiivty the new table overwrites the old one so you still just have one extra table.

Spent 3 months in the Gulf as DHU on board P&O general cargo / freezer ship in 1975. Visited Kuwait before Saddam did. Great trip

 

[captgnvr]

D/BILL
it is 0200hrs already and I was going thro ur suggestions. Confession: yes you are absolutely right; our ship is alcohol free and there is no alcohol onbd; that includes beer too. Nice to know u also have sailed. Nice to meet u too.

The reason for trying to get the ROB or quantity sold etc is only to have the monthly report or as and when crew sign off etc. Also if the person who is handling the stores signs off to get ROB and other stock out/sold out etc.

It is seeming so simple and yet no way for me to figure out how to do. I have the quantity sold using the expression sum of quantity and I want to deduct that from the quantity onboard to get ROB. These are the times I prefer to do it in excel and so simple it will be. Anyhow I am on my last lap and it is interesting to see how I am going to learn/figure it out. Tomoro when I wake up and after the ship usuals are over, I will sit and do as u have suggested and post u back. Good night and thanks for the guidance. brgds/captgnvr

 

[PNGBill]

captgnvr, For when you are able to work on your database again.

To calculate your ROB as at a given date you select all the sales qty's and Goods In qty's for each product and Date of the transaction but filter the date by a criteria so you only get the transactions that suit your end result.
eg, say you now want to know the ROB as at 31st Dec 2009 then your criteria for the date would be <#01/01/2010# so only records up to and including 31st Dec 2009 are included in your calculations.

To do this you could use 5 queries.
1. select all sales records <#01/01/2009#
2. Sum the Qty field for query 1
3. Select all Goods In records <#01/01/2009#
4. Sum the Qty field for query 3
5. Select ProductID, ProductName from Product table and SalesSum from Query2 and GoodsInSum from query 4 and create a new field which is [GoodsInSum]-[SalesSum].

End result is you have created 3 calculated fields. Sum of Sales, Sum of Goods In and the result of salessum less goodsin sum. None of these fields are in your tables. They are just calculated as and when required.

You will have a Form with a button to get query 5 going and that will get the other 4 queries working.
On the form you could have a date field where you enter the date you want to calculate ROB for - say 31/12/09 or 1/5/10... whatever.

The queries I have done ask for the dates to be entered but you can get them to look at the field in your form and get this date so no need to type each time.

With this setup, you can get your ROB for today or any date you wish.
If you have ROB as a field in your Product Table then presumably it will always show today's ROB which may not be what you want.

5 queries are here in 1 to 5 order.

SELECT Products.ProductID, Products.ProductName, Orders.OrderDate, [Order Details].Quantity
FROM Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)<[Enter ROB Sales Date]));

SELECT QrySalesProductbyDate.ProductID, Sum(QrySalesProductbyDate.Quantity) AS SumOfQuantity
FROM QrySalesProductbyDate
GROUP BY QrySalesProductbyDate.ProductID;

SELECT TblGoodsIn.GoodsInID, TblGoodsIn.ProductID, TblGoodsIn.GoodsInDate, TblGoodsIn.GoodsInQty
FROM TblGoodsIn
WHERE (((TblGoodsIn.GoodsInDate)<[Enter ROB Purchases Date]));

SELECT QryPurchasesbyProductbyDate.ProductID, Sum(QryPurchasesbyProductbyDate.GoodsInQty) AS SumOfGoodsInQty
FROM QryPurchasesbyProductbyDate
GROUP BY QryPurchasesbyProductbyDate.ProductID;

SELECT Products.ProductID, Products.ProductName, Nz([SumOfGoodsInQty],0) AS PurchaseQty, Nz([SumOfQuantity],0) AS SalesQty, [PurchaseQty]-[SalesQty] AS ProductROB
FROM (Products LEFT JOIN QryPurchasesbyDateSum ON Products.ProductID = QryPurchasesbyDateSum.ProductID) LEFT JOIN QrySalesProductSumbyDate ON Products.ProductID = QrySalesProductSumbyDate.ProductID;

I should mention that I created a new table TblGoodsIn with fields GoodsInID, ProductID, GoodsIndate and GoodsInQty.

You need at least one table for Product, One for Sales and one for Purchases.
You have two for sales and could have two for goods in but the principal should still be that stock on hand (ROB) is calculated from QtyIn less QtyOut.
All Products assume Zero stock holding on day one and of course your first supply of ships stores is the first group of records in your GoodsIn table.

Trust the above assists

 

[PNGBill]

Replace <[Enter.... with <=[Enter... to get up to this date. ie enter 31/12/09 and you will get records for 31/12/09 and prior
Better then my example of just <

 

[captgnvr]

D/BILL
Started to work on the basis of 5 queries, making a table and the date query. I must appreciate for giving plenty directions to work. I am caught up with the idea of making a new table and I already tried and it is easy to manipulate data and this sticky update queries with the tables. Yet so much rigmarole just to get ROB(stock in hand) etc. I already done the query with calendar controls for 'from date' and 'to date' so that data can be gottend for the range and also made one for criteria for current month. Yes yes, progressing thanks to you.

 

[captgnvr]

D/Bill

Last one week got caught in month end paper work and now expecting inspections of the vessel and so heavy prparations are going on. That is why I am unable to get free time to finish the project.

By 20th of this month I hope the inspection will get over and can get back to my project.

Sorry for informing so late. brgds/captgnvr

 

[captgnvr]

D/Bill
good day. I just completed the inspections but not in flying colours. All superintendents gone and ship is back under my charge . Anyhow I am resuming from where I left off the project. I will post a new thread for some other queries. But with this one, I will continue what u hv guided and will try and complete it. brgds/captgnvr

 

[PNGBill]

Just taking a break from watching an old 1974 movie, Juggernaut with Omar Sharif, Richard Harris and Anthony Hopkins.

Ocean Liner (capt Omar Sharif) has 7 bombs on board but never fear, Richard Harris is going to disarm them and Anthony Hopkins will catch the culprit.

Your ship passed survey, bon voyage!