Query returning 0 of a type and not showing that type in the query table

[emjlr3]

Man that title is poorly descriptive, lets try again.

I have two tables. The first is Chemical Types (this table contains information for all possible types of chemicals I may have), the primary key is a text field Chemical Name, and some other fields of interest are Reorder Level (number), Target Level (number), and Units (text).

This table is linked to another table called Inventory (this table contains the records of each container) by the text field Chemical Name using a One-To-Many relationship. The table Inventory has three fields of interest for this question, a text field Barcode, which is the primary key, a boolean field Removed (which tells me if we still have the container or if the container has been turned in/disposed), and a number field Size.



Size is used in this way. If my units of measure for a chemical type from the table Chemical Types is liters (L), and I received a container of 1L of a chemical, the Size entry in the table Inventory for that container would be 1. Since we always order chemicals in the same sized containers, a static Units field in Chemical Types works fine.

Reorder Level is used to create a threshold where if a Chemical Name reaches that level in my Inventory, I would want it flagged so I could order more of that Chemical Name, and not risk running out. To know how much of a Chemical Name I currently have in Inventory, I Query all records in Inventory that are not Removed, and summate the Size fields for each Chemical Name. If the sum is less than or equal to my Reorder Level, I would want my Query to flag that Chemical Name and tell me about it.

I have created such a Query using these two tables to create a list of Chemical Names that are Low Stock Inventory, based on my Reorder Level set points. See below for a picture of my Query set up.



A problem I have encountered is this. My Query does not return Chemical Names that have a sum of Size equal to 0. I assume because the Query has no containers of that Chemical Name to summate, its basically skipping that Chemical Name as a potential return for the Query.

Does anyone have any thoughts on how I could remedy this problem? Thanks.

[Bob Fitz]

Not sure on this one, but it sounds a though you need to edit the join between the two tables, so the query returns all the records from the left and only matching records from the right.

[emjlr3]

Ideally the Query would only return Chemical Names that are low in stock.

I see your work around perhaps having the same problem as my current Query. I will give it a go.

[Bob Fitz]

You may need to use Nz() function so that Null value return 0

[John_G]

Hi -

See my comment on your post in the "Forms" board - there are problems with your data structure. I think you will need three tables - Chemicals, Deliveries and Inventory. You have data which relates to specific chemicals (e.g. MSDS), specific deliveries (e.g. Purchase Order) or specific Inventory items (e.g. Removed).

Once you sort out which data items belong in which table, life will be easier.

That said, with regard to your query problem, your assumption is correct - if there are no Inventory records that meet the criteria, you won't get any records for that chemical. Also, your setting for the "Total" line in the removed column would be better set to "Where", because you are really only using it as selection criteria.

Bob's suggestion as to the fix is correct, but note that the "Sum of Size" column will probably be blank where no inventory records are selected.

John

[Bob Fitz]

Hi -

See my comment on your post in the "Forms" board - there are problems with your data structure. I think you will need three tables - Chemicals, Deliveries and Inventory. You have data which relates to specific chemicals (e.g. MSDS), specific deliveries (e.g. Purchase Order) or specific Inventory items (e.g. Removed).

Once you sort out which data items belong in which table, life will be easier.

That said, with regard to your query problem, your assumption is correct - if there are no Inventory records that meet the criteria, you won't get any records for that chemical. Also, your setting for the "Total" line in the removed column would be better set to "Where", because you are really only using it as selection criteria.

Bob's suggestion as to the fix is correct, but note that the "Sum of Size" column will probably be blank where no inventory records are selected.

John

Do you think the "Sum of Size" column will still be blank if OP uses Nz(Size)

[emjlr3]

I tried using the Nz() function, perhaps I am not using it correctly. Here is the error I received.

"Total" line in the removed column would be better set to "Where

I corrected this.

Bob's suggestion as to the fix is correct, but note that the "Sum of Size" column will probably be blank where no inventory records are selected.

could you elab. a bit on this, I am not quite clear on what is meant. thanks.

[Bob Fitz]

Just a guess, but have you tried changing Expr1 from Expression to Where

[emjlr3]

I just did. That still does not seem to return any Chemical Names that have inventory of 0. I ran the query again, and with some other tweaking was never able to get it to produce returns that included the 0 inventory chemicals.

I tried using a column where I returned the sum of Nz(Size), but was not sure how to get the syntax correct.

[Bob Fitz]

To return a 0
Nz(Size)
should be
Nz(Size,0)

[emjlr3]

that does not seem to work either. Still does not give me any Chemical Names with 0 inventory.

Shouldn't that also have a sum in it because I want the sum of the Sizes for each chemical type, and where the sum is 0, return a 0 so it still counts that chemical type.

[Bob Fitz]

that does not seem to work either. Still does not give me any Chemical Names with 0 inventory.

Shouldn't that also have a sum in it because I want the sum of the Sizes for each chemical type, and where the sum is 0, return a 0 so it still counts that chemical type.

Possibly. Can you post a copy of the db in A2003 mdb format. Just the tables and queries would be ok.

[emjlr3]

i don't know about saving it as 2003, I do not see that as an option when I save the file. if you cannot open it let me know.

also, seems it would not let me share the file here on the forums 9size maybe), so here is an outside link to it at my One Drive account.

[Bob Fitz]

To convert to A2003 mdb format see: http://office.microsoft.com/en-gb/ac...#_Toc258329152
Do a compact and repair first to reduce its size.

[emjlr3]

To convert to A2003 mdb format see: http://office.microsoft.com/en-gb/ac...#_Toc258329152
Do a compact and repair first to reduce its size.

damn it wont let me. my database has attachments. i removed that table (not important for this problem) and its relationships, but i still must have other things that are not supported in the 2003 version. poop.