Totals query question

Question

I am working on an inventory application in Access 2013, and I have question regarding Total queries. 

The underlying table is pretty simple. I will try to give the gist here. It essentially tracks items that are serialized, i.e., they have a serial number and other sttributes:

Item     S/N     location    condition   weight    ... etc

--------------------------------------------------

itemA   123      loc1          new          3

itemB    234     loc2          used         4

itemA    567     loc2          used         3

itemA    789     loc1          new          3

I now want to be able to select a condition in a combobox (for example: New), and I want table that shows me

Item      quantity

-------------------

itemA       2

and when I select another attribute (for example loc2) the query should show:

Item      quantity

-------------------

itemA       1

itemB       1

I have tried to do that with a totals query, by adding "location" or "condition" to the query grid and filter the results by adding the combobox as a criteria, but if I do that, access sees records for, say itemA as different and instead of "itemA   2", I see 2 times "item A   1"

How do I fix that? Can I set up a totals query by saying: Give me the number of records where item is "itemA" and Condition is "New" and disregard anything else? Remember that I want to be able to change that to "Give me the number of records where item is "itemA" and Location is "loc1" and disregard anything else, and also "Give me the number of records where item is "itemA" disregard anything else.

I keep on digging to see if I can find an answer ...

Answer 1

Hi, Since you want to apply a criteria on different fields at a time, you might be better off writing the SQL statement on-the-fly. Also, what do you plan on doing with the result? It is not always necessary to display the result of a query before using it elsewhere. Just my 2 cents...

Answer 2

 Can I set up a totals query by saying: Give me the number of records where item is "itemA" and Condition is "New" and disregard anything else? Remember that I want to be able to change that to "Give me the number of records where item is "itemA" and Location is "loc1" and disregard anything else, and also "Give me the number of records where item is "itemA" disregard anything else.

Search on 'cascading combos.'

---

Build a little, test a little

Answer 3

Can I set up a totals query by saying: Give me the number of records where item is "itemA" and Condition is "New" and disregard anything else? Remember that I want to be able to change that to "Give me the number of records where item is "itemA" and Location is "loc1" and disregard anything else, and also "Give me the number of records where item is "itemA" disregard anything else.

To restrict a query on the basis of multiple parameters, and apply only those parameters with a value the syntax for the query's WHERE clause is:

WHERE (SomeColumn = [some parameter]
  OR [some parameter] IS NULL)
AND (SomeOtherColumn = [some other parameter]
  OR [some other parameter] IS NULL)
AND etc

The way this works is that each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

In your case the same logic has to be applied to expressions in the GROUP BY clause so that it will only group by the Condition and/or Location if the condition parameter and/or location parameter  is not Null, e.g.

SELECT Item, COUNT(*) As Quantity
FROM Inventory
WHERE (Item = Forms!YourForm!cboItem
    OR Forms!YourForm!cboItem IS NULL)
AND (Condition = Forms!YourForm!cboCondition
    OR Forms!YourForm!cboCondition IS NULL)
AND (Location = Forms!YourForm!cboLocation
    OR Forms!YourForm!cboLocation IS NULL)
GROUP BY Item,
IIF(Forms!YourForm!cboCondition IS NULL,NULL,Condition),
IIF(Forms!YourForm!cboLocation IS NULL,NULL,Location);

Note that when you do this, parameters should only be declared if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE and GROUP BY clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

---

Ken Sheridan, Stafford, England

Answer 4

Hi vistauser111,

you had mentioned that,"I have tried to do that with a totals query, by adding "location" or "condition" to the query grid and filter the results by adding the combobox as a criteria, but if I do that, access sees records for, say itemA as different and instead of "itemA   2", I see 2 times "item A   1""

as I can see you have multiple item A in your data which have different serial numbers , conditions and weight.

if it just a sample data and you have proper names stored in your table then it should not be an issue.

but if you have duplicate names then I suggest you to properly name your product.

it creates lots of confusion for the person who going to see this data.

I think that your approach for combobox showing you the expected result but the issue is with your logic.

so you need to correct it.

you can dynamically add the conditions to your query before selecting the data.

for that you can try to use number of checkboxes for each conditions.

if you use combobox then you can add only one condition in your query.

also you can show us your query and your desired output and the actual data in your table.

we will try to provide you an example.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi vistauser,

For your requirement, I agree with theDBguy, you need to generate the SQL dynamically.

I think you could pass the selected column name and value while conbobox value changed to below function for your first requirement.

Private Sub QueryByPara()
'declare a string type variable to hold the sql statement
Dim strSql As String
'declare a string type variable to hold the selected field name
Dim strSelectedFieldName As String
Dim Criteria As String
Criteria = "new"
'assign the selected field name to the variable and
'concatenate the predefined text value to it
strSelectedFieldName = "condition"
'create the sql statement using the variable
'to include the user selected Field
strSql = CurrentDb.QueryDefs("qryGroup").SQL
strSql = Replace(strSql, "FilterName", strSelectedFieldName)
strSql = Replace(strSql, "FilterValue", Criteria)
'here I am updating the "query definition" of an existin query but
'the sql statement could acutally be used in any appropriate capacity
'use the updated sql statement to actually
'update the sql part of the predefined query
Debug.Print strSql
CurrentDb.QueryDefs("qryGroup").SQL = strSql
'additional code as needed
End Sub

qryGroup query

SELECT GroupTab.Item, Count(GroupTab.Item) AS quantity
FROM GroupTab
WHERE FilterName='FilterValue'
GROUP BY GroupTab.Item;

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi vistauser111,

I can see that you did not follow up this thread , after creating this thread.

is your issue solved?

if you got the solution by yourself then I suggest you to post the solution and mark it as an answer.

if your issue is still exist then I suggest you to refer the suggestions given by the community members.

if still you have any further question regarding same issue then I suggest you to let us know about that.

we will try to provide you further suggestions to solve the issue.

Regards

Deepak 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].