fernandoweb
asked on
How do I run a query which uses a "where" clause in the criteria?
Hi there. I'm trying to run a query which works out stock levels which returns a figure based on two different tables - if I put just one field in it (screengrab1), it will output for that particular stock item fine . If however I add another (screengrab2), it outputs nothing, which I assume is because it's saying only output the figure if both conditions are true. However moving it down to the "or:" row doesn't help. Any ideas?
ASKER
Sorry. I would like a table which displays the stock level for each item. For example, the output I'm expecting for this is:
1ml Insulin: 4700
1ml Barrel: 4500
As there are 5000 of each and 300 of the former have been transacted, 500 of the latter. If I just run the screengrab 1 query, I get this:
1ml Insulin: 4700
Which is great. However if I then add another field (screengrab 2), the output is blank.
1ml Insulin: 4700
1ml Barrel: 4500
As there are 5000 of each and 300 of the former have been transacted, 500 of the latter. If I just run the screengrab 1 query, I get this:
1ml Insulin: 4700
Which is great. However if I then add another field (screengrab 2), the output is blank.
It's still not clear I'm afraid.
You have two queries - qrySumOfStock appears to show a stock level for each item. I assume that qrysumoftransactions is a sum of transactions - is that for each stock item as well, there is no stockitem field visible.
How does qrySumOftransactions work? Are you adding together movements in and out for the same item? If so, and there are are 200 in and 50 out does that give a sum of 150 or -150?
I assume you want to use sumof transactions and sumof stock to get a ''current stock" result - is that what you are after?
What are you using the criteria to do? I don't understand that bit at all.
I would be expecting to match the two queries on stock item if the field is present in both.
qrySumOfTransactions contains a lot of different totals. You are only using SumOf1mBarrel in your expression; is that the only value that matters in qrySumOfTransactions.
You have two queries - qrySumOfStock appears to show a stock level for each item. I assume that qrysumoftransactions is a sum of transactions - is that for each stock item as well, there is no stockitem field visible.
How does qrySumOftransactions work? Are you adding together movements in and out for the same item? If so, and there are are 200 in and 50 out does that give a sum of 150 or -150?
I assume you want to use sumof transactions and sumof stock to get a ''current stock" result - is that what you are after?
What are you using the criteria to do? I don't understand that bit at all.
I would be expecting to match the two queries on stock item if the field is present in both.
qrySumOfTransactions contains a lot of different totals. You are only using SumOf1mBarrel in your expression; is that the only value that matters in qrySumOfTransactions.
ASKER
Right....
The reason the two tables are different is because one (transactions) takes each stock item as a different field, while the other (sumofstock) has a field called "Stockitem" which works from a picklist. But they're the same items.
The criteria is just asking for the stock to be subtracted for that particular item - ie: 1ml Insulin has the code 1, so you only want stock to be subtracted for that field which has the code 1. And as I say, it does work fine if you just do it for one item.
If there are 200 in and 50 out btw that gives a sum of 150 (200 minus 50) Correct that I want a "current stock" result.
In terms of using SumOf1mlBarrel, that's just to illustrate the point that as soon as I add another field the query returns zero. The query if it worked would have every stock item in it.
Sorry, don't know how to make it any clearer but let me know if I can.
The reason the two tables are different is because one (transactions) takes each stock item as a different field, while the other (sumofstock) has a field called "Stockitem" which works from a picklist. But they're the same items.
The criteria is just asking for the stock to be subtracted for that particular item - ie: 1ml Insulin has the code 1, so you only want stock to be subtracted for that field which has the code 1. And as I say, it does work fine if you just do it for one item.
If there are 200 in and 50 out btw that gives a sum of 150 (200 minus 50) Correct that I want a "current stock" result.
In terms of using SumOf1mlBarrel, that's just to illustrate the point that as soon as I add another field the query returns zero. The query if it worked would have every stock item in it.
Sorry, don't know how to make it any clearer but let me know if I can.
Sorry - but I can't get to grips with this.
I'll ask others to take a look.
I'll ask others to take a look.
The second column, move the criteria one row down (to line labelled Or: at far left).
/gustav
/gustav
Your problem is that your first query, qrySumOfTransActions, is fundamentally incorrect. Instead of returning one record with a field for each stock type, you should be returning one record for each stock item along with the sum of all transactions.
Then you would join the two queries on StockItem.
The result is that you would have the Sum Of Stock (Quantity On Hand) and Sum of Transactions (Inventory Movement) for each item and could do anything you want with the numbers at that point.
Jim.
1. You lack any joining criteria. (may be in Where clause or in From clause)
2. It looks like you have summarized your stock as separate columns. Please confirm. (not generally how this is to be done)
3. Please paste the SQL for both queries.
2. It looks like you have summarized your stock as separate columns. Please confirm. (not generally how this is to be done)
3. Please paste the SQL for both queries.
Correct me if I'm wrong, but it would appear that qrySumofTransactions returns a single row, with one column per item, and that, conversely, qrySumofStock2 returns two columns with one record per item. I'm assuming that the stock item number 1 is Insulin, and you want to subtract from SumOfNumber (the total stock) the total in SumOf1ml Insulin, hence you write a criteria which you hope to be active only for that column. Is that correct?
A database system doesn't work that way. You should rewrite your sum of transactions to create something very similar to the sum of your stock, using in particular the StockItem numbers from the transactions, and not the full names. In the end, both your sum of transactions and your sum of the current stock would have two fields, StockItem and a sum, which you can then link.
In any case, your query cannot work like this, and there is no way to specifically link the column for Insulin from one query to the record for Insulin in the other.
To achieve the result you are looking for, you can remove the query qrySumofStock2 from your query and use DLookup to find the relevant number from it instead. I am not recommending you use this as a permanent solution; this is only a demonstration.
1ml Insulin: DLookup('SumOfNumber', 'qrySumofStock2', 'StockItem=1)-[SumOf1ml Insulin]
1ml Barrel: DLookup('SumOfNumber', 'qrySumofStock2', 'StockItem=2)-[SumOf1ml Barrel]
2ml Barrel: ...
Again, this should not be used as solution; rewrite the query to return rows and not columns.
(°v°)
A database system doesn't work that way. You should rewrite your sum of transactions to create something very similar to the sum of your stock, using in particular the StockItem numbers from the transactions, and not the full names. In the end, both your sum of transactions and your sum of the current stock would have two fields, StockItem and a sum, which you can then link.
In any case, your query cannot work like this, and there is no way to specifically link the column for Insulin from one query to the record for Insulin in the other.
To achieve the result you are looking for, you can remove the query qrySumofStock2 from your query and use DLookup to find the relevant number from it instead. I am not recommending you use this as a permanent solution; this is only a demonstration.
1ml Insulin: DLookup('SumOfNumber', 'qrySumofStock2', 'StockItem=1)-[SumOf1ml Insulin]
1ml Barrel: DLookup('SumOfNumber', 'qrySumofStock2', 'StockItem=2)-[SumOf1ml Barrel]
2ml Barrel: ...
(°v°)
Took me some time to react, understand, and write... sorry about the repetition. — (°v°)
ASKER
Thanks for your help with this - you're correct harfang in everything you've said. I'm having a bit of a problem with rewriting the query to generate rows and not columns. Apparently I need to use a union query to transpose them or I can go further back and change the whole way stock is entered on to the system. It's just taking me a while working it out.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To be honest it is just a small part of a database that's been running for 3 years now and seems to work fine in every other area, but I realise I messed up with the design of this part of it so you're right, this component probably does need sorting from scratch.
ASKER
Probably not my greatest question!
All we can see is the query you've created, which is not that clear in itself, but we don't know what you want as a result.