I have to update tempqty of all available products in the productlist. For this I have to calculate the difference of the Sums of each product purchased and sold from Inventory.
I am writing this query for the same in MS access but is not working:
Code:
UPDATE CopyProductBatch AS p SET p.tempqty =
(SELECT (a.total - b.total) FROM
(SELECT SUM(qty) AS total FROM Inventory AS i, CopyProductBatch AS p WHERE type = 'p' AND i.productbatchcode = p.productbatchcode) a,
(SELECT SUM(qty) AS total FROM Inventory AS i, CopyProductBatch AS p WHERE type = 's' AND i.productbatchcode = p.productbatchcode) b)
ps: 'p' stands for purchase and 's' stands for sales.
The MySQL query thats working for the above scenario is :
Code:
UPDATE CopyProductBatch AS p
LEFT JOIN (SELECT productbatchcode, SUM(qty) AS total FROM Inventory WHERE type = 'p' GROUP BY productbatchcode) AS a USING (productbatchcode)
LEFT JOIN (SELECT productbatchcode, SUM(qty) AS total FROM Inventory WHERE type = 's' GROUP BY productbatchcode) AS b USING (productbatchcode)
SET p.tempqty = IFNULL(a.total,0) - IFNULL(b.total,0)
In MS ACCESS it says "syntax error in update statement" and the cursor goes over "USING".
Is there any alias for this that works in MS ACCESS?
I don't use MySQL but I think this would be the Access equivalent. However I am fairly sure this is not an updateable query because of the aggregate in the subqueries. You will probably need to process the subqueries to temporary tables and do the update on a join between them.
Hopefully you will be able to see the structure of Access queries from this. Best work in the query designer and then view the sql until you get familiar with Access sql.
The append query syntax is:
INSERT INTO tablename (fieldname1, fieldname2)
SELECT .....
Code:
UPDATE CopyProductBatch
SET tempqty = (
SELECT (Nz(a.total) - Nz(b.total)) As Expr1
FROM CopyProductBatch
LEFT JOIN (
SELECT productbatchcode, SUM(qty) AS total
FROM Inventory AS a
WHERE type = 'p'
GROUP BY productbatchcode
)
ON CopyProductBatch.productbatchcode = a.productbatchcode
LEFT JOIN (
SELECT productbatchcode AS total
FROM Inventory AS b
WHERE type = 's'
GROUP BY productbatchcode
)
ON a.productbatchcode = b.productbatchcode)
;
Nz is a function that sets Nulls to zero. You can include an optional second argument to use a different substitution value.
I tried this query but its not working. The error is "Syntax Error (missing operator) in query expression"
I have tried several other different combinations of this query like converting it to SELECT but the same msg reappears. I have also tried changing the LEFT JOINS and the syntax all the way round, but the msg keeps popping up.
Try the change I made but I'm not hopeful. Sorry I am not very good at sql particularly with subqueries and multiple joins. I do most of my queries in the designer.
If I use subqueries I do them in the designer then try to cobble them together.
Access is odd in that it's quite strict and disallow updating even though the UPDATE query itself is one-to-one but if there's a subquery that's nonupdateable, even despite it being used as a scalar expression, this will fail.
There are two options:
1) Use a temporary table as container for the values and update from the temporary table. I don't like the idea but...
2) Write a VBA function that calls the query.
Code:
UPDATE CopyProductBatch AS p SET p.tempqty = GetTotal(p.ProdcutionCode);
In VBA:
Code:
Public Function GetTotal(ProdCode As Long) As Variant
Dim rs As DAO.Recordset
Set rs = DbEngine(0)(0).OpenRecordset( _
"SELECT (a.total - b.total) " & _
"FROM ( " & _
" ( " & _
" SELECT SUM(qty) AS total " & _
" FROM Inventory AS i, CopyProductBatch AS p " & _
" WHERE type = 'p' " & _
" AND i.productbatchcode = p.productbatchcode " & _
" ) AS a, " & _
" ( " & _
" SELECT SUM(qty) AS total " & _
" FROM Inventory AS i, CopyProductBatch AS p " & _
" WHERE type = 's' " & _
" AND i.productbatchcode = p.productbatchcode " & _
" ) AS b " & _
");" _
)
GetTotal = rs.Fields(0)
rs.Close
End Function
BTW, note that Access dislikes aliasing without the AS. It's technically optional but I've gotten errors when I try to omit the AS keyword. Furthermore, consider using ANSI style of joining instead of theta-joins.
As always it is worth posting something and waiting for Banana to explain how and why there is a better way plus a bit more.
However there are a couple of things I don't understand about the function:
The argument doesn't seem to be used in the function code at all.
I also thought that it would be rather slow to call the function for each record. Then it occurred to me that maybe both my questions are barking up the wrong tree because this function doesn't just return one value.
I would really appreciate further explanation because this does indeed look like a great solution to nonupdateable queries.
What is the difference between DbEngine(0)(0) and CurrentDB?
Thanks Galaxiom for the revision, but I had already tried it in your previous post. Not working
Its really silly of Access not to execute query that works cool in MySQL.
@Banana - Thanks for the suggestions. I think going with the temporary table idea is better because only 1 query needs to be executed, but even thats not working as you can see from Galaxiom's previous post.
First calculating all the products and then Iterating over using vb or c# will make the application many times slower.
I also thought that it would be rather slow to call the function for each record. Then it occurred to me that maybe both my questions are barking up the wrong tree because this function doesn't just return one value.
It's my fault - it is indeed meant to return one value per productbatchcode. It's an workaround to the problem of being unable to use a subquery, even if it's used as scalar expression if the subquery is non-updatable. Access will complain over this:
Code:
UPDATE t
SET t.c = (SELECT MAX(x) FROM y WHERE t.i = y.i);
saying we must use updatable query. That's a bit too strict in contrast to other engines out there which permits this syntax because the subquery can be evaluated to a scalar expression (e.g. "42" as the return) into this:
Code:
SET t.c = 42
WHERE t.i = 1
Bizarrely, when a function is used even if it's used to perform aggregated query, Jet says it's okay. In my book, it's likely to be faster to use a function query than to create a temporary table and dealing with bloat - There is, to my knowledge, no way to create table entirely in memory - such thing requires disk I/O and that's usually the slowest part of database program. Of course, that's not always true so one have to test which works better in this specific scenario.
I would really appreciate further explanation because this does indeed look like a great solution to nonupdateable queries.
What is the difference between DbEngine(0)(0) and CurrentDB?
They will return the same database, but CurrentDb is a function while DBEngine(0)(0) is just a reference. CurrentDb is nice in that it will refresh all of its internal collections before returning a pointer to you so when you examine the collections (e.g. TableDefs, QueryDefs, etc) you are looking at most recent version. If you were to delete a TableDef from a variable then later looked at DbEngine(0)(0)'s TableDefs, you would have errors because the collection would seem to reference the deleted tabledef whereas CurrentDb will clean it out so you don't have that problem.
In this function, we're not looking at the collections so there's no need to pay the relatively enormous cost of refreshing all the collections of database object, especially not several times over. Thus we use DbEngine(0)(0). In same token, this would seem to work but could be troublesome:
Code:
For Each tdf In CurrentDb.TableDefs
...
Next
If the code happen to do something such as deleting and re-creating new tabledef, perhaps to re-link, you'll find that the iteration seems to skip over few tabledef due to the refreshing messing up the enumeration of members. So it's better to do:
Code:
Set db = CurrentDb
For Each tdf in db.TableDefs
...
Next
So you get the snapshot of most recent collections and can then enumerate without getting confused over which member you are currently on, and that also runs faster because you pay the refresh cost only once instead of for each TableDef.
Its really silly of Access not to execute query that works cool in MySQL.
@Banana - Thanks for the suggestions. I think going with the temporary table idea is better because only 1 query needs to be executed, but even thats not working as you can see from Galaxiom's previous post.
You need 3 queries for that, unfortunately. Something like:
Code:
DELETE FROM tmp;
Code:
INSERT INTO tmp (productbatchcode, NewTotal)
SELECT (a.productbatchcode, Nz(a.total) - Nz(b.total)) As NewTotal
FROM (
CopyProductBatch
LEFT JOIN (
SELECT productbatchcode, SUM(qty) AS total
FROM Inventory AS a
WHERE type = 'p'
GROUP BY productbatchcode
)
ON CopyProductBatch.productbatchcode = a.productbatchcode
)
LEFT JOIN (
SELECT productbatchcode AS total
FROM Inventory AS b
WHERE type = 's'
GROUP BY productbatchcode
)
ON a.productbatchcode = b.productbatchcode)
;
Code:
UPDATE CopyProductBatch AS p JOIN tmp t ON p.productbatchcode = t.productbatchcode
SET tempqty = t.NewTotal;
As a rule, I don't recreate and drop temporary tables in Access as Append Queries to temporary table are cheaper than Make Table queries and incur less bloat that way.
First calculating all the products and then Iterating over using vb or c# will make the application many times slower.
While I agree function calls are inherently slower, I'd suggest testing this out because as I explained to Galaxiom, creating temporary table is inherently disk-bound so it may be actually expensive than doing a function call. Maybe not. You're looking at running 3 queries versus 1 function queries so it's worth testing it out.
There's also third alternative which requires more coding: Taking the SQL from 2nd queries, omitting the INSERT INTO clause so it's just a plain SELECT query, open recordset upon it then looping over each row and executing UPDATE query for each row. By using recordsets, this is no longer disk-bound, and the loop over recordset can be wrapped into a single transaction which may give better performance in relieving the overhead of fetching and locking a record for update.
Thanks Banana. Glad I wasn't losing the plot with functions. I have come across surprises so often in Access that nothing surprises me any more.
Regarding the use of temporary tables I always avoid bloat by using another local database which can easily be compacted or even discarded and recreated if necessary. I call it the "side end" and we have discussed such a concept on this forum before.
I expect the loop over the recordset is the best solution.
