Hello again,
I am using Libreoffice base 6.3.5.2 on Windows 10, after setting up invoices and a few items. I wanted to to set up a very simple stock update query. Basically I have a number of items in stock (“ITEMS”.“Stock”) and would like to simply deduct the items that would get invoiced from another table (“SOLD”.“QTY”). SO i thought that by simply deducting the items sold would update the stock but my problem is that it only updates the values where you have a sold qty BUT puts 0 values on items that have not been sold (whereas there still should be 1 or 2 pcs in stock. This is most probably due to the fact that there is no value where there is no invoice of course. I have tryed to COALESCE (“SOLD”.“QTY”, 0) within the query but that didn’t resolve the problem.
I have been trying and searching but couldn’t find the correct way to put it. Of course as a newbie, I am using the graphical metod to make queries.
If my explanation isn’t accurate enough, I can send the odb file of course.
Hello,
Your question is a bit confusing. Not certain whether you want to actually update a file or just produce a calculated result. COALESCE is used to select the first non NULL item. Certainly not what you want.
It would appear that the query should contain something such as:
"ITEMS"."Stock" - "SOLD"."QTY" AS "Current Stock"
Of course a lot depends on how and where to use this here as it fits into your entire processing. A consideration is to get the total sold first before calculating the entire inventory.
If you still have problems, edit your question with the additional information and please supply a sample (still don’t know what database you are using!).
Many thanks for your help and speedy answer @ratslinger, I slept over it and came to the conclusion that this is something that would need to be done differently. I will rethink this and rather should build something like a IN / OUT kind of Query to establish a proper inventory, at least I will try. I am using the internal HSQLBD database for this but I am thinking trying to set this as a split database later for the sake of security.