Query Calculation Field Criteria Not Followed

[klarae99]

In Access 2003 I am creating an Inventory Database from scratch. There are two tables (tblProductInfo rmation and tblSubProducts) that I am trying to use in a queary to determine the number of Items in Stock. The SubProducts table is linked to the Product Information table by Item # (and the information is added and viewed as a form Product Information with a Sub Form Sub Products)

I have a form (frmInventoryLo cation) where a location is selected for the Queary, the queary also has criteria to return all locations if no location is choosen.

There is a calculation field in the queary that I am using to calculate the number in stock by subtracting the # Sold from the # recieved (both fields in the SubProducts table). This calculation works fine. However, I want to limit my list to those enteries that have inventory in stock (AKA In Stock > 0) I have tried adding the >0 criteria to the calculated field but the queary still returns all results, even those with no stock.

I have managed to get around this by creating a second queary (QryInventoryLo cation) that has all the fields from the first queary (QryInventory) and the Criteria of >0 for the In Stock field. This works but requires that I have two quearys to do something that I think I should be able to accomplish in one. Does anyone have any ideas to fix my first queary to 'see' the >0 criteria?

[klarae99]

Hello Scott,

This is not going away easily.

I moved the iff clause into the sum clause like you suggested and am now getting a different set of messages when I click run. First I am getting two dialoge boxes, the first asking for tblSubformProdu cts!#Recieved and the second asking for #Sold. (I also get one asking for location but I know that is because I am running in without the form for input.) If I leave the text boxes blank I get the following error message:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

If I enter 1 for the #Recieved and 0 for the #Sold The queary will run but it does not give me the correct In Stock amount, and it also does not give me an answer of 1.

Could this error be because I am using the groupby Sum feature on the # Recieved and #Sold? My reasoning for doing this was to get only one listing per item per location but if it is causing things to be more complicated than they should be maybe there is a different way I could obtain the same result?

Sorry to be such a pain, I had thought that this would be an easier fix. I appreciate all the time you have dedicated to my question.

[Scott Price]

Again, not a problem! Happy to help out...

How about pasting in the sql you have now; then I'll set this up in my test database and get back to you.

Regards,
Scott

[Scott Price]

Ok, I put this into my test database and came up with this SQL. This is only using one table: tblSubformProdu cts. It returns one value for each productID and eliminates products that have a balance of 0 in stock...

SELECT DISTINCTROW tblSubformProducts.ProductID, Sum([tblSubformProducts].[#Recieved]-[tblSubFormProducts].[#Sold]) AS [In Stock]
FROM tblSubformProducts
GROUP BY tblSubformProducts.ProductID
HAVING (((Sum([tblSubformProducts].[#Recieved]-[tblSubFormProducts].[#Sold]))<>0));
 

It could also be useful to you turning this around, to find out which product is sold out, eh? To do that just change the <>0 to =0...

Regards,
Scott

[klarae99]

Scott,

Here is the current version of my SQL code.

SELECT tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued, Sum(tblSubformProducts.[# Recieved]) AS [SumOf# Recieved], Sum(tblSubformProducts.[# Sold]) AS [SumOf# Sold], Sum(IIf(tblSubformProducts![#Recieved]>tblSubformProducts![#Sold],tblSubformProducts![#Recieved]-tblSubformProducts![#Sold],"")) AS [In Stock]
FROM tblProductInformation INNER JOIN tblSubformProducts ON (tblProductInformation.[Item#] = tblSubformProducts.[Item #]) AND (tblProductInformation.[Item#] = tblSubformProducts.[Item #]) AND (tblProductInformation.[Item#] = tblSubformProducts.[Item #])
GROUP BY tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued
HAVING (((tblSubformProducts.EventLocation)=[Forms]![FrmInventoryLocation]![Text37])) OR ((([Forms]![FrmInventoryLocation]![Text37]) Is Null))
ORDER BY tblSubformProducts.EventLocation;

I hope this helps!

I tried to use the code you posted in the last post but it prompted me for #Recieved,#Sold ,Etc. even after I opened it in design view and checked that it was linked to my correct table. If you have any questions or need any other information let me know.

[Scott Price]

Recheck the spelling carefully, I notice you have it # Recieved, and # Sold once, then the second time is without the space. (By the way... Recieved is spelled ei, as in i before e except after c... Not a real problem, just as long as it's spelled consistently throughout your references :-)

Regards,
Scott

[klarae99]

Scott,

You were correct in evaluating my space issue (I thought I had removed all the spaces from my field names in the tables but I had not.) Once I put the spaces into the expression the two diologe boxes asking for # Recieved and # Sold stopped popping up but I still got the Error message:

This expression is incorrectly typed, or it is to complex to be evaluated, Etc, etc.

Who thought something so simple could cause so many problems?

Thanks for your continuing efforts.

[Scott Price]

Try copying and pasting this sql directly into a new query:

SELECT DISTINCTROW tblProductInformation.[Item#], Sum(tblSubformProducts.[#Recieved]-tblSubFormProducts.[#Sold]) AS [In Stock], tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.Discontinued, tblProductInformation.Category, tblProductInformation.Subcategory, tblProductInformation.Description
FROM tblProductInformation INNER JOIN tblSubformProducts ON tblProductInformation.[Item#] = tblSubformProducts.[Item#]
GROUP BY tblProductInformation.[Item#], tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.Discontinued, tblProductInformation.Category, tblProductInformation.Subcategory, tblProductInformation.Description
HAVING (((Sum([tblSubformProducts].[#Recieved]-[tblSubFormProducts].[#Sold]))<>0)) AND (((tblSubformProducts.EventLocation)=[Forms]![FrmInventoryLocation]![Text37]));
 

I set up the two tables in my test database, wrote this query up, and it gives no errors.

I did notice in looking more closely at your last posted sql that you have several duplications, as well as an unnecessary where criteria. Give this one a try and see if it's what you're after.

Regards,
Scott

For the copy and paste, you'll have to paste it into Notepad first to strip out the line numbers.

[klarae99]

Scott,

I copied the new code into a new queary and did not get any error messages (yeah!), however the number in stock is not seperated by the Items, all the items list the same amount in stock (and I think it is the total # of items per each location) but I need to have the stock per item per location. I hope that makes sense to you. Your additional help is appreciated!

[Scott Price]

Hi Klara,

I'm sorry, but I can't seem to reproduce that problem in my test db :-( When I populate with test data, using the sql I posted, it does what I think you want done!

I.e. I put three items into the tblProductInfor mation, all with the same vendorID, with varying category, subcategory and descriptions.

Then in the tblSubFormProdu cts I posted a bunch of numbers for these items (at least two of each #Recieved and #Sold). Running the query results in two (I made sure the third of my test items would sum to 0, to 'test' the criteria) items being summed and grouped together accurately.

I'm at a loss right now to know what to tell you!

Regards,
Scott

[klarae99]

Scott,

Thanks for all your help with this, its good to know that its not just me that runs into a wall.

I also wanted to let you know that I have used scripts to solve many of my other issues with access by reading other posts that are similar to what I need done. Many of those posts were responded to by you and your advice there helped me progress to this point.

Thanks again for all your help, I'll update this post if I do figure out what was going on, or if I determine another way to achive my goal. I'm sure that I'll come up with more questions and I'll see you back here!