Trying to get to a report that shows current inventory and the total amount in assemblies

[rmandrell0808]

I have a 2013 database with an "items" table that reflects all items and all assemblies right up to the finished product. I have several tables that will eventually list all the parts in each assembly and sub assembly (BOM tables). In each BOM table I have parts that apply to several applications that are listed in the table by application and parts per application. The values in the parts per application columns are summed in a "Totals" column. These parts are linked to the Items table by the Item ID.





Then I have a query that references all the data in the BOM tables and sums the components used in all the sub-assemblies. The problem is that the different components are not used at all in some of the assemblies and only used in one or two places in others. The items that happen to be used in all the assemblies in question return a total but the items that are not used in all the assemblies return nothing. I need a way to do this so that I can set inventory levels that are appropriate to consumption. How can I get this to work? In the table all I had to do was make sure the cells had a zero in them when they were null. In the query I don't know of a way to do that. I used the expression builder to build the sum expressions in both cases.

[Micron]

Some expressions don't work as well with Nulls, so depending on the situation either
- make 0 the table default and new records will contain 0 where no value is provided
- use Nz function to return the desired value where a value is Null
- or you can create a calculated field in a query like IIf(IsNull([SomeField]),0,[SomeField])
in English it basically means if SomeField is null, return a zero, else return what's in SomeField. Text values would have to be wrapped in quotes.

[rmandrell0808]

Micron and anyone else who would help with this,

I would like to make the default value "0" but I have not found a way to do this in a query yet.

Can I add the code to my expression in some way { IIf(IsNull([SomeField]),0,[SomeField]) }? The expression in these fields looks like this right now Total: [1000208BOM]![Total]+[1000251BOM]![Total]+[1000471BOM]![Total]+[ATC106-C01T01B01-BOM]![Total]. Is there a function in the expression builder to add this with proper syntax?

Thanks for the reply.

[Micron]

I would like to make the default value "0" but I have not found a way to do this in a query yet.

I'm referring to the default value property as set in table design; not sure what you're referring to as queries don't have default values. They can contain expressions though, that create "default values" in the absence of some other value. If this expression

[1000251BOM]![Total]+[1000471BOM]![Total]+[ATC106-C01T01B01-BOM]![Total]

is what you have in a query field, but you get Nulls if there are no values for some or all of the fields/controls in the expression, then give your calculated field an alias using the Nz function as previously mentioned, and you should get a zero in place of Null:

SomeTotal: Nz([1000251BOM]![Total]+[1000471BOM]![Total]+[ATC106-C01T01B01-BOM]![Total],0)

This goes in the Field row of the query design grid.

[rmandrell0808]

I tried this in the total column ItemID but all I got was all zeros and no total. Then I realized I had left out the table in the design view table row. When I put them back in I got an error message telling me the table had to be in the expression. So far I have not been able to get the syntax correct and I don't understand why.

IIf(IsNull([Total]),0,[Total])

I then tried putting the code from the second message but got no totals at all.

[Micron]

Not sure if this is why, but don't use a field name for an alias. Total: is the syntax for creating an alias field name, but you have a table by that name. That can cause unexpected results. You also seem to have fields with the same name as one of your tables. When this is sorted out, I suggest you check out naming conventions given that and the fact that you're using special characters in field names. Here's one I refer people to: https://access-programmers.co.uk/for...d.php?t=225837

[rmandrell0808]

The last reply got me thinking about the name. I didn't find that the name table is on the official Access reserved list but I can see that it is a word that can cause unexpected results. The problem was more that in the tables I had the "total" field in each table. I renamed the field with the last three characters from the sub assembly +BOM +TTL and now I have practically the same expression but it works fine, filling the null cells with zeros.

The table above is a result of the expressions in the image below.


If I can get the rows to add (the program needs to recognize the zeros as values) this problem will be solved.

I really appreciate your help so far, I would have spent much more time on this and possibly never figured it out without you...

[Micron]

You're right, Total is not on the list, but I'm a bit surprised that you didn't raise an error like "Ambiguous name detected..." given that I didn't expect Access to be able to distinguish between a table name and field name that are the same (unless you had created explicit references, which I didn't see). You should give serious consideration to eliminating the spaces and special characters (such as -) in your names if you have not already. The tend to create other headaches you don't need.

As I mentioned, aggregate functions such as summing don't always work out well when Nulls are involved, so if you are getting the zeros you need, you should be OK. Still don't see why you can't/don't just set the field default to be zero in table design but you might have a valid reason for not doing so. If your issue has been solved, please mark the thread as solved. Thanks and good luck.

[rmandrell0808]

The default is set to zero and has been for the duration of this discussion in the tables that the query is sourced from. I will remove the dashes since I understand that they are a potential problem.
There are no table names that are the same as field names.

[rmandrell0808]

So I have been fiddling with the Nz function today since I got the other issue solved. It may not have been an issue if I had figured out the syntax for the Nz function before now. I definitely feel stupid because this is actually simple once I see the proper way to do it. So now I have TTL: Nz([1000208BOM]![208BOMTTL])+Nz([1000251BOM]![251BOMTTL])+Nz([1000471BOM]![471BOMTTL])+Nz([ATC106C01T01B01BOM]![ATC106BOMTTL]) as the expression in the final total column and it works fine.

I will get better at this...

So now I am trying to figure out how to mark this thread solved. I have not found "thread tools yet"....

[Micron]

So now I am trying to figure out how to mark this thread solved. I have not found "thread tools yet"

It's in the bar just under your post title. For me, it's green but that's an option you can set. Note that if you don't specify a default for Nz, you could get a 0 or a zero length string. I always specify a default as a matter of habit rather than take a chance.

I guess what I'm not getting is that if the table field defaults to zero, why you have to use an expression to get a zeros in a Select query, unless you're creating calculations that don't involve those table fields. Anyway, good luck with the project.